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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Passenger that participated in every trip

Author  Topic 

Proofkyko
Starting Member

6 Posts

Posted - 2012-12-03 : 17:03:42
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

103 Posts

Posted - 2012-12-03 : 17:40:23
Not me.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-03 : 17:54:23
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 - 2012-12-03 : 17:58:30
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

15732 Posts

Posted - 2012-12-03 : 18:34:53
[code];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[/code]That's a best guess, cannot verify it without sample data.
Go to Top of Page

Proofkyko
Starting Member

6 Posts

Posted - 2012-12-03 : 18:38:27
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 - 2012-12-03 : 18:48:05
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

15732 Posts

Posted - 2012-12-03 : 20:16:02
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
   

- Advertisement -