SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 where condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

373 Posts

Posted - 09/18/2013 :  05:45:14  Show Profile  Reply with Quote

i have this:

id depart arrive type journeyNumber
123 car bus 4 1
123 bus van 4 2
456 car bus 2 1
999 bus van 2 1

type=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=US

id depart arrive type journeyNumber
123 bus van 4 2

it suppose to come out as below:

id depart arrive type journeyNumber
123 car bus 4 1
123 bus van 4 2

how can i get as the outcome?

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 10/11/2013 :  07:41:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Does it mean that you want to return all rows for IDs associated with the result?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

marcusn25
Starting Member

Germany
35 Posts

Posted - 10/12/2013 :  21:00:07  Show Profile  Reply with Quote
Not Sure, what you want. Hope this helps

select
*
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

) t
where
IDCount > 1


M. Ncube
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/13/2013 :  01:31:55  Show Profile  Reply with Quote
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 
      )a
inner join tableB b 
on a.contry=b.country 
WHERE a.Cnt > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000