Problems with SELECT statement over 3 tables [duplicate]-CodePudding

I´ve the following statement, where I get user-data, user-addresses and additional user-address-attributes in one query. This works perfectly unless there are no address-attributes for the addresses, this means in "s_user_addresses_attributes" are no hits. How can I achieve that this fact is ignored? In such cases it would be enough to just get user-data and user-addresses.

SELECT *
FROM s_user AS a
JOIN s_user_addresses AS b
JOIN s_user_addresses_attributes AS c
WHERE a.id = 416
AND b.user_id = a.id
AND b.id = c.address_id
AND b.id != IF (((SELECT COUNT(*) FROM s_user_addresses WHERE user_id = a.id) > 1), a.default_billing_address_id, 0)
ORDER BY a.id

Regards

CodePudding user response：

You'll want to use a LEFT JOIN instead of an INNER JOIN (the default that's used when you just use JOIN). This will cause it to still return the main results even if no s_user_addresses_attributes are found.

LEFT JOIN: "This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join."

INNER JOIN: "The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same."

https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/

CodePudding user response：

to put LEFT JOIN in your code you only need to add LEFT to the left of the JOIN, like this:

SELECT *
FROM s_user AS a
LEFT JOIN s_user_addresses AS b
LEFT JOIN s_user_addresses_attributes AS c
WHERE a.id = 416
AND b.user_id = a.id
AND b.id = c.address_id
AND b.id != IF (((SELECT COUNT(*) FROM s_user_addresses WHERE user_id = a.id) > 1), a.default_billing_address_id, 0)
ORDER BY a.id