CODEWITHSUNDEEP
sqljoin
H. Ferrence
H. Ferrence

Reputation: 8106

How To Code a Multi-Join SQL Query And Get Query Results Even When 1 Table Lacks A Joining Row

I have the following query statement:

$query_string = '
    SELECT customerID, lastName, firstName, companyName, email, citizenship, primaryLanguage
    FROM customers
    JOIN citizenships USING(citizenshipID)
    JOIN languages USING(languageID)
    JOIN paymentMethods USING(customerID)
    WHERE customerID = "1"
';

Currently the customers, citizens and languages tables each contain rows and join properly. My query result returns 1 row for customer #1.

The paymentMethods table does not contain any rows at this time. When I add the join syntax for paymentMethods to the query string, my query result returns 0 rows for customer #1.

I want to join on paymentMethods and only return a row from the paymentMethods table when one exists without causing no customer rows to be returned otherwise.

How might I tweak my JOIN syntax to make that happen?

Thank you.

Upvotes: 0

Views: 100

Answers (2)

Chris Charles
Chris Charles

Reputation: 4446

$query_string = '
   SELECT customerID, lastName, firstName, companyName, email, citizenship,   primaryLanguage
   FROM customers
   JOIN citizenships USING(citizenshipID)
   JOIN languages USING(primaryLanguageID)
   LEFT JOIN paymentMethids USING(customerID)
   WHERE customerID = "1"
';

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

Replace it with LEFT JOIN:

SELECT  customerID, lastName, firstName, companyName, email, citizenship, primaryLanguage
FROM    customers
JOIN    citizenships
USING   (citizenshipID)
JOIN    languages
USING   (languageID)
LEFT JOIN
        paymentMethods
USING   (customerID)
WHERE   customerID = "1"

Upvotes: 1

Related Questions