getting rows from 2 tables

[lashki]

Hi,

I've got 2 tables where I want to extrat some rows from.

The first table, inschrijving, looks like this:

+---------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+---------------+-------------+------+-----+---------+
| ploegid | int(3) | | PRI | NULL |
| voornaam | varchar(25) | YES | | NULL |
| achternaam | varchar(25) | YES | | NULL |
| ploegnaam | varchar(50) | YES | | NULL |
| email | varchar(50) | YES | | NULL |
| speler1 | int(3) | YES | | NULL |
| speler2 | int(3) | YES | | NULL |
| speler3 | int(3) | YES | | NULL |
| speler4 | int(3) | YES | | NULL |
| reservespeler | int(3) | YES | | NULL |
+---------------+-------------+------+-----+---------+

The second one, g4punten, looks like this:

+-------------+--------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------+--------+------+-----+---------+
| spelerid | int(3) | YES | | NULL |
| totaalvorig | int(3) | YES | | NULL |
| totaalnu | int(3) | YES | | NULL |
+-------------+--------+------+-----+---------+
The numeber you find in inschrijving for the values of sepler1, speler2, speler3, speler4 and reservespeler can also be find in g4 punten as spelerid.

Now what I want to display should look like this:
ploegnaam , sum of totaalnu for sepler1, speler2, speler3, speler4 and reservespeler.

I tried using the following query, but it didn't work:
select ploegid, ploegnaam, speler1, speler2, speler3, speler4, reservespeler,
sum(totaalnu), sum(totaalvorig)
from inschrijving, g4punten
where spelerid in (speler1, speler2, speler3, speler4, reservespeler)
group by totaalnu, totaalvorig;

It showed to many rows and the sums weren't correct.

Someone an idea?

 

[KarveR]

Havent changd the query, just the way it looks at the two tables. see if this has better results.

SELECT a.ploegid, a.ploegnaam, a.speler1, a.speler2, a.speler3, a.speler4, a.reservespeler,
sum(b.totaalnu), sum(b.totaalvorig)
FROM inschrijving a, g4punten b
WHERE b.spelerid in (a.speler1, a.speler2, a.speler3, a.speler4, a.reservespeler)
GROUP BY b.totaalnu, b.totaalvorig;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[lashki]

Tried the query you wrote down, but it gives the same result.

Thx anyway.