Problems with SELECT statement over 3 tables

Question

I have 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

Please see [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056). Also, can you edit the title, so it reflects the issue? "Problems" isn't very descriptive. — Scratte, Sep 29 '21 at 14:03
You can change to `AND b.id = c.address_id(+)` if you don't want to use newer syntax. You may also want to check out [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Scratte, Sep 29 '21 at 14:15

Dave · Accepted Answer · 2021-09-29T14:08:02.413

1

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/

edited Sep 29 '21 at 14:08

answered Sep 29 '21 at 14:06

Dave

28,833
23
113
183

Right. How do you do a left join? :) I mean you've described what it means, but how does one make use of it? – Scratte Sep 29 '21 at 14:11
@Scratte normally I'm happy to answer any question, but honestly, if you do one search, you'll find hundreds (likely more) of pages explaining exactly what MySQL is, how to use it, how to do JOINs...etc. (I even have a page already linked in my answer, so - just click that?) – Dave Sep 29 '21 at 14:24
I know how it works :) I was just wondering why is wasn't included in your Answer. About geeksforgeeks.. they're using Venn diagrams for this, which in my opinion is just plain wrong. It just leads to more confusion, because matching rows in joins is nothing like sets. – Scratte Sep 29 '21 at 14:26
@Scratte It's not in my answer because that's not what was asked. I also didn't include the code for the OP to build the rest of their system. Because... that's not what they asked. If someone was missing a semicolon, and the answer was "you're missing a semicolon", do they need to include additional information on how to use a semicolon? – Dave Sep 29 '21 at 14:39

score 0 · Answer 2 · answered Sep 29 '21 at 14:15

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

Problems with SELECT statement over 3 tables

2 Answers2