CODEWITHSUNDEEP
sqlsql-server-2008
rkeerthi
rkeerthi

Reputation: 25

sql query to get records based on 2 ID's

I have 2 Tables IlRoutes and PlRoutes and they both have a common RouteID. 

IlRoute:                          PlRoute:
RouteID   CID               RouteID  PlID Division
  1        3                   1      2     71
  2        3                   1      7     29
  3        3                   2      1     78
  4       37                   2      7     21
  5       37                   3      5     50
  6       37                   3      6     50

I want the records where plID = 7 and CID = 3 and also the records based on the RouteID's

Here is the sample output:

RouteID CID  RouteID  PlID Division 
  1      3     1       2     71
  1      3     1       7     29
  2      3     2       1     78
  2      3     2       7     21

any help is greatly appreciated.

Upvotes: 1

Views: 73

Answers (4)

jay.jivani
jay.jivani

Reputation: 1574

select 
    IlRoute.*, PlRoute.* 
from 
    IlRoute 
join 
    PlRoute 
where 
    IlRoute.RouteID = PlRoute.RouteID

Upvotes: 0

Ashley Lee
Ashley Lee

Reputation: 3986

The following meets your criteria and should be the most efficient:

SELECT i.RouteID, i.CID, p.RouteID, p.PlID, pl.Division
FROM IlRoute i
INNER JOIN PlRoute p ON i.RouteID=p.RouteID
CROSS APPLY (SELECT TOP 1 1 FROM PlRoute WHERE RouteID=i.RouteID AND PlID=7) pCriteria
WHERE CID=3

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Assuming I'm understanding your question correctly, you want to return any record that matches with cid = 3 in the IlRoute table, and at least one record in the PlRoute table plId = 7.

First join the tables together, and then use exists to see if there is at least one record with plid = 7:

select i.routeid, i.cid, p.plid, p.division
from ilroute i
  join plroute p on i.routeid = p.routeid 
where i.cid = 3 and 
  exists (select 1
                from PlRoute p2
                where p.routeid = p2.routeid 
                    and p2.plid = 7)

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

 select IlRoute.RouteID, IlRoute.CID,  PlRoute.RouteID,  PlRoute.PlID, PlRoute.Division  
 from  IlRoute,PlRoute 
 where IlRoute.RouteID=PlRoute.RouteID 
 and   IlRoute.CID=3

Upvotes: 0

Related Questions