StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to remove duplicating rows from union statement
text
Body
copied!<p>OK - I have looked and looked and found a lot of examples but nothing quite meeting my need. Maybe I used the wrong words to search with, but I could use your help. I will provide as much detail as I can.</p> <p>I need to produce a report that merges fields from two tables, or rather a view and a table, into one table for a report. Here is the statement I am using:</p> <pre><code>SELECT A.ConfInt, A.Conference, NULL as Ordered, NULL as Approved, NULL as PickedUp, SUM(dbo.Case_Visit_Payments.Qty) AS Qty FROM dbo.Conferences as A INNER JOIN dbo.Case_Table ON A.ConfInt = dbo.Case_Table.Conference_ID INNER JOIN dbo.Case_Visit_Payments ON dbo.Case_Table.Case_ID = dbo.Case_Visit_Payments.Case_ID WHERE (dbo.Case_Visit_Payments.Item_ID = 15 AND A.ProjectCool = 1) GROUP BY A.Conference, A.ConfInt UNION SELECT B.ConfInt, B.Conference, SUM(dbo.Cool_Fan_Order.NumberOfFansRequested) AS Ordered, SUM(dbo.Cool_Fan_Order.Qty_Fans_Approved) AS Approved, SUM(dbo.Cool_Fan_Order.Qty_Fans_PickedUp) AS PickedUp, NULL AS Qty FROM dbo.Conferences as B LEFT OUTER JOIN dbo.Cool_Fan_Order ON B.ConfInt = dbo.Cool_Fan_Order.Conference_ID where B.ProjectCool = 1 GROUP BY B.Conference, B.ConfInt </code></pre> <p>And here are the results:</p> <pre><code>4 Our Lady NULL NULL NULL 11 4 Our Lady 40 40 40 NULL 7 Holy Rosary 20 20 20 NULL 11 Little Flower NULL NULL NULL 21 11 Little Flower 5 5 20 NULL 19 Perpetual Help NULL NULL NULL 2 19 Perpetual Help 20 20 20 NULL </code></pre> <p>What I would strongly prefer is to not have the duplicating rows, such as:</p> <pre><code>4 Our Lady 40 40 40 11 7 Holy Rosary 20 20 20 NULL 11 Little Flower 5 5 20 21 19 Perpetual Help 20 20 20 2 </code></pre> <p>I hope this question was clear enough. Any Suggestions would be greatly appreciated. And I do mark as answered. :)</p> <p>Gregory</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload