Selecting multi values from Views in a join? help!
-
Hi,
I need to do the following:
Returns a list of IDS that exist in a view, but they must match all the criteria not just one.
I have so far:
Select A.pId from tblPrp A
Inner Join FacilityText F on A.pId = F.pId AND F.fac_id = 1 AND F.fac_id = 2
The FacilityText is a view:
fac_heading pId fac_id
Children 480 1
Resturant 480 2
Children 481 1
Children 482 1
Children 483 1
Children 484 1
Children 485 1
Children 486 1
Children 487 1
Children 488 1
I know you can't do fac_id = 1 AND fac_id = 2, but if i use OR then it will just return any of the pId's that have the fac_id of 1 or 2. What i want the above query to return (in this example) should be:
pId
480
480
If anyone could help, it would be great!
Thanks
John
-
Try this
Select A.pId from tblPrp A
where A.p_id in (
select p_id from FacilityText where fac_id in (1,2)
group by p_id
having count(id)=2)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi,
Thanks very much, works really well.
And thank you for the speedy reply
Regards
John
-
I know you have an answer; I would just like to say that on purely aesthetic grounds I would prefer
select A.pId from tblPrp A
inner join FacilityText F1 on A.pId = F1.pId and F1.fac_id = 1
inner join FacilityText F2 on A.pId = F2.pId and F1.fac_id = 2
with parentheses if the joins demand them. No convincing reason, just an alternative way of expressing the same thing. I think mine might be faster on some large data sets, because I let the server decide which order to do the restrictions in, rather than forcing the 'has a 1 and has a 2' join to be done first, which is effectively what the first answer does. But they are semantically equivalent, I think
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply