Query Display

eusanpe · December 19th, 2008, 09:35 PM

Hello All:

I am using query analyzer and I am trying make a query display the way I want it to but I am lost. Here are the fields in the two tables.

Code:

Table1:

Form#  FormID   FormName1   FormName2
------ -------- ---------   ---------
  1    FID1      FNTest1     NULL
  2    FID2      NULL        FNTest2
  3    FID3      FNTest3     FNTest4
  4    FID4      FNTest5     FNTest6

Table2:

Form#  Label     FormName3 
------ ------   --------- 
  1    Label1     NULL
  1    Label2     NULL
  1    Label3     FN3Data
  2    Label4     NULL
  2    Label5     FN4Data
  2    Label6     FN4Data 
  3    Label7     NULL
  3    Label8     NULL
  3    Label9     FN5Data
  4    Label10    NULL
  4    Label11    NULL
  4    Label12    NULL
  

I want to display the data as without the extra labels that contain null values.

FormID   FormName1  FormName2  Label   FormName3
------------------------------------------------
  FID1    FNTest1    NULL      Label3   FN3Data
  FID2    NULL       FNTest2   Label5   FN4Data
  FID2    NULL       FNTest2   Label6   FN4Data
  FID3    FNTest3    FNTest4   Label9   FN5Data
  FID4    FNTest5    FNTest6   Label10  NULL

I hope this makes sense.

Thanks,
Tony

Old Pedant · December 19th, 2008, 10:06 PM

How do you choose WHICH of the labels to show when all the FormName3 values are NULL???

4 Label10 NULL
4 Label11 NULL
4 Label12 NULL

How did you know to choose Label10 there instead of Label11 or Label12???

eusanpe · December 19th, 2008, 10:58 PM

Thank you for replying. Since Label10, Label11 and Label12 all have Null values, I just picked the first line to display. I really don't need it to display but I need the data FormName data to display in table 1 for that data.

I hope that makes sense.

Thanks again,
Tony

Old Pedant · December 19th, 2008, 10:58 PM

Code:

SELECT T1.FormID, T1.FormName1, T1.FormName2,
       ISNULL( T2.Label, T3.Label ) AS Label,
       ISNULL( T2.FormName3, T3.FormName3 ) AS FormName3
FROM Table1 AS T1 
LEFT JOIN Table2 AS T2 ON ( T1.FormNum = T2.FormNum AND T2.FormName3 IS NOT NULL )
LEFT JOIN ( SELECT FormNum, MIN(Label) AS firstLbl, FormName3
            FROM Table2 WHERE FormName3 IS NULL
            GROUP BY FormNum, FormName3
          ) AS T3 ON ( T1.FormNum = T3.FormNum )
ORDER BY 1,2,3,4,5

I think that works.

Consider FormNum2:

[NULL,FNTest2] will be joined with both [Label5,FN4Data] and [Label6,FN4Data]
so you get two records. And because neither T2.Label nor T2.FormNme3 is null,
the value (if any) from T3 is ignored.

(And there would be a record from T3: [Label4, NULL} But, again, it can be ignored
because we have valid data from T2 in its place.)

Consider FormNum4:

[FNTest5,FNTest6] will be joined with nothing from T2, since FormName3 is NULL
for all FormNum4 records. So now the values from T3 will be the single record
[Label10,Null]. And because the join to T2 resulted in nothing, both T2.Label
and T2.FormName3 will be null, and so the ISNULL will use the values from T3.

Give it a try.

eusanpe · December 20th, 2008, 02:00 AM

Thank you.

For some reason it wouldn't take this:

ISNULL( t2.label, t3.label) AS Label

I kept getting invalid column 'label' errors.

I just added t2.label to the Select statement and it worked.

Thank you again,
Tony

Old Pedant · December 20th, 2008, 09:18 PM

It probably didn't like the fact that it couldn't figure out which label we were refering to when I used AS Label. It shouldn't care, but I've given up trying to figure out every oddity of SQL Server.

eusanpe · December 21st, 2008, 01:48 PM

I left out a couple of pieces of info. Check out the updated specs.

Basically either the Name column in Table00 or Table0 will match the Form#. How can I tell the query to use the one that matches?

Thank you again for your help.

Code:

Table00:

Name      Form#
------    ------
Name1       1
Name2       2


Table0:

Name      Form#
------    ------
Name3       3
Name4       4


Table1:

Form#  FormID   FormName1   FormName2
------ -------- ---------   ---------
  1    FID1      FNTest1     NULL
  2    FID2      NULL        FNTest2
  3    FID3      FNTest3     FNTest4
  4    FID4      FNTest5     FNTest6

Table2:

Form#  Label     FormName3 
------ ------   --------- 
  1    Label1     NULL
  1    Label2     NULL
  1    Label3     FN3Data
  2    Label4     NULL
  2    Label5     FN4Data
  2    Label6     FN4Data 
  3    Label7     NULL
  3    Label8     NULL
  3    Label9     FN5Data
  4    Label10    NULL
  4    Label11    NULL
  4    Label12    NULL
  

I want to display the data as without the extra labels that contain null values.

Name  FormID   FormName1  FormName2  Label   FormName3
-------------------------------------------------------
 Name1  FID1    FNTest1    NULL      Label3   FN3Data
 Name2  FID2    NULL       FNTest2   Label5   FN4Data
 Name2  FID2    NULL       FNTest2   Label6   FN4Data
 Name3  FID3    FNTest3    FNTest4   Label9   FN5Data
 Name4  FID4    FNTest5    FNTest6   Label10  NULL

Old Pedant · December 21st, 2008, 10:43 PM

Easy...

*FIRST* create a VIEW that unifies your Table0 and Table00, thus:

Code:

CREATE VIEW AllNamesView
AS
SELECT Name, FormNum FROM Table0
UNION
SELECT Name, FormNum FROM Table00

And then just join what I showed you to that view:

Code:

SELECT ANV.Name, T1.FormID, T1.FormName1, T1.FormName2,
       ISNULL( T2.Label, T3.Label ) AS Label,
       ISNULL( T2.FormName3, T3.FormName3 ) AS FormName3
FROM AlllNamesView AS ANV
INNER JOIN Table1 AS T1 ON ANV.FormNum = T1.FormNum
LEFT JOIN Table2 AS T2 ON ( T1.FormNum = T2.FormNum AND T2.FormName3 IS NOT NULL )
LEFT JOIN ( SELECT FormNum, MIN(Label) AS firstLbl, FormName3
            FROM Table2 WHERE FormName3 IS NULL
            GROUP BY FormNum, FormName3
          ) AS T3 ON ( T1.FormNum = T3.FormNum )
ORDER BY ...whatever you need...

Untested, off the top of my head. Might need minor refinements.

eusanpe · December 22nd, 2008, 09:55 PM

Worked perfectly. Thank you for your help.

Tony