logo
::[Home]
::[News]
::[Challenges]
::[Ranking]
::[My Account]
::[Forum]
::[Chat]
::[Private Messages]
::[Vulnerable Code]
::[Tutorials]
::[Downloads/Scripts]
::[Support]
::[Links]
::[Contact/FAQ]
::[Login]
0 anonymous
Views: 1080666 Challenges: 342
Users: 12689 Online: 11

Settings
Search
0 new posts

Help on a SQL Query – 8 Posts

  • Help on a SQL Query

    12/08/2014 20:07
    Hessiann's Avatar Hessiann 2000
    Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
    Hello World!<br>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\".<br><br>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...<br><br>How can I obtain the right result like<br><pre> Team LocalMatches VisitorMatches Real Madrid 4 5 Manchester 5 4 </pre><br><br>Note that \"sums\" or \"counts\" in the query may differ. <br>Thanks in advance, I hope anyone can help me here. <br><br>Hess
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    sniperkid's Avatar sniperkid 5440
    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.
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    dalfor's Avatar dalfor 560
    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<br><br>http://mattclingan.wordpress.com/2008/02/08/pivot-tables-vs-sum-w-group-by-rollups-vs-cube/<br><br>
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    Hessiann's Avatar Hessiann 2000
    Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
    Ty guys,<br>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.<br><br>You can solve these kind or problems using subqueries inside the FROM clause:<br><pre></pre><br><br>So...I didnt knew you can use subqueries to créate new tables and solve these particular problems this way.<br>Hope it can be usefull for anyone else.<br><br>Cheers!<br><br>PS: I passed that damn exam and I have to take only 1 more to be a former engineer <img alt="\&quot;:)\&quot;" src="%5C">
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    Baubbi's Avatar Baubbi 1480
    Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
    \"former\" engineer?
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    dalfor's Avatar dalfor 560
    Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
    I am sure that he means formal engineer, <img alt="\&quot;:hihi:\&quot;" src="%5C"> but then again, being an engineer can suck sometimes...maybe he wants out?!?<img alt="\&quot;:student:\&quot;" src="%5C"> <img alt="\&quot;:drink3:\&quot;" src="%5C"><br><br><div class="\&quot;tbscode_standard_quote_headline\&quot;"><img alt="\&quot;Quote\&quot;" src="%5C">Quote from <a href="%5C">Baubbi</a>:</div><div class="\&quot;tbscode_standard_quote\&quot;">\"former\" engineer?</div>
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    sniperkid's Avatar sniperkid 5440
    Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
    gratz! <img alt="\&quot;:king:\&quot;" src="%5C"><br>
    Edit
    Reply
    Quote
    0 Likes
  • 12/08/2014 20:07
    Hessiann's Avatar Hessiann 2000
    Not SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot SpecifiedNot Specified
    <div class="\&quot;tbscode_standard_quote_headline\&quot;"><img alt="\&quot;Quote\&quot;" src="%5C">Quote from <a href="%5C">dalfor</a>:</div><div class="\&quot;tbscode_standard_quote\&quot;">I am sure that he means formal engineer, <img alt="\&quot;:hihi:\&quot;" src="%5C"> but then again, being an engineer can suck sometimes...maybe he wants out?!?<img alt="\&quot;:student:\&quot;" src="%5C"> <img alt="\&quot;:drink3:\&quot;" src="%5C"><br><br><div class="\&quot;tbscode_standard_quote_headline\&quot;"><img alt="\&quot;Quote\&quot;" src="%5C">Quote from <a href="%5C">Baubbi</a>:</div><div class="\&quot;tbscode_standard_quote\&quot;">\"former\" engineer?</div></div><br><br>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 <img alt="\&quot;:pc4:\&quot;" src="%5C">
    Edit
    Reply
    Quote
    0 Likes