Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-09-18 : 05:45:14
|
i have this:id depart arrive type journeyNumber123 car bus 4 1123 bus van 4 2456 car bus 2 1999 bus van 2 1type=4 meants connecting route, type=2 means none connecting route.arrive=van are from US country.i would like to pull out all which has connecting route and from US country.i did this but it only come out as below:select * from tableA inner join tableB on tableA.contry=tableB.country where type=4 and country=USid depart arrive type journeyNumber123 bus van 4 2it suppose to come out as below:id depart arrive type journeyNumber123 car bus 4 1123 bus van 4 2how can i get as the outcome? |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-10-11 : 07:41:16
|
Does it mean that you want to return all rows for IDs associated with the result?MadhivananFailing to plan is Planning to fail |
|
|
marcusn25
Yak Posting Veteran
56 Posts |
Posted - 2013-10-12 : 21:00:07
|
Not Sure, what you want. Hope this helpsselect *from (select TableA.ID ,TableA.Type ,TableA.Arrive , count(*) over (partition by TableA.ID) as IDCount FROM TableA tableA inner join tableB on tableA.country= tableB.country where tableA.type=4 and tableB.country=US ) twhere IDCount > 1 M. Ncube |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-13 : 01:31:55
|
Maybe this?select * from (select *,SUM(CASE WHEN type=4 and country='US' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS Cnt FROM tableA )ainner join tableB b on a.contry=b.country WHERE a.Cnt > 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|