Simple Joins - 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 2008 Forums

Transact-SQL (2008)

Simple Joins

Author

Topic

Gigabyte
Starting Member

30 Posts

Posted - 2012-06-11 : 14:26:31

Can someone help me with this?

table A

id name
1 abc
2 bcd
3 def

table B

id name
3 asd
4 dfg
5 ggh

We need to write a join with which we should get row # 1 and 2 only.

GIGABYTE+

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-11 : 14:33:20

Here is one way:

DECLARE @TableA TABLE (id INT, name VARCHAR(10))
INSERT @TableA (id, name)
VALUES
(1, 'abc'),
(2, 'bcd'),
(3, 'def')

DECLARE @TableB TABLE (id INT, name VARCHAR(10))
INSERT @TableB (id, name)
VALUES
(3, 'asd'),
(4, 'dfg'),
(5, 'ggh')


SELECT A.*
FROM 
	@TableA AS A
LEFT OUTER JOIN
	@TableB AS B
	ON A.id = B.ID
WHERE
	B.ID IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:35:11

[code]
SELECT *
FROM @TableA a
WHERE NOT EXISTS(SELECT 1
FROM @TableB
WHERE id = a.ID
)
[/code]

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

Gigabyte
Starting Member

30 Posts

Posted - 2012-06-11 : 16:26:38

thank you Lamprey !:)

I wrote exact query but there was a little mistake and you have corrected it.
(select * from t1 left outer join t2 on t1.id = t2.id and t2.id is null)

Yours
(select * from t1 left outer join t2 on t1.id = t2.id where t2.id is null)

quote:

Originally posted by Lamprey

Here is one way:

DECLARE @TableA TABLE (id INT, name VARCHAR(10))
INSERT @TableA (id, name)
VALUES
(1, 'abc'),
(2, 'bcd'),
(3, 'def')

DECLARE @TableB TABLE (id INT, name VARCHAR(10))
INSERT @TableB (id, name)
VALUES
(3, 'asd'),
(4, 'dfg'),
(5, 'ggh')


SELECT A.*
FROM 
	@TableA AS A
LEFT OUTER JOIN
	@TableB AS B
	ON A.id = B.ID
WHERE
	B.ID IS NULL

GIGABYTE+

Gigabyte
Starting Member

30 Posts

Posted - 2012-06-11 : 16:30:51

Hi Visakh16,

Your sub query was very impressive. I really love it.

However I wanted to know if we can do this with joins rather than sub query.

With that said, I still like the query u wrote.

Impressive.

quote:
Originally posted by visakh16

SELECT *
FROM @TableA a
WHERE NOT EXISTS(SELECT 1
FROM @TableB
WHERE id = a.ID
)

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

GIGABYTE+

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 16:57:25

you can use Lamprey's suggestion in that case

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-12 : 07:19:31

You can use the following query:


--Creating Tables

Create table A
(id int,
 name varchar(3) )
 
Create Table B
(id int,
 name varchar(3) )


--Inserting Sample Data
 
Insert into A
Select 1, 'abc'
Union ALL
Select 2, 'bcd'
Union ALL
Select 3, 'def'

Insert Into B
Select 3, 'asd'
Union ALL
Select 4, 'dfg'
Union ALL
Select 5, 'ggh'


--Query For Your Requirement

Select a.id, a.name, b.id, b.name From
	(Select *, ROW_NUMBER() Over (Order By (Select NULL) ) As rn From A) As a
	 JOIN
	(Select *, ROW_NUMBER() Over (Order By (Select NULL) ) As rn From B) As b ON a.rn = b.rn
Where a.rn <= 2

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources