Selecting from 2 tables with one reference table

[axl8910]

Hi, here are my tables:

 

FLEXI

 

ID          |name|  designation  |  status  |  PLC  | 

id_flexi|flexi1|(not important) |                |              |

id_flexi|flexi2|      .....                |                |              |

id_flexi|flexi3|    .....                |                |              |

 

USER

 

id_user|username|............|phone

 

FLEXI_ALLOCATION

 

id_flexi|id_user

 

In table flexi I have 7 fields(7 names), table USER changes when user registers on page, and FLEXI_ALLOCATION table changes when user reserves one item. In table FLEXI_ALLOCATION I have id from user and id from item he reserved, so in select I need to add username and phone from table USER to name, status PLC from FLEXI where their id matches in table FLEXI_ALLOCATION and fields username and phone should be "none" when there is no match. I need all that in one table so I can print it with mysql_fetch_array in PHP. My table should look like this:

 

Flexi  |  Status  |  PLC  |  User  |  Phone  |

Flexi1|  ON      |  ON  |  john  |  253452|      John has id 2

Flexi2|  OFF    |  OFF| none  | none      |

Flexi3| ON      |  ON  | Ann    |  214331|    Ann has id 4

 

If my FLEXI_ALLOCTION is:

id_flexi  |  id_user

  1          |      2

    3          |      4

 

 

 

 

[AyKay47]

use a JOIN

[Muddy_Funster]

use a JOIN

 

Yup - with the information you have given there is also no need for the additional link table, you could allocate the id_flexi to a field in the user table and save some effort.

[axl8910]

Yes but I'm required to use reference table... I tried to use join but I'm not sure how to connect 3 tables and to have empty space also.

[Muddy_Funster]

Why do you "need" to? That's just forcing bad practice.

 

What do you get from the following?

SELECT username, phone,  name as FlexiName, status, PLC 

FROM flexi_allocation 
RIGHT JOIN flexi ON (flexi_allocation.flexiID = flexi.ID)
RIGHT JOIN user ON flexi_allocation.user_id = user.user_id)

WHERE flexi_allocation.flexi_id IS NOT NULL

ORDER BY flexi_allocation.flexi_id ASC

 

Obviously change field and table names as needed.

[axl8910]

I 'need' to because that are orders from superiors=) I already had database and PHP and it worked perfectly but yeah...=) This works like charm thx=)) but there is just one little thing if you can also help me with. I need to print ALL FlexiNames no matter if they have match or no. This just prints lines and flexis where users are allocated and I want to print them all even though others have empty cells

[axl8910]

woo I solved. I just changed position of FLEXI and FLEXI_ALLOCATION and RIGHT JOIN to LEFT. THX for your help. Solved=)