Please, help with join select - 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

General SQL Server Forums

New to SQL Server Programming

Please, help with join select

Author

Topic

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 09:54:22

There are 2 tables

tab1

IDname Name
1 Ivanov
2 Petrov
3 Sidorov
4 Mihalych
5 Alekseev

tab2

IDname Name
1 Ivanov
2 Petrov
3 Sidorov
5 Alekseev
6 Nikiforov

Could you provide me a query that finds records that exist in tab1 and
does not exist in tab2

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 09:58:38

SELECT *
FROM tab1
EXCEPT
SELECT *
FROM tab2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 10:01:34

[code]SELECT * FROM tab1 t1
WHERE NOT EXISTS(SELECT 1 FROM Tab2 WHERE Name = t1.Name)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 10:03:17

also


SELECT t1.*
FROM Tabl1 t1
LEFT JOIN Tab2 t2
ON t1.Name = t2.Name
WHERE t2.Name IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:38:37

Tank you guys. You really helped me.

BTW

This one doesn't works:

SELECT *
FROM tab1
EXCEPT
SELECT *
FROM tab2

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'EXCEPT'.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 10:42:53

It is because you use SQL Server 2000.

Try this one:

SELECT IDname, Name
FROM (SELECT *, k=1 FROM tbl1
UNION ALL
SELECT *, k=2 FROM tbl2) D
GROUP BY IDname, Name
HAVING MAX(k) = 1;

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:46:14

This query runs on what version of Server?

SELECT *
FROM tab1
EXCEPT
SELECT *
FROM tab2

Thanks, last quesry helped me too

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:47:39

Could we receive the result with other types of?join

Maybe right,outer,etc?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 10:47:57

quote:
Originally posted by y0zh

This query runs on what version of Server?

SELECT *
FROM tab1
EXCEPT
SELECT *
FROM tab2

Thanks, last quesry helped me too

SQL Server 2005 introduced this set operator.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 10:50:01

quote:
Originally posted by y0zh

Could we receive the result with other types of?join

Maybe right,outer,etc?

Visakh posted solution using LEFT OUTER JOIN in third reply!

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:50:25

ANy other result with join?
Maybe together with sub-query?

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:53:36

I mean right join. Is it possible?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 11:07:08

quote:
Originally posted by y0zh

I mean right join. Is it possible?

Yes. You should replace the tables name like this:

SELECT t1.*
FROM Tab2 t2
RIGHT OUTER JOIN Tab1 t1
ON t1.Name = t2.Name
AND t1.IDname = t2.IDname
WHERE t2.Name IS NULL

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 11:56:27

Thank you. Now I clearly understand how join works.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 13:50:04

quote:
Originally posted by y0zh

Thank you. Now I clearly understand how join works.

That is cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources