Avatar of Kurt Ross
Kurt Ross
Flag for United States of America asked on

Best Query

I have 2 tables
table 1
select * from table1 join table 2 on table1.1  = table2.1
but there are hundreds in table 2 and I only want to know if there is a match or not a match

so in table 1 I have 3 records- when I join to table2 I have hundreds

Help
Oracle Database

Avatar of undefined
Last Comment
Kurt Ross

8/22/2022 - Mon
slightwv (䄆 Netminder)

Sorry but I don not understand you are you asking.

If the columns in the join match between the tables then you will get all the rows.  That is the way joins work.

What I don't understand is what you mean by:   I only want to know if there is a match or not a match.

Can you provide some sample data and expected results?
Sean Stuber

Something like one of these?

select t1.*,  NVL( (select 'MATCH' from table2 t2 where t2.id t1.id and rownum = 1) , 'NO MATCH')
from table1 t1

or

select t1.*, nvl(t2.match,'NO MATCH')
 from table1 t1
left outer join  (select distinct id, 'MATCH' match from table2) t2
 on t1.id = t2.id
ASKER CERTIFIED SOLUTION
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

EXISTS or IN or an INNER JOIN will only give you the implicit "match" criteria.  To get "no match" you need to include the rest of the rows
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
johnsone

I was making an assumption based on the original query that the join was what they were really only looking for matches.  You are correct that EXISTS wouldn't give the no match and the outer join would be the best way to go there.
Kurt Ross

ASKER
is there a way to add a third table matching the same criteria as the second to the first
so there would be 2 more columns from a third table saying null or showing the match>
slightwv (䄆 Netminder)

>>is there a way to add a third table

Probably.

Personally I have no idea what you are looking for.  There also appears to be some confusion between the other Experts that posted.

Can you not post sample data and expected results?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
johnsone

Agreed.  A third table should be able to be added, but I'm not entirely sure what you are looking for.  Sample data and results would be the easiest way to show what you are looking for as a final result.  Please be sure to include all 4 outcomes (assuming only 3 tables).
PortletPaul

An "exists" test seems to me to be the approach wanted to meet this:

"so in table 1 I have 3 records- when I join to table2 I have hundreds" [but I only want 3]

& I don't see anything specific in the question that leads to a need for a string "match" or "no match"

However the added requirement has morphed the question a bit it seems to me
"is there a way to add a third table matching the same criteria as the second to the first
so there would be 2 more columns from a third table saying null or showing the match"

If you want columns OF table3 in the result then join table3 into the query
If you merely wish to test for the existence of a match then you can use EXISTS
If you wish to suppress "unwanted repitition" in the results then perhaps you need to use a GROUP BY

There is a broad and generalized set of possible solutions because the question is vague. To get a more definitive answer please supply "sample data" and, from that data, the "expected result"
Kurt Ross

ASKER
Worked perfectly
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Sean Stuber

what worked perfectly?

the accepted post doesn't offer an answer, only questions
Kurt Ross

ASKER
SELECT t1.*
FROM   table1 t1
WHERE  EXISTS (SELECT 1
               FROM   table2 t2
               WHERE  t1.id = t2.id)
Sean Stuber

That answer, as acknowledged by the one who posted it does NOT give the requested results.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Kurt Ross

ASKER
it helped me figure out what I needed.
as I asked the question!
Thanks
Kurt
Kurt Ross

ASKER
I've requested that this question be closed as follows:

Accepted answer: 0 points for KurtRoss's comment #a41382640

for the following reason:

the solution I have found
Odd someone else would tell me it isn't!
Kurt Ross

ASKER
it gives me the results I require!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Kurt Ross

ASKER
it was what I needed- so it was the solution I required
Please Close this ticket