Query help - 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

Transact-SQL (2000)

Query help

Author

Topic

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-02-10 : 09:51:11

Hi.

I have a problem with a query. I have the following table:


ID1	ID2	ID3	ID4
25	3	0	0
25	5	0	0
25	5	5	0
25	5	6	0
25	5	6	4
25	5	6	6

From that I want to do a SELECT that returns:

ID1	ID2	ID3	ID4
25	3	0	0
25	5	5	0
25	5	6	4
25	5	6	6

All the ID's are links to other tables that hold names. So the output originally looks like this:
AR1204	Car	NULL		NULL
AX1305	Bus	NULL		NULL
AX1305	Bus	Small	 	NULL
AX1305	Bus	Small		Special
AX1305	Bus	Small		Normal
AX1305	Bus	Large		NULL

But I would like it to look like this:
AR1204	Car	NULL		NULL
AX1305	Bus	Small		Special
AX1305	Bus	Small		Normal
AX1305	Bus	Large		NULL

.
In other words in the above example I do not want to return 5,0,0 if 5,5,0 exists or 5,0,0 or 5,6,0 if 5,6,x exists etc.

I know it's a strange table but there isn't much I can do about it. I inherited the system :P. Any tips on this or should I try and handle the result when presenting the data in the application instead?

/Argyle

Edited by - argyle on 02/10/2003 09:54:11

ToddV
Posting Yak Master

218 Posts

Posted - 2003-02-10 : 13:35:12

My guess is that someone will have a better solution than this, but I think this does what you are looking for.

Create table #TEMP (
ID1 INT,
ID2 INT,
ID3 INT,
ID4 INT)

INSERT #TEMP VALUES (25, 3, 0, 0)
INSERT #TEMP VALUES (25, 5, 0, 0)
INSERT #TEMP VALUES (25, 5, 5, 0)
INSERT #TEMP VALUES (25, 5, 6, 0)
INSERT #TEMP VALUES (25, 5, 6, 4)
INSERT #TEMP VALUES (25, 5, 6, 6)

SELECT *
FROM #TEMP A
WHERE (
ID2 > 0
AND NOT EXISTS (
SELECT *
FROM #TEMP B
WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2 AND ID3 > 0)
)OR
(
ID3 > 0
AND NOT EXISTS (
SELECT *
FROM #TEMP B
WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2 AND A.ID3 = B.ID3 AND
ID4 > 0 )
) OR (ID4 > 0)

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-02-10 : 18:03:52

Thx, I'll try this out :)

/Argyle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources