asked on

SQL INNER JOIN using 'IN' rather than '='

This query works great.

SELECT     mr.mailRecipient_ID, mr.mailRecipient_lists, mr.mailRecipient_firstName, mr.mailRecipient_email, mr.mailRecipient_lastMsg
FROM         mailRecipient AS mr INNER JOIN
                      mailList AS ml ON ml.mailList_ID IN (4, 5, 6)

Open in new window

However... this is what I really want to do. The mailRecipient_lists column of the mailRecipient table contains a comma delimited list of id's from the mailList table (mailList_ID). How do I select a list of mailList_titles with each record associate to the id's in the in the mr.mailRecipient_lists column? Obviously the query below is not going to work. :) TIA

SELECT     mr.mailRecipient_ID, mr.mailRecipient_lists, mr.mailRecipient_firstName, mr.mailRecipient_email, mr.mailRecipient_lastMsg, ml.mailList_id, ml.mailList_title
FROM         mailRecipient AS mr INNER JOIN
                      mailList AS ml ON ml.mailList_ID IN (mr.mailRecipient_lists)

Open in new window

mmr159

You're almost there. Use a subquery.

SELECT mr.mailRecipient_ID, mr.mailRecipient_lists, mr.mailRecipient_firstName, mr.mailRecipient_email, mr.mailRecipient_lastMsg, ml.mailList_id, ml.mailList_title
FROM mailRecipient AS mr INNER JOIN
mailList AS ml ON ml.mailList_ID IN (
SELECT mailRecipient_lists
FROM mailRecipient
)

mmr159

> The mailRecipient_lists column of the mailRecipient table contains a comma delimited list of id's from the mailList table (mailList_ID).

Sorry. I just realized my previous post will not work due the data being comma delimited. You will need to parse this out first.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brianmfalls

ASKER

Thank you.