CODEWITHSUNDEEP
sqloracle-databasejoin
Ikeem Wilson
Ikeem Wilson

Reputation: 161

Need help creating the proper join in Oracle

I am a front end developer that has been tasked with creating a query that will extract contact data (email) for users that are stored in our database that meet a certain criteria. Some users don't have an email address but I want the rest of their information to show up anyway. I read the Techonthenet article about Oracle Joins but I don't seem to be getting it. Below is the code before adding joins.

SELECT p.person_id, p.first_name || ' ' || p.last_name person_name, pci.contact_data
 FROM cse.meeting m, cse.person p, cse.meeting_person mp, cse.person_contact_info pci
WHERE mp.meeting_id = m.meeting_id
  AND mp.person_id = p.person_id
  AND mp.person_id = pci.person_id
  AND m.meeting_date >= '03-Jan-2020'
  AND pci.contact_type_code = 'EMAIL'
ORDER BY m.session_date ASC;

I've tried the both:

AND mp.person_id = pci.person_id(+)

and

AND mp.person_id(+) = pci.person_id

With no success. I also tried doing it with explicit code as such:

SELECT p.person_id, p.first_name || ' ' || p.last_name person_name, pci.contact_data
 FROM cse.meeting m, cse.person p, cse.meeting_person mp LEFT JOIN cse.person_contact_info pci ON mp.person_id = pci.person_id
WHERE mp.meeting_id = m.meeting_id
  AND mp.person_id = p.person_id
  AND m.meeting_date >= '03-Jan-2020'
  AND pci.contact_type_code = 'EMAIL'
ORDER BY m.session_date ASC;

Still no luck. It will not return data for persons that don't have a row in the table person_contact_info with a contact_type_code of "EMAIL" Can someone describe to me what I'm doing wrong? The "join" concept is a little over my head.

Thanks

Upvotes: 1

Views: 61

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to show all persons that are assigned to at least one meeting on January 3, 2020. So select from table person.

You would use an IN or EXISTS clause in your WHERE clause to specify the criteria, but you want to order by the meeting's session date, so you must join the meeting tables instead. (That is a bit weird, because while you order by the session date, you are not showing it, thus making the query result look unordered.)

Then, you want to show the email, if the person has an email. So outer join the table person_contact_info.

Here is the query: 

SELECT
  p.person_id, 
  p.first_name || ' ' || p.last_name AS person_name,
  pci.contact_data
 FROM cse.person p
 JOIN cse.meeting_person mp ON mp.person_id = p.person_id
 JOIN cse.meeting m ON m.meeting_id = mp.meeting_id
                   AND m.meeting_date >= DATE '2020-01-03'
 LEFT JOIN cse.person_contact_info pci ON pci.person_id = p.person_id
                                      AND pci.contact_type_code = 'EMAIL'
ORDER BY m.session_date;

(One more thing: You are joining a person with their meetings on on January 3, 2020. If a person has three meetings on that day, you will show the person three-fold. So, maybe you do want to use IN or EXISTS instead.)

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

You have an outer join condition, but then you refer to the pci table in the where clause too - which has the effect of turning it back into an inner join. Put all the outer-join's conditions in its on clause, even though that one might not seem to quite belong there:

LEFT JOIN cse.person_contact_info pci ON mp.person_id = pci.person_id
AND pci.contact_type_code = 'EMAIL'

So, also replacing your old-style joins to be consistent:

SELECT p.person_id, p.first_name || ' ' || p.last_name person_name, pci.contact_data
FROM cse.meeting m
JOIN cse.meeting_person mp ON mp.meeting_id = m.meeting_id
JOIN cse.person p ON p.person_id = mp.person_id
LEFT JOIN cse.person_contact_info pci ON pci.person_id = p.person_id
AND pci.contact_type_code = 'EMAIL'
WHERE m.meeting_date >= DATE '2020-01-03'
ORDER BY m.session_date ASC;

Using a single style isn't just for consistency; new-style joins are evaluated first, so you can get odd-looking errors with more complicated queries that mix references.

I've also used a date literal for the target date; you were comparing with a string, which relies on implicit conversion and will fail if someone running this has different NLS settings to you.

The issue with your old-style outer joins was the same, it just looks different. You had outer and inner references to pci:

AND mp.person_id = pci.person_id(+)
AND pci.contact_type_code = 'EMAIL'

so if you had to do this in that style you would need to make both parts outer (and maybe make it easier to follow by swapping the terms):

AND pci.person_id (+) = mp.person_id
AND pci.contact_type_code (+) = 'EMAIL'

Unless you have a really good reason you should be using new-style joins anyway. 'New' here means 1992, so not really 'new' at all, of course.

Upvotes: 4

dvo
dvo

Reputation: 2153

I think I see the problem. If the record doesn't exist in cse.person_contact_info that you alias pci, then the field pci.contact_type_code is NULL. This is because the LEFT JOIN fills the field with NULL if not found in the joined table.

So, when you call pci.contact_type_code = 'EMAIL' in your WHERE clause, it doesn't match the records that aren't found because the field is filled with NULL in the JOIN.

To account for this, change this line in your where clause: AND pci.contact_type_code = 'EMAIL' to AND (pci.contact_type_code = 'EMAIL' OR pci.contact_type_code IS NULL). This will accept all where the pci.contact_type_code is EMAIL or not found (NULL).

Upvotes: 0

Related Questions