need help getting right query

[karimali831]

Hey, need some help please! 

 

Standard query 1:

 

 

Query 2:

 

 

1) in clan1 they vs. all of clan 2, there is a row for every ID

2) but clan1 must vs. all of every other clan1 also

 

does this make sense?

 

so query 2 should be something like this:

 

clan1  clan2

  1        8

  1        10

  1        4

  1        3

  1        7

  1        9

  3        8

  3        10

  3        4

  3        1

  3        7

  3        9

 

etc.

 

anyone change query 2 for me to make it like this please?

[Jessica]

Your query 2 looks almost good but needs to be a left join and I believe the syntax you're using intimates an inner join.

Try doing this (not tested)

SELECT [same]

FROM table a

LEFT JOIN table b

on a.matchNo = b.matchNo

 

This would get you

 

In your first example, unless you cut off rows, there is no example where clan1 = 1 and clan2 = 3.

 

Do you have a separate table of the clan ids? That is what you'll need I think to get the desired result.

Then it would be

SELECT a.*, b.*

FROM clans a

LEFT JOIN clans b

on a.clan_id != b.clan_id

 

Would get you the list of clans vs each other.

[mikosiko]

quick and dirty, but I believe that is what you want (same query that you have before in Query 2 just using JOIN and UNION for the second part)

 

SELECT a.cupid,
       a.laid,
       a.matchno,
       a.clan1,
       b.clan2 AS 'versus',
       a.comment,
       a.1on1
FROM ws_bi2_cup_matches a
     JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan2 != b.clan2 AND b.type = 'gs'
UNION
SELECT a.cupid,
       a.laid,
       a.matchno,
       a.clan1,
       b.clan1 AS 'versus',
       a.comment,
       a.1on1
FROM ws_bi2_cup_matches a
     JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan1 != b.clan1 AND b.type = 'gs'
WHERE a.matchno = '6'
ORDER BY clan1 DESC, versus;

[Barand]

To get every combination of clan x vs clan y you nedd a cartesian (or cross) join.

 

you can either omit the join condition

SELECT a.clan_id, b.clan_id
FROM clan a, clan b
WHERE a.clan_id <> b.clan_id

 

or specify CROSS JOIN

SELECT a.clan_id, b.clan_id
FROM clan a CROSS JOIN clan b
WHERE a.clan_id <>b.clan_id

[karimali831]

Thanks all, this join/cross query is very confusing for me, I tried the union in mikosiko post but it seems to selecting too much!

 

 

Query:

 

SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1
FROM ws_bi2_cup_matches a
JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID
AND b.cupID = 'a'
AND b.matchno = '6'
AND a.clan2 != b.clan2
AND b.type = 'gs'
UNION
SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan1 AS 'versus', a.comment, a.1on1
FROM ws_bi2_cup_matches a
JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID
AND b.cupID = 'a'
AND b.matchno = '6'
AND a.clan1 != b.clan1
AND b.type = 'gs'
WHERE a.matchno = '6'
ORDER BY `clan1` ASC

 

I noticed it selecting rows where matchno = 5 and not 6 as stated in query?

 

[mikosiko]

after small adjustments... try this

(SELECT a.cupid,
       a.ladid,
       a.matchno,
       a.clan1,
       b.clan2 AS 'versus',
       a.comment,
       a.1on1
FROM ws_bi2_cup_matches a
     JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' 
                                        AND b.matchno = '6' 
                                        AND a.clan2 != b.clan2 
                                        AND b.type = 'gs'
WHERE a.matchno = '6'
  AND a.type = 'gs')
UNION
(SELECT a.cupid,
       a.ladid,
       a.matchno,
       a.clan1,
       b.clan1 AS 'versus',
       a.comment,
       a.1on1
FROM ws_bi2_cup_matches a
     JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' 
                                        AND b.matchno = '6' 
                                        AND a.clan1 != b.clan1 
                                        AND b.type = 'gs'
WHERE a.matchno = '6'
  AND a.type = 'gs')
ORDER BY clan1 DESC, versus;

[karimali831]

Sorry I didn't get back sooner and thanks again for the help!

 

I am trying to put this into INSERT INTO()

 

safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) ((
SELECT a.$type,
       a.$type_opp,
       a.matchno,
       a.clan1,
       b.clan2 AS 'versus',
       a.comment,
       a.1on1
FROM ".PREFIX."cup_matches a
     JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = 'a' 
                                        AND b.matchno = '$ID' 
                                        AND a.clan2 != b.clan2 
                                        AND b.type = 'gs'
WHERE a.matchno = '$ID'
  AND a.type = 'gs')
UNION (
SELECT a.$type,
       a.$type_opp,
       a.matchno,
       a.clan1,
       b.clan1 AS 'versus',
       a.comment,
       a.1on1
FROM ".PREFIX."cup_matches a
     JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = 'a' 
                                        AND b.matchno = '$ID' 
                                        AND a.clan1 != b.clan1 
                                        AND b.type = 'gs'
WHERE a.matchno = '$ID'
  AND a.type = 'gs')
ORDER BY clan1 DESC, versus)");

 

I get this error:

 

error=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.cla' at line 1
query=INSERT INTO ws_bi2_cup_matches (cupID, ladID, matchno, clan1, clan2, comment, 1on1) (( SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan2 AS 'versus', a.comment, a.1on1 FROM ws_bi2_cup_matches a JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan2 != b.clan2 AND b.type = 'gs' WHERE a.matchno = '6' AND a.type = 'gs') UNION ( SELECT a.cupID, a.ladID, a.matchno, a.clan1, b.clan1 AS 'versus', a.comment, a.1on1 FROM ws_bi2_cup_matches a JOIN ws_bi2_cup_matches b ON a.cupID = b.cupID AND b.cupID = 'a' AND b.matchno = '6' AND a.clan1 != b.clan1 AND b.type = 'gs' WHERE a.matchno = '6' AND a.type = 'gs') ORDER BY clan1 DESC, versus)

 

I have been trying the last hour to figure what's wrong?

[karimali831]

The SELECT works no problem but using insert into - select says SQL syntax when I attempt to run the query.

[karimali831]

*bump*

[fenway]

Remove the extra parents.

[Jessica]

Remove the extra parents.

But, but, mother's day is this Sunday! Surely it's not the time to remove parents! :-P

[karimali831]

Thanks that worked but there is problem:-

 

It shouldn't be generating the rows in red as they already exist, everything above is fine:-

 

 

Query:-

 

safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) 
(SELECT a.$type,
       a.$type_opp,
       a.matchno,
       a.clan1,
       b.clan2 AS 'versus',
       a.comment,
       a.1on1
FROM ".PREFIX."cup_matches a
     JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = 'a' 
                                        AND b.matchno = '$ID' 
                                        AND a.clan2 != b.clan2 
                                        AND b.type = 'gs'
WHERE a.matchno = '$ID'
  AND a.type = 'gs')
UNION
(SELECT a.$type,
       a.$type_opp,
       a.matchno,
       a.clan1,
       b.clan1 AS 'versus',
       a.comment,
       a.1on1
FROM ".PREFIX."cup_matches a
     JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = 'a' 
                                        AND b.matchno = '$ID' 
                                        AND a.clan1 != b.clan1 
                                        AND b.type = 'gs'
WHERE a.matchno = '$ID'
  AND a.type = 'gs')
ORDER BY clan1 DESC, versus");

 

These rows will already be inserted:-

 

 

It's really close though

 

[Illusion]

which rows are in red ?

 

if  duplicates getting inserted then your select query need to be modified.

[karimali831]

Take a look at my pic above I circled in red where player 1 or player 2 is Karim.

 

The rows showing where Karim is player 1 is right,  there are 7 rows there.

The rows showing where Karim is player 2 is wrong, there are 3 rows.

 

Would you be able to help please?

[Illusion]

Do the following change in your select query

 

a.clan1 != b.clan1 

 

to

 

a.clan1 <  b.clan1 

[karimali831]

Perfect!!

 

Thanks soo much you made my day !!

[karimali831]

Final question, am I able to insert values aswell? Or can it only be from SELECt?

[Illusion]

It should work fine with insert values as well.

[karimali831]

How can I go about doing this? (last request please :-\)

 

This is the code, works fine now with inserting rows, thanks again

the extra column I want to insert data to is "wk" and the values of it should be 1-7 as shown:

 

 

     for($i = 1; $i <= 8; $i++) {
     
        switch($i) {
	   case 1: $var_alpha = $rows_a; $let_alpha = 'a';
	   break;
	   case 2: $var_alpha = $rows_b; $let_alpha = 'b';
	   break;
	   case 3: $var_alpha = $rows_c; $let_alpha = 'c';
	   break;
	   case 4: $var_alpha = $rows_d; $let_alpha = 'd'; 
	   break;
	   case 5: $var_alpha = $rows_e; $let_alpha = 'e'; 
	   break;
	   case 6: $var_alpha = $rows_f; $let_alpha = 'f';
	   break;
	   case 7: $var_alpha = $rows_g; $let_alpha = 'g'; 
	   break;
	   case 8: $var_alpha = $rows_h; $let_alpha = 'h';
	   break;
	}
     
        if($var_alpha != $max_participants) {

                   safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) 
                   (SELECT a.$type,
                          a.$type_opp,
                          a.matchno,
                          a.clan1,
                          b.clan2 AS 'versus',
                          a.comment,
                          a.1on1
                   FROM ".PREFIX."cup_matches a
                        JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' 
                                                           AND b.matchno = '$ID' 
                                                           AND a.clan2 != b.clan2				
                                                           AND b.type = 'gs'
                   WHERE a.matchno = '$ID'
                     AND a.type = 'gs')
                   UNION
                   (SELECT a.$type,
                          a.$type_opp,
                          a.matchno,
                          a.clan1,
                          b.clan1 AS 'versus',
                          a.comment,
                          a.1on1
                   FROM ".PREFIX."cup_matches a
                        JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' 
                                                           AND b.matchno = '$ID' 
                                                           AND a.clan1 <  b.clan1 
                                                           AND b.type = 'gs'
                   WHERE a.matchno = '$ID'
                     AND a.type = 'gs')
                   ORDER BY clan1 DESC, versus");

	}
     
     }

[karimali831]

Oops image disappear:

 

http://s17.postimage.org/w358jzhe5/success.png

[Illusion]

if the index needs to be reset for every new clan1 then it gets very complicated.

 

This is easy as you can declare a mysql variable and increment it by 1 for each record

 

A- B-1

A-C-2

A- D-3

B-C-4

B-D-5

 

This is complicated

 

A- B-1

A-C-2

A- D-3

B-C-1

B-D-2

 

 

which is your scenario ?

 

 

 

 

 

[karimali831]

I see the problem now, there cannot be the same team in the same week more than once.

Each team has 7 matches each, so it is 7 weeks.

 

It doesn't have to be in the insert query I can use update after insert.

 

I will re-think this later.

 

http://s16.postimage.org/th6kv9m77/PROB3.png

[karimali831]

I just noticed something I can't believe I didn't spot, it generates 7 matches for clan1 which are all the matches but for clan2 is only generates 4 matches if you take a look at image in previous post.

[Illusion]

I just noticed something I can't believe I didn't spot, it generates 7 matches for clan1 which are all the matches but for clan2 is only generates 4 matches if you take a look at image in previous post.

 

That is because clan2 player is not playing match with another clan2 player ... in that case you need to have another union

 

 

[karimali831]

I fixed that, this is final scenario now:

 

If it is possible to insert the week values whether insert() or update() that would be great,

it does seem complicated!  you see each match a team has there are 7 so the week

values should be inserted as follow:-

 

week 1 values are already inserted, each week there are 4 matches.

 

http://s15.postimage.org/s4x5i0ard/success_final.png

 

 

Code:

     for($i = 1; $i <= 8; $i++) {
     
        switch($i) {
	   case 1: $var_alpha = $rows_a; $let_alpha = 'a';
	   break;
	   case 2: $var_alpha = $rows_b; $let_alpha = 'b';
	   break;
	   case 3: $var_alpha = $rows_c; $let_alpha = 'c';
	   break;
	   case 4: $var_alpha = $rows_d; $let_alpha = 'd'; 
	   break;
	   case 5: $var_alpha = $rows_e; $let_alpha = 'e'; 
	   break;
	   case 6: $var_alpha = $rows_f; $let_alpha = 'f';
	   break;
	   case 7: $var_alpha = $rows_g; $let_alpha = 'g'; 
	   break;
	   case 8: $var_alpha = $rows_h; $let_alpha = 'h';
	   break;
	}
     
        if($var_alpha != $max_participants) {

                   safe_query("INSERT INTO ".PREFIX."cup_matches ($type, $type_opp, matchno, clan1, clan2, comment, 1on1) 
                   (SELECT a.$type,
                          a.$type_opp,
                          a.matchno,
                          a.clan1,
                          b.clan2 AS 'versus',
                          a.comment,
                          a.1on1
                   FROM ".PREFIX."cup_matches a
                        JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' 
                                                           AND b.matchno = '$ID' 
                                                           AND a.clan2 != b.clan2				
                                                           AND b.type = 'gs'
                   WHERE a.matchno = '$ID'
                     AND a.type = 'gs')
                   UNION
                   (SELECT a.$type,
                          a.$type_opp,
                          a.matchno,
                          a.clan2,
                          b.clan1 AS 'versus',
                          a.comment,
                          a.1on1
                   FROM ".PREFIX."cup_matches a
                        JOIN ws_bi2_cup_matches b ON a.$type = b.$type AND b.$type = '$let_alpha' 
                                                           AND b.matchno = '$ID' 
                                                           AND a.clan1 != b.clan1 
                                                           AND b.type = 'gs'
                   WHERE a.matchno = '$ID'
                     AND a.type = 'gs')
                   ORDER BY clan1 DESC, versus");

	}
     
     }