Clever Geek Handbook

How to select unique rows from one to many related tables in MySQL?

As a MySQL-Noob, I am afraid to solve the following problem: Assuming 3 tables: location

groceries

and person

with the following properties:

  • Each table has a primary auto-incrementing integer ID and a varchar column.
  • All records in tables are unique.
  • Each record in groceries

    has a foreign key to the record in person

    .
  • It is possible that more than one of the records groceries

    are using the same foreign key.
  • (3) and (4) apply to person

    and location

    also

Thus, we are related many to one. How can I select every triplet (groceries_product, person_name, location_name)

that person_name

does not occur more than once?

Example:

tables:  groceries                  | person      | location
------------------------------------ ------------- -------------------------
columns: id  product      person_id | id  name    | id  name  person_id
------------------------------------ ------------- -------------------------
         1   hamburger    1         | 1   Peter   | 1   home  1
         2   cheeseburger 1         | 2   Tom     | 2   work  1
         3   carot        1         |             | 3   zoo   2 
         4   potatoe      1         |             |
         5   mango        2         |             |

All the triplets that you can create that are Peter

irrelevant. I only want threes like (mango, Tom, zoo) because Tom really only exists once in all possibilities. I hope my question is not clear. :-)

0


source to share


2 answers


I think you need to do a subplot to get the result:



SELECT groceries.product, person.name, location.name
FROM person
LEFT JOIN groceries ON person.id = groceries.person_id
LEFT JOIN location ON person.id = location.person_id
WHERE person.id
IN (
SELECT person.id
FROM person
LEFT JOIN groceries ON person.id = groceries.person_id
LEFT JOIN location ON person.id = location.person_id
GROUP BY person.id
HAVING count( person.id ) =1
)
+1


source 


select l.name, m.name, r.name
from `left` l
left join middle m on m.id = l.middle_id
left join `right` r on m.id = r.middle_id;


-1


source






More articles:


All Articles