?
Solved

How to use two select elements as if they were two scalars returned from subquery

Posted on 2010-01-10
4
Medium Priority
?
205 Views
Last Modified: 2012-05-08
The below doesn't work as a subquery because I'm trying to use the two select elements as if they were two elements in a set returned to the outer query.  The values for Ref_for_Care_Coord_ID1 and Ref_for_Care_Coord_ID2 are both int as is the ct.contactid and there are rows to make the matching occur, I'm just wondering what technique I can use to make this work.

TIA
select ct.firstname, ct.lastname, ct.workphone, ct.Email
from contacts ct
where ct.contactid in (select 
                        Ref_for_Care_Coord_1ID,
                        Ref_for_Care_Coord_2ID 
                        from collaboratives 
                        where collabrecid = 30)
go

Open in new window

0
Comment
Question by:dneill8
  • 2
4 Comments
 
LVL 83

Accepted Solution

by:
leakim971 earned 2000 total points
ID: 26280181
Hello dneill8,

Try :


select ct.firstname, ct.lastname, ct.workphone, ct.Email
from contacts ct
where ct.contactid in (select 
                        Ref_for_Care_Coord_1ID
                        from collaboratives 
                        where collabrecid = 30
                       union 
                        select 
                        Ref_for_Care_Coord_2ID 
                        from collaboratives 
                        where collabrecid = 30
                      )
go

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26280188
Something like this perhaps:

select ct.firstname, ct.lastname, ct.workphone, ct.Email
from contacts ct
        LEFT JOIN collaboratives c1 On ct.contactid = c1.Ref_for_Care_Coord_1ID And c1.collabrecid = 30
        LEFT JOIN collaboratives c2 On ct.contactid = c2.Ref_for_Care_Coord_2ID ANd c2.collabrecid = 30
0
 

Author Closing Comment

by:dneill8
ID: 31675312
Thanks for the fast solution!
0
 
LVL 83

Expert Comment

by:leakim971
ID: 26280222
You're welcome! Thanks for the points! Have a good week!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question