CODEWITHSUNDEEP
sqloracle-databaseinner-join
user2333225
user2333225

Reputation:

SQL query joining multiple tables and getting information thats in one but not the others

I am having trouble with making a query that joins three different tables and getting information from different ones.

The tables are:

TableP
DataP1 (primary key)


TableL
DataL1 (primary key)
DataP1 (foreign key)


TableA
DataA1 (primary key)
DataP1 (foreign key)
Date

I need to show the DataP1 values that do not appear in TableL but appear in TableP and TableA, along with the related dates taken from TableA

So far I have something like this:

Select TableL.DataP1, TableA.date
from TableP
inner join TableL on TableP.DataP1 = TableL.DataP1
inner join TableA on TableP.DataP1 = TableA.DataP1

but this shows just the values that are in TableL and not in TableA and TableP (basically the opposite)

I tried adding things like ...

when TableL.DataP1 <> TableA.DataP1

... but this didn't seem to help.

If you could offer insight into where I am going wrong, it would be very helpful Thanks

Upvotes: 1

Views: 87

Answers (4)

Ed Gibbs
Ed Gibbs

Reputation: 26363

To show DataP1 values that are in TableP and in TableA you use an inner join:

SELECT TableP.DataP1
  FROM TableP
  INNER JOIN TableA On TableP.DataP1 = TableA.DataP1

To show DataP1 values that are in TableP and not in TableL, do an outer join and look for instances where TableL.DataP1 is NULL:

SELECT TableP.DataP1
  FROM TableP
  LEFT OUTER JOIN TableL ON TableP.DataP1 = TableL.DataP1
  WHERE TableL.DataP1 IS NULL

To put it all together (DataP1 values in TableP and in TableA and not in TableL):

SELECT TableP.DataP1
  FROM TableP
  INNER JOIN TableA On TableP.DataP1 = TableA.DataP1
  LEFT OUTER JOIN TableL ON TableP.DataP1 = TableL.DataP1
  WHERE TableL.DataP1 IS NULL

Upvotes: 1

Ray K
Ray K

Reputation: 1490

I wouldn't start my "FROM" clause with the table I want to exclude. From my understanding of your question:

Data is desired that is found in both TableP and TableA; however, exclude this record if found in TableL.

SELECT 
     l.DataP1
   , a.Date

FROM TableP p
   Inner Join TableA a 
   on p.DataP1 = a.DataP1
   Left Join TableL l
   on p.DataP1 = l.DataP1
WHERE
  -- you'll join on TableL and here you exclude it when the data is found there
   l.DataL1 is null

The Inner join to table A will assure you only pull records that have data in both P and A. The Left join to Table L will show you records that both have and don't have data in Table L... in this case, your where clause then excludes records when it is found in Table L.

Upvotes: 0

Chris
Chris

Reputation: 2950

you need to do a left join and check for null fields

SELECT TableL.DataP1, TableA.date
FROM TableP tp
inner join TableA ta on tp.DataP1 = ta.DataP1
LEFT JOIN TableL tl ON tl.DataP1 = ta.DataP1
WHERE tl.DataP1 IS NULL

Hope that helps

Upvotes: 0

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

Use a left join against TableL instead of an inner join, then only select rows with null values in the TableL portion of the data:

SELECT p.DataP1, a.Date
FROM TableP p
INNER JOIN TableA a ON p.DataP1 = a.DataP1
LEFT JOIN TableL l ON l.DataP1 = p.DataP1
WHERE l.DataL1 IS NULL

Inner join requires that both sides of the join (i.e., both tables) have records that match on the join criteria. Left join will always return results for the table on the left side of the join, even if there are no matches on the right side.

Upvotes: 1

Related Questions