Newbie query problem (Join?) 1

[Baukep]

Hello,

Starting to teach myself SQL. Goes fine except that I run into new problems every now and again (as expected ;-).

I have 2 tables:
1 Games with 1 record for every game played. Two (different) players with their results. Fields: Gamenr, player1, pl1_points, pl1_turns, player2, pl2_points, pl2_turns etc. In player1 and player2 there is the plyer_id from
2 Players with id, name, adres etc.

For every player I have to Count the gamenr, sum the points etc but my problem is that there are records where player_id 1 is in player1 and in other records player_id 1 is player2.
I think the solution is in joins but in my new books I couldn't find a proper solution (my fault of course).

I have the following:

SELECT ronde, speler1, L1.naam, COUNT( wnr ) AS "Gespeeld", SUM( s1_car ) , SUM( s1_brtn )
FROM (
wedstrijden1 AS W1
INNER JOIN leden AS L1 ON W1.speler1 = L1.lidnr
INNER JOIN leden AS L2 ON W1.speler2 = L2.lidnr
)
WHERE seizoen = "2006/07"
GROUP BY ronde, speler1, L1.naam
ORDER BY 1 , 2,

I know some fields are in dutch, but hope this gives enough info. I also know the select is far from right, but I tried IF's , other joins (left , right) but I don't get any further so.....

I could really use some advice here

Thanks in advance

Bauke (Holland)

 

[r937]

select ronde
     , speler
     , [b]L.naam[/b]
     , count(wnr) AS "Gespeeld"
     , SUM( s1_car ) 
     , SUM( s1_brtn )
  from (
select ronde
     , [b]speler1 as speler[/b]
     , wnr
     , s1_car
     , s1_brtn
  from wedstrijden1
 WHERE seizoen = "2006/07"
[b]union all[/b]
select ronde
     , [b]speler2 as speler[/b]
     , wnr
     , s1_car
     , s1_brtn
  from wedstrijden1
 WHERE seizoen = "2006/07"
       ) as d
inner
  join leden AS L 
    ON L.lidnr = d.speler
group
    by ronde
     , speler
     , L.naam

r937.com | rudy.ca

 

[Baukep]

Thank you so much. Changed a little (in the second select s2) and it works great.

One more question. I now have enough to calculate the points each player gets. Have two more field for calc point s1_calcpoints and s2_calcpoints.
Should I update the record in a query or is this something for a stored procedure (how). The way the points are calculated in seizoen 2005/06 differs from 2006/07.

Or is there even a better way do do this?

Bauke

 

[r937]

no idea what those calcs are

i would not store something that is obtainable via query

r937.com | rudy.ca

 

[Baukep]

You are right. The reason I added these fields (columns) is that I want to make sure I get the same results in the query. If that is oke the extra fields are removed.

(later)

and I have made some long nested IF's and its working now.

Thanks for the reply!

Bauke

 

[Baukep]

Hello (again),

Like you (R937) said I shouldn't store something thats obtainable via a query. And that leads to the following problem...........but I'm learning a lot here!
In the query below I use a column named s1_temaken and s2_temaken. These values can obtained fro another table:
Moyenne fields: lidnr, seizoen, ronde, moyenne
s1_temaken is moyenne *25
With:
SELECT W.seizoen AS Seizoen, W.ronde, speler1 AS Speler, wnr AS "Wnr", s1_temaken, M.moy*25 AS Moet
FROM wedstrijden AS W, moyenne AS M
WHERE W.seizoen = "2006/07"
AND M.lidnr = W.speler1
AND M.seizoen = W.seizoen
AND M.ronde = SUBSTRING( W.ronde, 1, 5 )
ORDER BY wnr DESC

