I need help on sql query.... - 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)

I need help on sql query....

Author

Topic

great_mamun
Starting Member

14 Posts

Posted - 2008-09-15 : 03:38:51

Dear All,
I have 2 tables t1 & t2.

t1 (id is primary key)
--------------
id | name
--------------
1 | Mamun
--------------
2 | Jui
--------------
3 | Sayed
--------------

t2 (id is foreign key)
---------------
id | status
---------------
1 | P
---------------
1 | A
---------------
2 | P
---------------

I want select * from t1
and it will show

2 Jui
3 Sayed

Because,
Sayed's id 3 is not present in t2.
Jui's id 2 is present in t2 but status is 'P'.
and Mamun's id 1 is present but status is 'A' so it will not display.

please someone help on the where clause or what will be the condition so that the above result will show.

Best Regards,
Abdullah Al Mamun

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 03:43:27

[code]SELECT t.*
FROM t1 t
LEFT JOIN
(SELECT id,SUM(CASE WHEN status<>'p' THEN 1 ELSE 0 END) AS ppresent
FROM t2
GROUP BY id)tmp
ON tmp.id=t.id
AND tmp.ppresent>0
WHERE tmp.id IS NULL[/code]

EDIT:should be >0 instead of =0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 04:09:01

[code]DECLARE @t1 TABLE (ID INT, Name VARCHAR(20))

INSERT @t1
SELECT 1, 'Mamun' UNION ALL
SELECT 2, 'Jui' UNION ALL
SELECT 3, 'Sayed'

DECLARE @t2 TABLE (ID INT, Status CHAR(1))

INSERT @t2
SELECT 1, 'P' UNION ALL
SELECT 1, 'A' UNION ALL
SELECT 2, 'P'

-- Peso
SELECT t1.ID,
t1.Name
FROM @t1 AS t1
LEFT JOIN @t2 AS t2 ON t2.ID = t1.ID
GROUP BY t1.ID,
t1.Name
HAVING MAX(CASE WHEN t2.Status = 'A' THEN 1 ELSE 0 END) = 0

--Visakh16
SELECT t.ID,
t.Name
FROM @t1 AS t
LEFT JOIN (
SELECT id,
SUM(CASE WHEN status <> 'p' THEN 1 ELSE 0 END) AS ppresent
FROM @t2
GROUP BY id
) as tmp ON tmp.id = t.id
AND tmp.ppresent = 0
WHERE tmp.id IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 04:22:24

[code]SELECT t1.ID,
t1.Name
FROM @t1 AS t1
WHERE NOT EXISTS (SELECT * FROM @t2 AS t2 WHERE t2.ID = t1.ID AND t2.Status = 'A')[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources