complicated select query

[safra]

Hi,

I wonder if the following can be done by a sql query only and without the need for extra conditions in php.

I have 3 tables. Table 2 and 3 both have the field 'myName'. This is one of the fields that should be returned but whether it should be selected from table2 or table3 depends on the value of the field playerType in table 1. Is the value 0 then the name should come from table 2. Is the value 1 then the name should come from table 3.

Right now I have the following:

SELECT table1.id, table1.playerType, table1.mydate, table2.myName, table3.myName FROM table1,table2,table3 WHERE table1.mydate='$mydate' AND (table1.myName = table2.id OR table1.myName = table3.id )

Then, while fetching the results, I have the following if-statement

if($myrow[1] == 0){ // field 'playerType'
$name = $myrow[3];
}else{
$name = $myrow[4];
}

I would like to get the correct name directly from the sql query. Is this possible?

Thanks!

[_____]

mmmmm

i think about it
looks the way u did it is the best
i don't know a way to make a decesion on mysql

at the end i am new
and may be others can help better

[tsinka]

Hi,

you can use IF statements in MySQL queries.

Example:

SELECT DISTINCT table1.id, table1.playerType, table1.mydate, 
                IF (table1.playerType='0',table2.myName, table3.myName) 
           FROM table1,table2,table3 
          WHERE table1.mydate='$mydate' 
            AND  IF (table1.playerType='0',table1.myName = table2.id,table1.myName = table3.id) 

Shorter:

SELECT DISTINCT t1.id, t1.playerType, t1.mydate, 
                IF (t1.playerType='0',t2.myName, t3.myName) 
           FROM table1 t1,table2 t2,table3 t3 
          WHERE t1.mydate='$mydate' 
            AND  IF (t1.playerType='0',t1.myName = t2.id,t1.myName = t3.id) 

Something like that worked for me. But I had to use DISTINCT. The values that were returned seemed to be correct.

Hope that solves the problem or at least gives you a direction to search in.

This query was a bit time expensive so your php solution might be faster.

Depending on the MySQL version using UNION might be another way. But I'm not sure.

A very important task is to create indexes on the fields at least used in the where clause where applicable (it's not in every case as easy as creating an index on all fields).

You can use

EXPLAIN sql_query

to see which indexes MySQL uses.

Thomas

[ultraslacker]

This screams database design problem.

[Danicek]

1. I think that syntax needed to write this query is ussually part of SQL extension coming with certain db server. For example in Sybase I could use "case...when...then...else". So should we suppose that you are using mySQL?

2. This whole question seems strange. You should probably remake your database desing.

[safra]

Thanks, tsinka I will try that!

I agree, I am still not sure if I should use two tables or one table for players. Players can be members or visitors. The table for members has different fields then the one for visitors although they have a number of fields in common.

What would you guys do?

[Danicek]

I believe that standart db design should be something like:

players (collumns used by both members and visitors + collumn indicating if certain player is member or visitor)

visitors (collumns specific for visitors)

members (collumns specific for members)

[safra]

Thanks Danicek,

I believe that, unless you include the Name of the player as the primary key or just as one of the fields in table 'players' you would still need an if-clause in the MySQL query to get the correct name straight from the sql query as you still need to check whether the player is a member or a visitor. Or not?

[Danicek]

I do not see problem.
I suppose you will use two queries.
First will join tables players and visitors, second will join tables players and members. You will use one of this queries (I believe you know if you need to use first one or second one). To decide if join first and socend or first and third is not thing of db query.
In both situations you will get all data needed for each type of player.

[safra]

Yes, but my initial question was, if you can do this with one query only and without going through a if-clause in php to get the correct player name. So either I include playername in the players table (in your design structure). Or I include an if-clause, like tsinka showed, in the sql query.

[Danicek]

Of course - answer to your question is "no" or "you need if statement".
However it is normal and it should like this. You should not try to change your db design to let you do this query without if.