Help on a SQL Query [TBS]

::[Home]

::[News]

::[Challenges]

::[Ranking]

::[My Account]

::[Forum]

::[Chat]

::[Private Messages]

::[Vulnerable Code]

::[Tutorials]

::[Downloads/Scripts]

::[Support]

::[Links]

::[Contact/FAQ]

::[Login]

0 anonymous

Views:	1339343	Challenges:	342
Users:	12728	Online:	0

Help on a SQL Query – 8 Posts

10/16/2024 13:00
Hessiann 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
Hello World!
Im stuck with a SQL (SQL Server 2008) query. To sum up, we have a Table \"Teams\" which has two columns, teamID and name. Then we have another Table \"Matches\" that has, among other things, matchID, localTeamID, visitorTeamID,localTeamGoals,visitorTeamGoals....etc etc. So the query requires the following: \"Return the name of the teams, indicating the number of matches played as local, and the number of matches played as visitor, as two different columns, in a single query, not allowed to use Cursors\".

The problem is, you need the team names, so when you do the join between Matches and Teams on, lets say, localTeamID = teamID, you only get the local matches...so in order to get the visitors aswell...you join again the same teamID with visitorTeamID....but there is my problem, for each local match, i get all the visitors matches...

How can I obtain the right result like
Team LocalMatches VisitorMatches Real Madrid 4 5 Manchester 5 4

Note that \"sums\" or \"counts\" in the query may differ.
Thanks in advance, I hope anyone can help me here.

Hess
Edit

Reply

Quote

0 Likes
10/16/2024 13:00

sniperkid 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified

I think i understand the issue, have a look into at PIVOT, i'll have a look at the weekend if its still an issue.

Edited by sniperkid 12/11/2014 18:30 ago

Edit

Reply

Quote

0 Likes
10/16/2024 13:00

dalfor 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified

I was also thinking of group by as a solution. Here is a link to the difference between pivot and group by

http://mattclingan.wordpress.com/2008/02/08/pivot-tables-vs-sum-w-group-by-rollups-vs-cube/

Edit

Reply

Quote

0 Likes
10/16/2024 13:00
Hessiann 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
Ty guys,
Unfortunately I saw your replies after taking the exam. Anyway, a friend told me the solution to this riddle, and when I was taking the exam, the damn profesor used a similar query and I needed to use the same method to solve it. Lucky for me, I already knew how to sort that problema.

You can solve these kind or problems using subqueries inside the FROM clause:

So...I didnt knew you can use subqueries to créate new tables and solve these particular problems this way.
Hope it can be usefull for anyone else.

Cheers!

PS: I passed that damn exam and I have to take only 1 more to be a former engineer $\":)\"$
Edit

Reply

Quote

0 Likes
10/16/2024 13:00

Baubbi 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified

\"former\" engineer?

Edit

Reply

Quote

0 Likes
10/16/2024 13:00

dalfor 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified

I am sure that he means formal engineer, $\":hihi:\"$ but then again, being an engineer can suck sometimes...maybe he wants out?!? $\":student:\"$ $\":drink3:\"$

$\"Quote\"$ Quote from Baubbi:
\"former\" engineer?

Edit

Reply

Quote

0 Likes
10/16/2024 13:00

sniperkid 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified

gratz! $\":king:\"$

Edit

Reply

Quote

0 Likes
10/16/2024 13:00

Hessiann 00
Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified

$\"Quote\"$ Quote from dalfor:
I am sure that he means formal engineer, $\":hihi:\"$ but then again, being an engineer can suck sometimes...maybe he wants out?!? $\":student:\"$ $\":drink3:\"$

$\"Quote\"$ Quote from Baubbi:
\"former\" engineer?

Ty dalfor for correcting me and for correctly explain what I meant to say. And I still dont wanna out. What I want is a bigger salary right now $\":pc4:\"$

Edit

Reply

Quote

0 Likes