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

LVL 1
brianmfallsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmr159Commented:
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
                            )
0
mmr159Commented:
> 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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to do something like this, presuming that the mailinglist field is a comma-delimited field:
http://www.experts-exchange.com/A_1536.html
it will be the CROSS APPLY dbo.ParmsToList() method ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brianmfallsAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.