MySQL Join Query


I have 4 tables in my MySQL database:

  • category with fields cat_id,category

  • nonadminusers with fields id, username,password,category,fid

  • ambulance with fields ambulance_id, ambulance_name,etc….

  • hospital with fields hospital_id, hospital_name….etc

The nonadminusers table has data like id=1,username=tes,password=pass,category=1,fid=2

The category has data like cat_id=1,category=Hospital, cat_id=2,category=Ambulance

I need to display data from nonadminusers joining data from category table linked with category foreign key and and fid foreign key either from hospital or ambulance depending on category id in table.

I am a newbie to MySQL and how is it possible using joins or otherwise in MySQL query?

Best Answer

Try the following SQL query:

Select * 
from nonadminusers as a 
inner join category as c 
    on a.category=c.cat_id
where category in ('Ambulance','Hospital');