CODEWITHSUNDEEP
sqloracle-databasejoin
dscl
dscl

Reputation: 1626

ORACLE/SQL - Joining 3 tables that aren't all interconnected

I need help joining 3 tables where they are not all interconnected

So lets say I have tables A, B, C

here are the relations

A.type = B.type

A.model = C.model

What I need to do is inner join A and B and return all the matched A records. Next I need to pull the records from C that match on the prior join.

Or in other words all the records in C that are in A where A is in B

Hope that makes sense. Sorry for no data examples.

I have tried this

select
    c.*
from
    c, a, b
where
    c.model_ = a.model_
    and a.type_ = b.type_

but receive this message 'Errors: Query has MERGE JOIN CARTESIAN. You must correct where-clause to properly join tables in the select statement.'

Upvotes: 2

Views: 5920

Answers (3)

Jörn Horstmann
Jörn Horstmann

Reputation: 34024

I know this is a matter of style but in my opinion ansi style joins make this much clearer:

SELECT c.*
FROM c
JOIN a ON a.model = c.model
JOIN b on b.type = a.type

In case you have multiple matching elements in a or b, this query will return duplicates. You can either add a DISTINCT or rewrite it as an EXISTS query:

SELECT *
FROM c
WHERE EXISTS (SELECT 1
              FROM a
              JOIN b ON b.type = a.type
              WHERE a.model = c.model)

I think this should also give the same result, as long as there are no NULL values in model:

SELECT *
FROM c
WHERE c.model IN (SELECT a.model
                  FROM a
                  JOIN b ON b.type = a.type)

Upvotes: 3

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

Without the data samples, it is difficult to understand the actual problem and validate the query.

did you try..

Select c.*
   from a,b,c
    where a.type = b.type
      and a.model = c.model;

Are your losing some records or seeing extra records that you don't expect to see?

The above query will only give you records in c that are in a and where a records match the criterion in b.

Upvotes: 0

Randy
Randy

Reputation: 16677

simply also join to C with an AND condition

Upvotes: 0

Related Questions