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

Transact-SQL (2008)

Query Help

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-10-14 : 11:41:06

How can I select the records that fit this criteria?

MyID YourID MineID
100 200 300
400 200 100
500 600 100

I need to select the records that have MyID = MineID and YourID = YourID (all one condition). My result would be line record 2.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-14 : 11:47:19

select t2.*
from tbl t1
join tbl t2
on t1.MyID = t2.MineID
and t1.YourID = t2.YourID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-10-14 : 11:59:09

Thank you so much! Can i get the opposite (i.e. those who do not have MyID = MineID and YourID = YourID) if I use the <> operator?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 12:06:51

for that use like


select t1.*
from tbl t1
left join tbl t2
on t1.MyID = t2.MineID
and t1.YourID = t2.YourID
where t2.MyID IS NULL

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

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-10-14 : 12:38:03

I'm not sure it's working because I'm getting rows that are the same in both queries?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 13:03:52

what does that mean? can you show a data sample and explain?

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

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-10-14 : 13:07:20

In the first query I return:
MyID YourID MineID
400 200 100

In the second query I return the same thing:
MyID YourID MineID
400 200 100

I would expect to return:
MyID YourID MineID
500 600 100

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-14 : 14:30:29

I think you just need to ad to Visakh's

select t1.*
from tbl t1
left join tbl t2
on t1.MyID = t2.MineID
and t1.YourID = t2.YourID
where t2.MyID IS NULL
or t2.YourID is null

Jim

Everyday I learn something that somebody else already knew

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-10-14 : 15:46:27

I actually figured it out....it was my logic not the code. Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources