SELECT Query - Urgent - 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)

SELECT Query - Urgent

Author

Topic

sagavb
Starting Member

18 Posts

Posted - 2009-06-12 : 06:04:54

Hi Friends,

Here, i have 2 tables. I just want to find out the records from TableA which are not matched with TableB

TableA:

ID
--
A1
A2
A3

TableB:

ID
--
A1
B1

o/p

ID
--
A2
A3

primary key is ID

Can anybody help on this?

Thanks in advance
Saga

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 06:10:06

1. Use NOT EXISTS
2. Use NOT IN
3. Use LEFT JOIN

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

sagavb
Starting Member

18 Posts

Posted - 2009-06-12 : 06:12:51

hi

Can you post me a query for the above example

Regards
Saga

quote:
Originally posted by Peso

1. Use NOT EXISTS
2. Use NOT IN
3. Use LEFT JOIN

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 06:22:09

4. UNION ALL

SELECT		ID
FROM		(
			SELECT	0 AS s, ID FROM TableA UNION ALL
			SELECT	1 AS s, ID FROM TableB
		) AS d
GROUP BY	ID
HAVING		MAX(s) = 0

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-12 : 08:08:37

quote:
Originally posted by Peso

4. UNION ALL
SELECT ID
FROM (
SELECT 0 AS s, ID FROM TableA UNION ALL
SELECT 1 AS s, ID FROM TableB
) AS d
GROUP BY ID
HAVING MAX(s) = 0

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

Just horrible. but cool.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-12 : 08:11:30

quote:
Originally posted by sagavb

hi

Can you post me a query for the above example

Regards
Saga

quote:
Originally posted by Peso

1. Use NOT EXISTS
2. Use NOT IN
3. Use LEFT JOIN

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

I generally use LEFT JOIN -- performance wise I understand that NOT EXISTS my be quicker but I find LEFT JOIN much easier to read.


SELECT
    a.*
FROM
    tableA a
    LEFT JOIN tableB b ON b.[Id] = a.[Id]
WHERE 
    b.[Id] IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-12 : 08:28:44

quote:
Originally posted by sagavb

Hi Friends,

Here, i have 2 tables. I just want to find out the records from TableA which are not matched with TableB

TableA:

ID
--
A1
A2
A3

TableB:

ID
--
A1
B1

o/p

ID
--
A2
A3

primary key is ID

Can anybody help on this?

Thanks in advance
Saga

Hi saga!

Why so urgent???

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-12 : 08:29:39

quote:
Originally posted by senthil_nagore

quote:
Originally posted by sagavb

Hi Friends,

Here, i have 2 tables. I just want to find out the records from TableA which are not matched with TableB

TableA:

ID
--
A1
A2
A3

TableB:

ID
--
A1
B1

o/p

ID
--
A2
A3

primary key is ID

Can anybody help on this?

Thanks in advance
Saga

Hi saga!

Why so urgent???

select * from tab1 where id not in(select id from tab2)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled