Newbie query question 2

[Baukep]

Hello,

I'm using Mepis 6.0 with XAMPP (mysql 5.0.24) and I'm
completely stuck I think,

I have two tables:
one for members (id, name, adres etc) and
one for the played games (date, season, round, gamenr, member_id1, score, member_id2, score etc)

I already have queries in which i calculate the points each player gets, make total score lists etc

One thing (for now) I cannot accomplish:
I want for every member a list with all the members they have not played against this season and this round (a gametype
in which everybody should play against everbody else).

The problem is that sometimes members.id 1 is in one game games.member_id1 and sometimes games.member_id2

I've been struggling with joins, nested joins, but if something came out it just was not what I wanted.

It must have been done before but for a newbie like me its almost impossible.

I hope this makes my problem clear.

Please give me a solution or the light because I running in the dark now (only with this problem)

Thanks in advance

Bauke

 

[feherke]

Hi

[b]select[/b]
m.*
[b]from[/b] members m
[b]left join[/b] (
  [b]select[/b]
  member_id2 id
  [b]from[/b] games
  [b]where[/b] member_id1=[green][i]current_member[/i][/green] [b]and[/b] season=[green][i]current_season[/i][/green]
  [b]union[/b]
  [b]select[/b]
  member_id1
  [b]from[/b] games
  [b]where[/b] member_id2=[green][i]current_member[/i][/green] [b]and[/b] season=[green][i]current_season[/i][/green]
) g [b]using[/b] (id)
[b]where[/b] g.id [b]is null[/b];

Feherke.

http://rootshell.be/~feherke/

 

[TonyGroves]

You could also use:
[tt]
SELECT m1.id,m1.name,GROUP_CONCAT(m2.name ORDER BY m2.name)
FROM
members m1
JOIN members m2 ON m1.id<>m2.id
LEFT JOIN games g
ON
g.season=$season
AND g.round=$round
AND
(
(m1.id=g.member_id1 AND m2.id=g.member_id2)
OR (m1.id=g.member_id2 AND m2.id=g.member_id1)
)
WHERE g.gameno IS NULL
GROUP BY m1.id
[/tt]

 

[Baukep]

I'm sorry but I must have made a mistake. Here are the tables I use

table leden (en: members)

Field Type Collation Extra
l_id tinyint(3) auto_increment
l_naam varchar(25) utf8_unicode_ci
l_adres varchar(25) utf8_unicode_ci
l_pcode varchar(7) utf8_unicode_ci
l_plaats varchar(15) utf8_unicode_ci
l_tel varchar(11) utf8_unicode_ci
l_aseizoen varchar(7) utf8_unicode_ci
l_eseizoen varchar(7) utf8_unicode_ci

prim key: l_id

table wedstrijden (en: games)

Field Type Collation
w_datum date
w_seizoen varchar(7) utf8_unicode_ci
w_ronde varchar(8) utf8_unicode_ci
w_wnr smallint(3) (en: gameno)
w_brtn smallint(3)
w_speler1 smallint(3) the l_id from leden
w_s1_temaken smallint(3)
w_s1_car smallint(3)
w_s1_hserie smallint(3)
w_speler2 smallint(3) the l_id from leden
w_s2_temaken smallint(3)
w_s2_car smallint(3)
w_s2_hserie smallint(3)

prim key: w_seizoen, w_wnr

My translation from your code to my tables:

select
l.*
from leden l
left join (
select
w_speler2
from wedstrijden
where w_speler1= l.l_id and w_seizoen="2006/07"
union
select
w_speler1
from wedstrijden
where w_speler2= l.l_id and w_seizoen="2006/07"
) w using (w_wnr)
where w.w_wnr is null;

Result: #1054 - Unknown column 'l_id' in 'where clause'
but I think that the w_speler1 (is the l_id) should be compared with the l_id in leden. And l_id is selected in the first select.

So, what wrong? (beside me being a newbie)

Bauke

 

[feherke]

Hi

There is no access in the inner [tt]select[/tt] to leden, because the [tt]join[/tt] is in the outer [tt]select[/tt] only. There you have to put the identifier of the member for who you ask the unplayed games.

select
l.*
from leden l
left join (
  select
  w_speler2 [red]l_id[/red]
  from wedstrijden
  where w_speler1=[red]current_member[/red] and w_seizoen="2006/07"
  union
  select
  w_speler1
  from wedstrijden
  where w_speler2=[red]current_member[/red] and w_seizoen="2006/07"
) w using ([red]l_id[/red])
where w.[red]l_id[/red] is null;

Also note that I put an alias for the field returned by the inner [tt]select[/tt], identical with the identifier in leden table.

So, what wrong?

May be more then mentioned above. Hmm... Maybe was better to give us the original names by

table wedstrijden (en: games)

Yes, we know that "games" is in english. But wedstrijden ?

Feherke.

http://rootshell.be/~feherke/

 

[Baukep]

Learning every day. Didn't know that giving an alias without AS was possible ;-).

Two more questions:

When I make current_member 1 then I get a list where nr 1 has to play against himself (nice; always winner ;-)). But impossible.

Is it possible to get this for all players in one query (for next)?

Like:

1 Feherke 3 Billy
1 Feherke 5 William
1 Feherke 8 Bauke
.......
8 Bauke 1 Feherke
8 Bauke 3 Billy


PS rootshell.BE ? Then your dutch should be better than my English ;-)

Thank you very much for your time (tijd)

Bauke

 

[TonyGroves]

I don't want to interrupt and spoil the fun, but have you looked at my solution at all? You might find that it works!

 

[Baukep]

Hello TonyGroves,

Your appreciated answer came in second and as a newbie I first try and investigate the first and then the second. Hope to be learning from both!

Thanks and I will be back here.

Bauke

 

[feherke]

Hi

Didn't know that giving an alias without AS was possible

Yes, it is. Is also possible in Oracle, in PostgreSQL is possible only for tables, not for fields.

I get a list where nr 1 has to play against himself

Oops. I missed that.

Is it possible to get this for all players in one query

No. My previous query is not suitable for that. The one suggested TonyGroves, seems to be possible to extend.

I try again. To be easier to check, I show my test tables too.

[blue]mysql>[/blue] [b]select[/b] * [b]from[/b] leden;
+------+
| l_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

[blue]mysql>[/blue] [b]select[/b] * [b]from[/b] wedstrijden;
+-----------+-----------+-----------+
| w_seizoen | w_speler1 | w_speler2 |
+-----------+-----------+-----------+
|         1 |         1 |         2 |
|         2 |         1 |         3 |
|         2 |         4 |         1 |
|         2 |         2 |         3 |
+-----------+-----------+-----------+
4 rows in set (0.00 sec)

[gray]-- unplayed games in season 2 for member 2[/gray]
[blue]mysql>[/blue] [b]select[/b] p.* [b]from[/b] ([b]select[/b] l1.l_id l_id1,l2.l_id l_id2 [b]from[/b] leden l1,leden l2 [b]where[/b] l2.l_id>l1.l_id) p [b]left join[/b] ([b]select[/b] least(w_speler1,w_speler2) l_id1,greatest(w_speler1,w_speler2) l_id2 [b]from[/b] wedstrijden [b]where[/b] w_seizoen=[green][i]2[/i][/green]) w [b]using[/b] (l_id1,l_id2) [b]where[/b] (p.l_id1=[green][i]2[/i][/green] [b]or[/b] p.l_id2=[green][i]2[/i][/green]) [b]and[/b] w.l_id1 [b]is null[/b];
+-------+-------+
| l_id1 | l_id2 |
+-------+-------+
|     1 |     2 |
|     2 |     4 |
|     2 |     5 |
+-------+-------+
3 rows in set (0.00 sec)

[gray]-- all possible game's status in season 2[/gray]
[blue]mysql>[/blue] [b]select[/b] p.*,if(w.l_id1 is null,[i]'-'[/i],[i]'Played'[/i]) status [b]from[/b] ([b]select[/b] l1.l_id l_id1,l2.l_id l_id2 [b]from[/b] leden l1,leden l2 [b]where[/b] l2.l_id>l1.l_id) p [b]left join[/b] ([b]select[/b] least(w_speler1,w_speler2) l_id1,greatest(w_speler1,w_speler2) l_id2 [b]from[/b] wedstrijden [b]where[/b] w_seizoen=[green][i]2[/i][/green]) w [b]using[/b] (l_id1,l_id2);
+-------+-------+--------+
| l_id1 | l_id2 | status |
+-------+-------+--------+
|     1 |     2 | -      |
|     1 |     3 | Played |
|     2 |     3 | Played |
|     1 |     4 | Played |
|     2 |     4 | -      |
|     3 |     4 | -      |
|     1 |     5 | -      |
|     2 |     5 | -      |
|     3 |     5 | -      |
|     4 |     5 | -      |
+-------+-------+--------+
10 rows in set (0.00 sec)

rootshell.BE ? Then your dutch should be better than my English

Nope. I am hungarian. Only the free shell account service I use is from Belgium.

Feherke.

http://rootshell.be/~feherke/

 

[Baukep]

Feherke thanks! I will have a close look at your code!

I started to look at the code of TonyGroves and indeed that works great!
Thank you TonyGroves!

TonyGroves, one question:

This is the code sofar:
SELECT l1.l_id AS Lidnr, l1.l_naam AS Naam, GROUP_CONCAT(l2.l_naam ORDER BY l2.l_id SEPARATOR ", ") AS "Open partijen"
FROM
leden l1
JOIN leden l2 ON l1.l_id<>l2.l_id
LEFT JOIN wedstrijden w
ON
w.w_seizoen="2006/07"
AND w.w_ronde="Libre 1"
AND
(
(l1.l_id=w.w_speler1 AND l2.l_id=w.w_speler2)
OR (l1.l_id=w.w_speler2 AND l2.l_id=w.w_speler1)
)
WHERE w.w_wnr IS NULL AND (l1.l_eseizoen IS NULL OR l1.l_eseizoen = "2006/07")
GROUP BY l1.l_id

As you can see I've managed to leave the retarded players out with (AND (l1.l_eseizoen IS NULL OR l1.l_eseizoen = "2006/07")) but in the GROUP_CONCAT they are still there. Where should I put the where to leave them out in the GROUP_CONCAT?

After that it is exactly what I need! Thanks again!

Bauke

 

[TonyGroves]

Your extra condition excludes "retarded" players from l1, but not from l2. Also, it would be better to put that in the JOIN condition:
[tt]
leden l1
JOIN leden l2
ON
l1.l_id<>l2.l_id
AND (l1.l_eseizoen IS NULL OR l1.l_eseizoen = "2006/07")
AND (l2.l_eseizoen IS NULL OR l2.l_eseizoen = "2006/07")
[/tt]

 

[Baukep]

Thank you,

I tried something similar but on the wrong spot I think ;-)

Your right, retarded is not what I ment. Just gone as members. English AND sql are both difficult!

Bauke