UNION ALL statement - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

UNION ALL statement

Author

Topic

lassew
Starting Member

5 Posts

Posted - 2005-03-11 : 14:34:01

Hello,

I have a SQL statement which is working OK:

SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1

I need to have a third selector, I have used the following code :

SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod3)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1

This is giving me to many answers, does anyone have any good suggestions to improve it?

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-11 : 14:39:24

Use UNION instead of UNION ALL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-11 : 14:41:16

hmmmmm .... is something wrong with:

SELECT diakod,diatexter,skada
FROM Tra_ddl
WHERE dia_ddl in(@Kod, @Kod2, @Kod3)

??? (or is that too simple?)

- Jeff

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-11 : 14:55:49

Good eye, I didn't even check that the tablename was the same in all 3 cases.

lassew
Starting Member

5 Posts

Posted - 2005-03-11 : 15:25:21

A short explanation of my case:

I have a simple table (Tra_ddl) with four columns:

* Id(numeric)
* Reason for damage (i.e. Car accident) [dia_ddl]
* Damage code (i.e. V1009) [diakod]
* Damage codedescription [diatexter]

There are many Reason for damage for each Damage. I would like to get the Distinct Damage code[diakod] and Damage codedesciption[diatexter] where Reason for damage[dia_ddl] are i.e. "Car accident" AND "Bicycle" AND "Bus".

Kristen
Test

22859 Posts

Posted - 2005-03-12 : 00:38:05

So for a "match" there are three records

One where [dia_ddl] = 'Car accident'
Second where [dia_ddl] = 'Bicycle'
Third where [dia_ddl] = 'Bus'

Is that right? Which of the other fields are the same? Do they have the same ID field?

I'd join the table back to itself three times on the "matching" columns. So, assuming that ID is common:


SELECT    T1.diakod,T1.diatexter,T1.skada
FROM      Tra_ddl T1
          JOIN Tra_ddl T2
              ON T2.ID = T1.ID
             AND T2.dia_ddl = 'Bicycle'
          JOIN Tra_ddl T3
              ON T3.ID = T1.ID
             AND T3.dia_ddl = 'Bus'
WHERE T1.dia_ddl = 'Car accident'

Kristen

lassew
Starting Member

5 Posts

Posted - 2005-03-12 : 03:37:26

Thanks Kirsten,

You got me on the right track! The ID field isn't the same but instead I Joined on diakod. So instead on

JOIN Tra-ddl T2
ON T2.ID = T1.ID

I used

JOIN Tra-ddl T2
ON T2.diakod = T1.diakod

It's working great!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources