0

New to SQL. Building my first contact manager project. Current schema can be seen below.

Issue:

  • I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact.
  • I put together the following attempt, but it only returns results if the contactGUID is in every contact_type table.
--Problem: Only returns records if contactGUID is present in every contact_type table

SELECT
c.contGUID, c.contType, 
i.indTitle, i.indFirstName, i.indMidName, i.indLastName, 
e.emailAddress, e.emailDomain,
p.phnCountryCode, p.phnAreaCode, p.phnNumber,
s.socURL, s.socHandle,
a.addrCountry, a.addrCountryCode, a.addrCountrySubdivision, a.addrCountrySubName, a.addrSecondarySubdiv, a.addrMunicipality, a.addrMuniSubdivision, a.addrStreetNumber, a.addrStreetName, a.addrPostalCode, a.addrLatitude, a.addrLongitude

FROM
CONTACT c

JOIN INDIVIDUALS i
ON c.contGUID = i.indGUID

JOIN CONTACT_EMAIL ce
ON c.contGUID = ce.contactGUID

JOIN EMAILS e
ON ce.emailGUID = e.emailGUID

JOIN CONTACT_PHONE cp
ON c.contGUID = cp.contactGUID

JOIN PHONES p
ON cp.phnGUID = p.phnGUID

JOIN CONTACT_SOCIAL cs
ON c.contGUID = cs.contactGUID

JOIN SOCIALS s
ON cs.socGUID = s.socGUID

JOIN CONTACT_PHYS_ADDRESS cpa
ON c.contGUID = cpa.contactGUID

JOIN PHYS_ADDRESSES a
ON cpa.addrGUID = a.addrGUID

Question:

  • How do I return all other contact types (email, social, address) if say, CONTACT_PHONE record is not present for a given contactGUID?

enter image description here

0

1 Answer 1

1

As Akina mentioned, you need to use an OUTER JOIN, specifically LEFT OUTER JOIN or more simply written as LEFT JOIN to take all the records from the left-hand side of the join clause, join where they match the right-hand side of the clause, and just NULL out the fields where they don't match in the right-hand side. This tutorial from W3Schools gives a brief intro with visuals to the different join types. Here's another article with further information on different join types. (Note these articles are database-agnostic and there may be other available join types for your specific system as well, but these are generally all one ever needs.)

What you were previously doing by just specifying JOIN is using the shorthand syntax for the INNER JOIN clause which only returns rows that match on both sides of the join clause.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.