[Resolved] SELECT from 2 differerent tables, only from 1 if a column = -1

suntra

Hello 😉

I have a Web page which must read data contained in a table called "operateurs" (operators in English). It has the following columns: id, Surnom (nickname), id_Usager (user id)

If an operator has a user id, id_Usager contains it, but if he/she doesn't have one, it is set to -1.

At the moment, when I extract data from the table, if id_Usager is different from -1, I read the operator's nickname from the usagers (users) table.

I would like to make 1 query instead of having something like 30 queries for 1 page. I tried the following, it works fine when operators have a user id, but the ones with no user id are not returned :

SELECT operateurs.*, usagers.id, usagers.Surnom FROM usagers, operateurs WHERE usagers.id=operateurs.id

Then, I tried the following, it worked fine with users with an id, but returned every single entry from the users table for those without an id...

SELECT operateurs.*, usagers.id, usagers.Surnom FROM usagers, operateurs WHERE usagers.id=operateurs.id OR usagers.id=-1

I think a subquery or something like that could help, but I wasn't able to find something on mysql.com that could help me.

Basically, I want the query to return operateurs.* at all times, and to return usagers.id and usagers.Surnom ONLY IF operateurs.id_Usager IS NOT -1.

Anyone's help would be very appreciated 🙂

Thanks !

mrhappiness

try

SELECT
  o.*,
  u.*,
  IF(u.usager_id = -1, CONCAT(u.id,' - ', u.surnom), u.usagers_id) username
FROM
  usagers u  

  INNER JOIN operateurs o ON u.id = o.id

in your phpmyadmin

if the output is not what you want please post it and tell which information you miss

suntra

I finally found it, thanks to you mrhappiness. I saw "inner join" and I read the documentation. Here's my SQL statement:

SELECT operateurs.id, operateurs.Niveau, operateurs.id_Usager, IF(operateurs.id_Usager<>-1, usagers.Surnom, operateurs.Surnom) AS Surnom FROM operateurs LEFT JOIN usagers ON usagers.id=operateurs.id_Usager ORDER BY Niveau DESC, Surnom ASC

Translation...
operateurs = operators
Niveau = level
Surnom = nickname

Thanks 🙂