Select from two tables with multiple criteria

Question

Select from two tables with multiple criteria

g_able

Valued Member

Points: 68
More actions
May 24, 2016 at 4:47 pm

#307991

How to I select from two tables with three criteria? I want to select TABLE A when TABLE B matches A on OBJECT00, FIELD00, AND VALUE00. In this example, ID01,ID02, and ID03 matches, whereas the rest doesn't match.
Thanks in advance.
TABLE A
+----+----------+--------+---------+----------+
|ID00|OBJECT00|FIELD00|VALUE00|USER|
+----+----------+--------+---------+----------+
|ID01|OBJECT01|FIELD01 |VALUE01|BOB|
|ID02|OBJECT02|FIELD02 |VALUE02|BOB|
|ID03|OBJECT03|FIELD03 |* |BOB|
|ID04|OBJECT04|FIELD04 |VALUE04|BOB|
|ID05|OBJECT05|FIELD05 |VALUE05|BOB|
|ID06|OBJECT06|FIELD06 |VALUE06|BOB|
+----+----------+-----+-----------+----------+
TABLE B
+----+----------+--------+---------+-----+-----+
|ID00|OBJECT00|FIELD00|VALUE00|VAR|STAT|
+----+----------+--------+---------+-----+-----+
|ID01|OBJECT01|FIELD01 |VALUE01|XXX|0000|
|ID02|OBJECT02|FIELD02 |VALUE02|XXX|0000|
|ID03|OBJECT03|FIELD03 |* |XXX|0000|
|ID07|OBJECT07|FIELD07 |VALUE07|YYY|0001|
|ID08|OBJECT08|FIELD08 |VALUE08|YYY|0001|
|ID09|OBJECT09|FIELD09 |VALUE09|YYY|0001|
+----+----------+-----+-----------+----------+
DESIRE RESULTS
+----+----------+--------+---------+----------+
|ID00|OBJECT00|FIELD00|VALUE00|USER|
+----+----------+--------+---------+----------+
|ID01|OBJECT01|FIELD01 |VALUE01|BOB|
|ID02|OBJECT02|FIELD02 |VALUE02|BOB|
|ID03|OBJECT03|FIELD03 |* |BOB|
+----+----------+-----+-----------+----------+

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

peacesells SSCrazy Points: 2882 More actions · Answer 1

I am not sure if I understand your question correctly. This could be a simple inner join on the three matching columns. Also it matters when you say the rest doesn't match or the rest doesn't matter. If its the first one, you could need to compare(<>) each column to ensure they don't match .

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

select *

from tableA a

where exists (select * from tableB b where a.ID00 = b.ID00 and a.OBJECT00 = b.OBJECT00 and a.FIELD00 = b.FIELD00)

BTW, your sample data isn't really helpful. When you post requests, please provide create table statements, inserts to populate the tables and then expected output. That way we can write a statement and validate it actually does what it needs to do instead of guessing. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

g_able Valued Member Points: 68 More actions · Answer 3

Sorry, first time poster. =) It's strange, I thought inner join will do the trick and your method works too. However, when I applied to a large data set (hundred millions of combination), it doesn't work. Scratch head....Will need to figure it out tomorrow. I'll have to reduce the data set to a few hundred rows and work from there. Thanks for helping.

Just to add, when I apply to a larger more robust data set, only the first row of each of the condition is returned.