MySQL - How to use (LEFT) JOIN to join two tables through one table?
Some background and objective
I was compiling a list of links for a person on my site in HTML / PHP . It allows for multiple selection (select with the "multiple" attribute) and it basically looks like this:
╔══════════╗ ║ Facebook ║ ╠══════════╣ ║ Google+ ║ ╠══════════╣ ║ Twitter ║ ╚══════════╝
The selected items will be saved to the MySQL database, but that's not all. After saving the selection, I would like to go back and see which selections were selected. This means that I have to generate this select element in PHP with my selected selections.
Database structure
Last edited on 6.24.2015, 13:23 (Finland time)
In MySQL , I have the following tables:
- the link contains information about the link (link address, color, etc.).
- person includes information about the person (first and last name, address, email address, etc.).
- person_link serves as a table to link table and person links together (has columns id_link and id_person)
The tables look like this (tried to display columns related to this question, data is arbitrary and here for demonstration):
link
╔══════════╦════════════╗
║ id ║ name ║
╠══════════╬════════════╣
║ 1 ║ Facebook ║
╠══════════╬════════════╣
║ 2 ║ Google+ ║
╠══════════╬════════════╣
║ 3 ║ Twitter ║
╚══════════╩════════════╝
person
╔══════════╦═════════════╦═════════════╦══════════════╦═════════════════╗
║ id ║ name_first ║ name_last ║ address ║ email ║
╠══════════╬═════════════╬═════════════╬══════════════╬═════════════════╣
║ 1 ║ Jack ║ Johnson ║ Wacko Street ║ NULL ║
╠══════════╬═════════════╬═════════════╬══════════════╬═════════════════╣
║ 2 ║ Hanna ║ Hill ║ Hill Town ║ hh@gmail.com ║
╠══════════╬═════════════╬═════════════╬══════════════╬═════════════════╣
║ 3 ║ Eric ║ Samson ║ Wacko Street ║ NULL ║
╚══════════╩═════════════╩═════════════╩══════════════╩═════════════════╝
person_link
╔═══════════╦═════════╦═════════════════════╗
║ id_person ║ id_link ║ address ║
╠═══════════╬═════════╬═════════════════════╣
║ 2 ║ 1 ║ www.example.com ║
╠═══════════╬═════════╬═════════════════════╣
║ 2 ║ 2 ║ www.hello.org ║
╚═══════════╩═════════╩═════════════════════╝
Problem
Long story short, my problem is that I can't put together the correct query in MySQL to achieve my desired goal. I have seen many questions regarding LEFT JOIN between two tables, but did not come across a question with an additional table joining two tables together.
I've tried the following:
SELECT link.name, person.id
FROM link
LEFT JOIN person_link ON (link.id = person_link.id_link)
JOIN person ON (person_link.id_person = person.id)
WHERE person.id = 2
Now, I would expect this kind of query to display all links on the left and match the ID of a specific person on the right (if available). If the id matches a link, that link was selected from the select element . This is what I wanted the result to look like (NULL represents no match):
╔══════════╦══════╗
║ Facebook ║ 2 ║
╠══════════╬══════╣
║ Google+ ║ 2 ║
╠══════════╬══════╣
║ Twitter ║ NULL ║
╚══════════╩══════╝
Why do I want the table to look like this? Since based on this table, I could generate the select-element to look like this:
╔═════════════════════╗ ║ Facebook (SELECTED) ║ ╠═════════════════════╣ ║ Google+ (SELECTED) ║ ╠═════════════════════╣ ║ Twitter ║ ╚═════════════════════╝
But instead of the request being successful, I ended up with this:
╔══════════╦══════╗ ║ Facebook ║ 2 ║ ╠══════════╬══════╣ ║ Google+ ║ 2 ║ ╚══════════╩══════╝
There is clearly something wrong with my request, but I don't know what it is. So my question is, how do I use (LEFT) JOIN to join two tables across one table?
source to share
SELECT l.name, p.id
FROM link l
LEFT JOIN person_link pl
ON (l.id = pl.id_link)
LEFT JOIN person p
ON (pl.id_person = p.id && l.id = pl.id_link && p.id = 2)
group by l.link
or based on Nirala's answer
SELECT l.name, pl.id_person
FROM link l
LEFT JOIN person_link pl
ON (l.id = pl.id_link && pl.id_person = 2)
group by l.link
source to share
You need to allow the person to NULL:
SELECT link.name, person.id
FROM link
LEFT JOIN person_link ON (link.id = person_link.id_link)
LEFT JOIN person ON (person_link.id_person = person.id)
WHERE (person.id = 2 OR person.id IS NULL)
You can also write it like:
SELECT link.name, person.id
FROM link
LEFT JOIN person_link ON (link.id = person_link.id_link)
LEFT JOIN person ON (person_link.id_person = person.id AND person.id = 2)
source to share
Some sample data would be good ( http://sqlfiddle.com/ would be ideal), but from my point of view this unverified query should do the job.
SELECT person.*, link.*
FROM person_link
RIGHT JOIN link ON id_link = link.id
INNER JOIN person ON id_person = person.id
WHERE id_person = 2
source to share