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. |
|
|
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. |
|
|
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 |
|
|
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 tINNER JOIN Destinations d on t.[local-origin]=d.originGROUP 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. |
|
|
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 tINNER JOIN Destinations d on t.[local-origin]=d.originGROUP 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 |
|
|
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 tINNER JOIN Destinations d on t.[local-origin]=d.originGROUP 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 ? |
|
|
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. |
|
|
|