mysql: how to query when id in another table record's field

Question

Say I have two tables doctors and patients Assume patients has two columns: Id and Name Assume doctors has three columns: Id, npi, patientids Each patientids field can have multiple patient's id value. So how can I query patients that belong to the doctor if given the Id of doctors table.

I have tried the following sql, but it does not return anything

select p.Name from patients p 
 where p.Id in (select patientIds from doctors d where d.Id=@id);

You can use `FIND_IN_SET`. But you better normalize your `doctors.patientids` column. — Paul Spiegel, Apr 21 '16 at 19:52
What @PaulSpiegel said, you need a `doctors_patients` table, parsing a value list is one of the last things you want to be doing in a RDBMS. — Uueerdo, Apr 21 '16 at 19:53

score 0 · Answer 1 · answered Apr 21 '16 at 20:02

0

With a small data set, this is fast enough:

SELECT p.Name
  FROM patients p
  JOIN doctors d ON d.Id=@id AND FIND_IN_SET(p.id, d.patientIds)
;

But really, as commenters have said, you want a join table mediating the relationship between patients and doctors.

answered Apr 21 '16 at 20:02

bishop

37,830
11
104
139

It's not only about performance. It's about code complexity. Every time you want to remove a patientID from a doctors patient list, you have to parse and rebuild the list. Not to mention if you want to remove a patient completely from the database. However - your query should work. – Paul Spiegel Apr 21 '16 at 20:20
@PaulSpiegel I agree that code complexity should balance performance. But I would agree to that regardless of the question. From this question alone, we don't know if this is a write active or a pure reporting database. For all we know, the data set is static and the OP just needs to do some reporting on it. (Which is what I assumed, because I can't imagine anyone trying to use this structure for active writes.) – bishop Apr 22 '16 at 12:47

score 0 · Answer 2 · answered Apr 21 '16 at 20:13

0

Generic answer/template for many-to-many relationships:

SELECT [fields you want]
FROM tableA
[INNER|LEFT] JOIN ["middle" table] AS AB ON tableA.id = AB.A_id
[INNER|LEFT] JOIN tableB ON AB.B_id = tableB.id
;

answered Apr 21 '16 at 20:13

Uueerdo

15,723
1
16
21

mysql: how to query when id in another table record's field

2 Answers2