Query to display names from multiple tables linked by ID's to a single table

[dipperdan]

Hi,

I am working on a plant database and I need some help with a query that i *thought* shouldn't have been too hard =\

I am trying to create a query that will display the names contained in 4 different tables but I am having trouble linking them using the main table containing ids

Here is the structure information for the 4 tables:
Families

family_id      int(10)               
family_name     varchar(50) 

Genera

genus_id      int(10)     
genus_name     varchar(50)

Species

species_id      int(10)       
species_name     varchar(50) 

Infraspecies

infraspecies_id      int(10)        
infraspecies_name     varchar(50)       
rank     varchar(5)

Then the main table:
Plants

plant_id      int(10)       
family_num     int(10)       families -> family_id       
genus_num     int(10)      genera -> genus_id       
species_num     int(10)     species -> species_id                 
infraspecies_num     int(10)     infraspecies -> infraspecies_id
syn_genus_num     int(10)     syn_genera -> syn_genus_id       
syn_species_num     int(10)     syn_species -> syn_species_id       
syn_infraspecies_num     int(10)     syn_infraspecies -> syn_infraspecies_id      
scientific_name     varchar(100)             
additional_info     text 

And I would like the query to print out the family name - genus name - species name - rank - infraspecies name (in that order). How exactly would i go about this, i was trying to do it using joins but that did not work.

Thanks in advance...

[ronverdonk]

Welcome to TSDN!

Have you tried a query like the following (not quite correct column names, but the join is what you want)

SELECT p.plant_id, p.family_num, p.genus_num,p.species_num, p.infraspecies_num, g.genus_name, s.species_name, i.infraspecies_name, f.family_name 
FROM  plants as p,generas g,families as f,species as s,infraspecies as i
WHERE p.family_num=f.family_id AND
      p.genus_num=g.genus_id AND
      p.species_num=s.species_id AND
      p.infraspecies_num=i.infraspecies_id; 

Ronald

[dipperdan]

Hey thanks for the tip, i tried the query and it worked but i edited it a bit to show just the fields i wanted. Problem is though that i would like null fields in infraspecies rank and infraspecies name to show up in the results so that i get the full amount of rows from plants table with all relevant information returned. How would i get that done, i'm assuming i would have to use joins somehow but i'm not exactly sure how to go about it.


Thanks

[dipperdan]

yaaaay, i think i got it lol, here's the query i used:

SELECT f.family_name, g.genus_name, s.species_name, i.rank, i.infraspecies_name
FROM plants AS p
LEFT JOIN (
genera AS g, families AS f, species AS s
) ON ( p.family_num = f.family_id
AND p.genus_num = g.genus_id
AND p.species_num = s.species_id )
LEFT JOIN infraspecies AS i ON p.infraspecies_num = i.infraspecies_id
 

Output seems to be accurate so far plus it returned the correct number of rows from the plants table (2,393)

[ronverdonk]

I was just working on it, but you beat me to it.

Ronald