MySQL SELECT DISTINCT field from second table using a relationship

Question

Let's say we have a tally_sheets table which contains some tally sheets information like in the following description and a persons table described below with a tally_sheets field containing a list of tally sheet's IDs separated by comma.

Every tally sheet contain only peoples from a country/city combination.

How to get a list of all tally sheets with all fields from tally_sheets and country and city fields from persons table?

mysql> desc tally_sheets;
+-----------+--------------+
| Field     | Type         |
+-----------+--------------+
| id        | int(10)      |
| date      | char(10)     |
| person    | varchar(64)  |
| comment   | varchar(255) |
| timestamp | timestamp    |
+-----------+--------------+

mysql> desc persons;
+------------------+--------------+
| Field            | Type         |
+------------------+--------------+
| id               | int(11)      |
| name             | varchar(30)  |
| country          | varchar(60)  |
| city             | varchar(60)  |
| tally_sheets     | varchar(64)  | <== comma separated tally_sheet's ID
+------------------+--------------+

Running

SELECT ts.id, ts.date, p.country, p.city, ts.person, ts.`comment`, '0' AS sqlExclude 
FROM tally_sheets ts 
RIGHT JOIN persons p ON ts.id IN (p.tally_sheets) 
GROUP BY p.city

will give all country/city combinations but all the other fields are NULL.

+------+------+------------+--------------------+--------+---------+------------+
| id   | date | country    | city               | person | comment | sqlExclude |
+------+------+------------+--------------------+--------+---------+------------+
| NULL | NULL | Country1   | City1              | NULL   | NULL    | 0          |
| NULL | NULL | Country1   | City2              | NULL   | NULL    | 0          |
| NULL | NULL | Country2   | City3              | NULL   | NULL    | 0          |
+------+------+------------+--------------------+--------+---------+------------+

Running SELECT ts.id, p.country, p.city FROM persons p LEFT JOIN tally_sheets ts ON ts.id IN (p.tally_sheets) GROUP BY p.city will also return NULL fields.

+------+------------+--------------------+
| id   | country    | city               |
+------+------------+--------------------+
| NULL | Country1   | City1              |
| NULL | Country1   | City2              |
| NULL | Country2   | City3              |
+------+------------+--------------------+

What i'm doing wrong? Thanks a lot!

Normalize your schema, don't use comma-separated lists. – Barmar Oct 22 '14 at 15:37 — Barmar, Oct 22 '14 at 15:37
@Barmar, I can't touch any table. Thanks for hint. – SYNCRo Oct 22 '14 at 15:45 — SYNCRo, Oct 22 '14 at 15:45

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

First of all i would say you should normalize your schema and read about Database_normalization for handling relationships,If you are not able to alter your schema you can use find_in_set,Also in() will not work for comma separated list stored in your column

SELECT ts.id, ts.date, p.country, p.city, ts.person, ts.comment, '0' AS sqlExclude 
FROM tally_sheets ts 
RIGHT JOIN persons p ON find_in_set(ts.id ,p.tally_sheets) >0

Here is the reference answer how you can normalize your structure

edited May 23 '17 at 11:51

Community

1
1

answered Oct 22 '14 at 15:38

M Khalid Junaid

63,861
10
90
118

Can't touch the tables structure. Your code works fine but it return a list of all persons. Adding `GROUP BY p.country, p.city` will return all tally sheets. Thank you! – SYNCRo Oct 22 '14 at 15:44

MySQL SELECT DISTINCT field from second table using a relationship

1 Answers1