COALESCE, LEFT OUTER JOIN and GROUP BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

COALESCE, LEFT OUTER JOIN and GROUP BY

Author

Topic

anlefi
Starting Member

4 Posts

Posted - 2009-02-20 : 08:45:24

Hi All,

I have the following tables in my database:

---------------------------------------
| MyFirstTable |
---------------------------------------
|MyFirstTableId | MyFirstTableGotoT2 |
---------------------------------------
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
---------------------------------------

---------------------------------------------
| MyTable1 |
---------------------------------------------
|MyTable1Id | MyFirstTableId | MyTable1Desc |
---------------------------------------------
| 1 | 1 | "A" |
| 2 | 1 | "I" |
| 3 | 2 | "A" |
| 4 | 3 | "I" |
| 5 | 3 | "A" |
---------------------------------------------

---------------------------------------------
| MyTable2 |
---------------------------------------------
|MyTable2Id | MyFirstTableId | MyTable2Desc |
---------------------------------------------
| 1 | 4 | "A" |
| 2 | 5 | "I" |
| 3 | 5 | "A" |
| 4 | 5 | "L" |
| 5 | 6 | "L" |
---------------------------------------------

What I want to do is to obtain all records from MyTable1 and MyTable2 in one query, grouped by Description.
This is the query I wrote to acomplish that:

----------------------------------------------------------------------------------------------------
SELECT COALESCE (MyTable1.MyTable1Desc, MyTable2.MyTable2Desc) AS Description, COUNT(*) AS Quantity
FROM MyFirstTable
LEFT OUTER JOIN
MyTable1 ON MyFirstTable.MyFirstTableGotoT2 <> 1
AND MyFirstTable.MyFirstTableId = MyTable1.MyFirstTableId
LEFT OUTER JOIN
MyTable2 ON MyFirstTable.MyFirstTableGotoT2 = 1
AND MyFirstTable.MyFirstTableId = MyTable2.MyFirstTableId
WHERE (MyTable1.MyTable1Desc IS NOT NULL) AND (MyFirstTable.MyFirstTableGotoT2 <> 1)
OR
(MyTable2.MyTable1Desc IS NOT NULL) AND (MyFirstTable.MyFirstTableGotoT2 = 1)
GROUP BY MyTable1.MyTable1Desc, MyTable2.MyTable2Desc
----------------------------------------------------------------------------------------------------

This is the result I'm qetting:

** QUERY RESULT **
----------------------------
|Description | Quantity |
----------------------------
| "A" | 3 |
| "I" | 2 |
| "A" | 2 |
| "I" | 1 |
| "L" | 2 |
----------------------------

But the result I want is this:

** DESIRED RESULT **
----------------------------
|Description | Quantity |
----------------------------
| "A" | 5 |
| "I" | 3 |
| "L" | 2 |
----------------------------

Does anyone have an idea if this is possible and give me a clue about how to do this?
Thanks in advance!!!

Anlefi

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 08:54:37

Can you explain how you want that output ?

Just guessing, Basis your output..

select c,count(*)
from (
	select MyTable1Id a , MyFirstTableId b, MyTable1Desc c
	 from MyTable1 
	union all
	select * from MyTable2
      )ss
group by c

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:06:32

[code]
SELECT Descn,COUNT(ID)
FROM
(
SELECT t1.MyTable1Id AS ID,t1.MyTable1Desc AS Descn
FROM MyFirstTable t
INNER JOIN MyTable1 t1
ON t1.MyFirstTableId=t.MyFirstTableId
AND t.MyFirstTableGotoT2=0
UNION ALL
SELECT t2.MyTable2Id ,t2.MyTable2Desc
FROM MyFirstTable t
INNER JOIN MyTable1 t2
ON t2.MyFirstTableId=t.MyFirstTableId
AND t.MyFirstTableGotoT2=1
)r
GROUP BY Descn
[/code]

anlefi
Starting Member

4 Posts

Posted - 2009-02-20 : 09:13:20

Thank you both!

The UNION ALL works perfect, I thought about trying it but I wasn't sure if the performance of the query with a lot of records would by good in comparation with the COALESCE with LEFT OUTER JOIN.

What do you think about that?

Thanks again!

Anlefi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:10:15

did you compare execution times of both?

anlefi
Starting Member

4 Posts

Posted - 2009-02-20 : 10:47:16

Not yet, but I'll do it aso soon as possible.

I was just asking if theorically they are equivalent in terms of performance or if there's any known issue.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:25:25

i think it depends on presence of your indexes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources