CODEWITHSUNDEEP
mysql
June Lewis
June Lewis

Reputation: 355

MYSQL Select items in list from subquery

Thanks in advance for any help you guys can offer. I have been stuck on this one for a few hours this morning and nothing I search for seems to help.

I am storing a list of accounts associated with a client as a string in my database like this:

   | li.client_accounts   |
   +-----+----------------+
   |ID   |facility        |
   +-----+----------------+
   |23   |1010, 1020, 1025|
   +-----+----------------+

I am trying to select appointments from another database where the account number is in the list using a sub query like this:

    SELECT * FROM li_appointments.li_appointments
    where app_client_id in (select facility from li_client_accounts where id = 23)

However my results are only showing the appointments with a client_id of 1010, and ignoring the rest. What do I need to do to get this working?

Upvotes: 2

Views: 3029

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

You can't use IN() like that.

What you are actually getting is something like this:

SELECT * FROM li_appointments.li_appointments
    where app_client_id in ('1010, 1020, 1025')

This is obviously not what you want.

You need to seriously think about normalizing your facility column if you want to use it in a relational manner.

Upvotes: 0

Nadh
Nadh

Reputation: 7243

It's not working because the IN clause expects a list of fields. However, in your case, facility is a string of comma separated values, and not a queriable list. This is bad design. You need to make your client_accounts table atomic, like :

   | li.client_accounts   |
   +-----+----------------+
   |client_ID   |facility |
   +-----+----------------+
   |23   |1010|
   |23   |1015|
   |23   |1020|
   +-----+----------------+

Upvotes: 5

Related Questions