I get what I want for speler1, but how do I incorporate something like that in the query below?
I tried different ways but all I get is errors ;-(

Again I could use some help here

Thanks in advance

Bauke

SELECT seizoen AS Seizoen
, ronde AS Ronde
, wnr AS "Wedstr. nr"
, brtn AS Beurten
, speler AS Speler
, L1.naam AS Naam
, temaken AS "Te maken"
, car AS "Caramb."
, Afwgem AS "Afw. gem."
, perc AS "Perc."
, berpunten AS Punten
FROM (
SELECT seizoen
, ronde
, wnr
, brtn
, speler1 AS speler
, s1_temaken AS temaken
, s1_car AS car
, ROUND(s1_car/brtn,4)-ROUND(s1_temaken/25,4) AS Afwgem
, ROUND(s1_car/s1_temaken*100,3) AS perc
, IF(ROUND(s1_car/s1_temaken*100,3) >= 100 AND ROUND(s2_car/s2_temaken*100,3) >= 100, 1,
IF(ROUND(s1_car/s1_temaken*100,3) = ROUND(s2_car/s2_temaken*100,3), 1,
IF(ROUND(s1_car/s1_temaken*100,3) > ROUND(s2_car/s2_temaken*100,3) AND ROUND(s1_car/s1_temaken*100,3) >= 100, 3,
IF(ROUND(s1_car/s1_temaken*100,3) > ROUND(s2_car/s2_temaken*100,3) AND ROUND(s1_car/s1_temaken*100,3) < 100,2,0)))) AS berpunten
FROM wedstrijden WHERE seizoen="2006/07"
UNION ALL
SELECT seizoen
, ronde
, wnr
, brtn
, speler2 AS speler
, s2_temaken AS temaken
, s2_car AS car
, ROUND(s2_car/brtn,4)-ROUND(s1_temaken/25,4) AS Afwgem
, ROUND(s2_car/s2_temaken*100,3) AS perc
, IF(ROUND(s2_car/s2_temaken*100,3) >= 100 AND ROUND(s1_car/s1_temaken*100,3) >= 100, 1,
IF(ROUND(s2_car/s2_temaken*100,3) = ROUND(s1_car/s1_temaken*100,3), 1,
IF(ROUND(s2_car/s2_temaken*100,3) > ROUND(s1_car/s1_temaken*100,3) AND ROUND(s2_car/s2_temaken*100,3) >= 100, 3,
IF(ROUND(s2_car/s2_temaken*100,3) > ROUND(s1_car/s1_temaken*100,3) AND ROUND(s2_car/s2_temaken*100,3) < 100,2,0)))) AS berpunten
FROM wedstrijden WHERE seizoen="2006/07"
) AS d
INNER JOIN leden AS L1 ON L1.lidnr= d.speler
ORDER BY seizoen, ronde, wnr DESC

 

[r937]

pretty hard to know what's wrong here

any chance you could mention what the errors are?

r937.com | rudy.ca

 

[Baukep]

Hello,

The query (big one; second) runs fine!

The problem is that I want to incorporate the first (little one) so I can calculate the 'temaken' field for speler1 (first part of the FROM) and speler2 (second part) and get rid of the extra fields (s1_tamaken and s2_temaken). But how do I put in this. Another UNION perhaps? How?

Hope this makes it clearer.

Bauke

 

[r937]

no, it's not clearer, sorry, i don't know what you're doing



r937.com | rudy.ca

 

[Baukep]

This is the fine running query: In this listing I will type wahat I want

SELECT seizoen AS Seizoen
, ronde AS Ronde
, wnr AS "Wedstr. nr"
, brtn AS Beurten
, speler AS Speler
, L1.naam AS Naam
, temaken AS "Te maken"
, car AS "Caramb."
, Afwgem AS "Afw. gem."
, perc AS "Perc."
, berpunten AS Punten
FROM (
SELECT seizoen
, ronde
, wnr
, brtn
, speler1 AS speler
, s1_temaken AS temaken
***** s1_temaken is a field but there's no need for it because I can calculate it from table moyenne field moy where lidnr, seizoen and ronde are the same
, s1_car AS car
, ROUND(s1_car/brtn,4)-ROUND(s1_temaken/25,4) AS Afwgem
, ROUND(s1_car/s1_temaken*100,3) AS perc
, IF(ROUND(s1_car/s1_temaken*100,3) >= 100 AND ROUND(s2_car/s2_temaken*100,3) >= 100, 1,
IF(ROUND(s1_car/s1_temaken*100,3) = ROUND(s2_car/s2_temaken*100,3), 1,
IF(ROUND(s1_car/s1_temaken*100,3) > ROUND(s2_car/s2_temaken*100,3) AND ROUND(s1_car/s1_temaken*100,3) >= 100, 3,
IF(ROUND(s1_car/s1_temaken*100,3) > ROUND(s2_car/s2_temaken*100,3) AND ROUND(s1_car/s1_temaken*100,3) < 100,2,0)))) AS berpunten
FROM wedstrijden WHERE seizoen="2006/07"
UNION ALL
SELECT seizoen
, ronde
, wnr
, brtn
, speler2 AS speler
, s2_temaken AS temaken
***** s2._temaken is also a field but there's no need for it because I can calculate it from table moyenne field moy where lidnr, seizoen and ronde are the same
, s2_car AS car
, ROUND(s2_car/brtn,4)-ROUND(s1_temaken/25,4) AS Afwgem
, ROUND(s2_car/s2_temaken*100,3) AS perc
, IF(ROUND(s2_car/s2_temaken*100,3) >= 100 AND ROUND(s1_car/s1_temaken*100,3) >= 100, 1,
IF(ROUND(s2_car/s2_temaken*100,3) = ROUND(s1_car/s1_temaken*100,3), 1,
IF(ROUND(s2_car/s2_temaken*100,3) > ROUND(s1_car/s1_temaken*100,3) AND ROUND(s2_car/s2_temaken*100,3) >= 100, 3,
IF(ROUND(s2_car/s2_temaken*100,3) > ROUND(s1_car/s1_temaken*100,3) AND ROUND(s2_car/s2_temaken*100,3) < 100,2,0)))) AS berpunten
FROM wedstrijden WHERE seizoen="2006/07"
) AS d
INNER JOIN leden AS L1 ON L1.lidnr= d.speler
ORDER BY seizoen, ronde, wnr DESC


What UNION should I make and where to make the mentioned calc possible?

Hope this is better ;-)

Bauke