?
Solved

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

Posted on 2010-01-10
4
Medium Priority
?
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 82

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 82

Expert Comment

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

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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