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
 Passenger that participated in every trip
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Proofkyko
Starting Member

6 Posts

Posted - 12/03/2012 :  17:03:42  Show Profile  Reply with Quote
Can anyone help me on this one ?

I need to find a passenger that participated in every trip from a certain local. My problem is how do I look for this certain once it can be any local that exists ?

shilpash
Posting Yak Master

101 Posts

Posted - 12/03/2012 :  17:40:23  Show Profile  Reply with Quote
Not me.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 12/03/2012 :  17:54:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
Can you post table structure (DDL), sample data and expected output? It's nearly impossible to work on a solution without this information.
Go to Top of Page

Proofkyko
Starting Member

6 Posts

Posted - 12/03/2012 :  17:58:30  Show Profile  Reply with Quote
Sorry. I am working over a problem about trips, with passengers etc.. The expected output is the passenger that participated in every trip with a certain origin. I do not know that origin, can be any place that exists. I have table trip(nick-passenger, date, cost, local-origin, local-destiny,car plate), and I believe that we need to work with these data. I also have tables with info about passengers, and car info but I believe that those are not important in this question
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 12/03/2012 :  18:34:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
;WITH Destinations(origin, destinations) AS (SELECT [local-origin], COUNT(DISTINCT [local-destiny]) FROM trip GROUP BY [local-origin])
SELECT t.[nick-passenger], t.[local-origin]
FROM trip t
INNER JOIN Destinations d on t.[local-origin]=d.origin
GROUP BY t.[nick-passenger], t.[local-origin]
HAVING COUNT(DISTINCT t.[local-destiny])=d.destinations
That's a best guess, cannot verify it without sample data.
Go to Top of Page

Proofkyko
Starting Member

6 Posts

Posted - 12/03/2012 :  18:38:27  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

;WITH Destinations(origin, destinations) AS (SELECT [local-origin], COUNT(DISTINCT [local-destiny]) FROM trip GROUP BY [local-origin])
SELECT t.[nick-passenger], t.[local-origin]
FROM trip t
INNER JOIN Destinations d on t.[local-origin]=d.origin
GROUP BY t.[nick-passenger], t.[local-origin]
HAVING COUNT(DISTINCT t.[local-destiny])=d.destinations
That's a best guess, cannot verify it without sample data.



You really are helping man, and sorry if this is a stupid question but I am supposed to write exactly like you right? I mean, COUNT(DISTINCT[local-destiny)] once I do not know which one it local is.. AM I understanding you? Thanks a lot @robvolk
Go to Top of Page

Proofkyko
Starting Member

6 Posts

Posted - 12/03/2012 :  18:48:05  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

;WITH Destinations(origin, destinations) AS (SELECT [local-origin], COUNT(DISTINCT [local-destiny]) FROM trip GROUP BY [local-origin])
SELECT t.[nick-passenger], t.[local-origin]
FROM trip t
INNER JOIN Destinations d on t.[local-origin]=d.origin
GROUP BY t.[nick-passenger], t.[local-origin]
HAVING COUNT(DISTINCT t.[local-destiny])=d.destinations
That's a best guess, cannot verify it without sample data.



It is outputing nick and local-origin right ?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 12/03/2012 :  20:16:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
but I am supposed to write exactly like you right?
Yes, unless you get a syntax error when running that code.
quote:
It is outputing nick and local-origin right ?
Yes. Again, best guess based on what was provided. Can't do anything else without the info I requested earlier.
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.08 seconds. Powered By: Snitz Forums 2000