Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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
For this, I always think of EXISTS, so I would do it this way:
SELECT t1.*
FROM table1 t1
WHERE EXISTS (SELECT 1
FROM table2 t2
WHERE t1.id = t2.id)
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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 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.
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>
so there would be 2 more columns from a third table saying null or showing the match>
>>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?
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?
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).
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"
"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"
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!
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Basic 239 lessons
-
Microsoft ApplicationsCertification: MCP MCSA Microsoft Certified Partner & Solutions Ass… 377 lessons
-
IT AdministrationCertification: ISCAP Information Systems Certification and Accreditati… 16 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2013 Part … 176 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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?