Something that would work like a "non-join"

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

Transact-SQL (2005)

Something that would work like a "non-join"

Author

Topic

SLM09
Starting Member

31 Posts

Posted - 2010-01-19 : 12:46:57

Ok, I know the name of the subject sounds odd, but I am trying to find a way to bump two tables against one another without joining them. Basically, I want table A and table B to return only table A (with no duplicates), but where I can say WHERE a.whatever = B.whatever

Is there a way to do this? I figure you could do SELECT A.x, A.y, A.z... but for large tables this is insane. Any ideas?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 12:49:44

SELECT a.*
FROM a
JOIN b...

but best practice is to specify all column names in your select statement

SLM09
Starting Member

31 Posts

Posted - 2010-01-19 : 13:03:19

Won't this still give me duplicates?

Sorry for not explaining well... ill give an example:

Table 1
Name Color
John Red
John Blue
Mark Green

Table 2
Name Car
John Camaro
John Corvette
Mark Pinto

I want to do searches on table 1 based on values in table 2... but if I join them, John ends up with 4 records when he should only have 2. Chalk it up to bad data layout (which I had nothing to do with), but I need to keep table 1 as is with nothing added and compare it to table 2 with stuff like WHERE 1.name = 2.name

I know there are several different Joins (i primarily use left and full) so maybe there is a join that does this... hopefully I explained a bit better this time...

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 13:08:32

If you are only selecting columns from A, but want to JOIN TableA and TableB, then the only purpose of the JOIN is to establish if a corresponding records in TableB exists (although, one thing a JOIN does do is to give you MULTIPLE results if there are multiple matches between the two tables).

Assuming you only want ONE row from TableA when there is a match with TableB I think it would be better to use an EXISTS statement in this context.

SLM09
Starting Member

31 Posts

Posted - 2010-01-19 : 13:12:57

Awesome! Not really familiar with exists, but it looks like it may be what I need. I will play with the code some.

Thanks for speedy replies!

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 13:19:19

quote:
Originally posted by SLM09

Not really familiar with exists ...


SELECT Col1, Col2, ...
FROM TableA AS A
WHERE EXISTS
(
    SELECT *
    FROM TableB AS B
    WHERE     B.SomeCol = A.SomeCol
          AND B.OtherColumn ='FooBar'
)

WHERE NOT EXISTS is also valid syntax, if you need it.

SLM09
Starting Member

31 Posts

Posted - 2010-01-19 : 15:56:49

I did end up getting this working using exists. Made things a bit more complicated since I did it to several tables and the nesting was pretty nuts, but it got the job done.

Thanks again! :)

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 08:38:21

I am confused as to why not join Table a to Select Distinct b.Name from Table b, and set the constraint of a.name = b.name. Then you will not get duplicates... it does not sound like you are checking column 2 against anything.

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 12:46:18

JOIN to SELECT DISTINCT requires making the SELECT DISTINCT first - which requires (usually, and AFAIK) findall all the rows, sorting them, and discarding duplicates.

OTOH an EXISTS test can stop as soon as a single match is made.

Not saying one is better than the other, but the way EXISTS works (or more correctly "when it can stops

) is worth considering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources