• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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

0
brianmfalls
Asked:
brianmfalls
  • 2
1 Solution
 
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
 
brianmfallsAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now