Board index » delphi » SQL select question: self join?

SQL select question: self join?

I got a table like this

   Name     Item1  Item2  Item3  Qty
   JOHN     APPLE                 2
   paul     Apple  Orange         3

I want to list the total of items for a QuickReport's TQuery.
So I do as:

SELECT ITEM1,SUM(QTY) FROM TABLE1
   WHERE (ITEM1 IS NOT NULL)
   GROUP BY ITEM1
UNION ALL
SELECT ITEM2,SUM(QTY) FROM TABLE1
   WHERE (ITEM2 IS NOT NULL)
   GROUP BY ITEM2
UNION ALL
SELECT ITEM3,SUM(QTY) FROM TABLE1
   WHERE (ITEM3 IS NOT NULL)
   GROUP BY ITEM3

It is very strange the result table contains one blank entry.
With Item1= Null.  (Which I already specify in each SELECT WHERE
condition for NOT NULL)
I suspect that the blank entry is returned from the 3rd "select" where an
empty set should return. (But in fact a table with 1 entry is returned)

What is the proper statement to do the above SELECT ?
How to avoid the empty entry?

Thanks.
You guys are very helpful!

 

Re:SQL select question: self join?


First you can change the select statement in order to check where the
results came from, to something like this:

SELECT ITEM1,"SEL1",SUM(QTY) FROM TABLE1
   WHERE (ITEM1 IS NOT NULL)
   GROUP BY ITEM1,2
UNION ALL
SELECT ITEM2,"SEL2",SUM(QTY) FROM TABLE1
   WHERE (ITEM2 IS NOT NULL)
   GROUP BY ITEM2,2
UNION ALL
SELECT ITEM3,"SEL3",SUM(QTY) FROM TABLE1
   WHERE (ITEM3 IS NOT NULL)
   GROUP BY ITEM3,2

The group by  is made by number since the field is a constant.

After this you can check where that empty row comes from.

Nevertheless, I think the select is correct, the only thing is that I don't
use the ALL keyword in front of the UNION keyword.

Other Threads