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

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.

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/

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

"former" engineer?

I am sure that he means formal engineer, but then again, being an engineer can suck sometimes...maybe he wants out?!?

gratz!

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