0

I am tying to bring several tables into a single script. To do this, I realize I will need to use INNER JOIN and this is confusing me.

"SELECT UserSpecies.commonName, UserSpecies.commonNameFR, UserSpecies.commonNameES, UserSpecies.commonNameDE, UserSpecies.speciesName, UserSpecies.speciesRegion, UserSpecies.speciesDetails, UserSpecies.maxSize, UserSpecies.creditSource, UserSpecies.UserCreated, UserSpecies.genusUC, UserSpecies.familyUC, UserSpecies.orderUC, UserSpecies.groupUC, UserSpecies.subGroupUC, UserSpecies.authority, Genus.name, Family.name, Orders.name, Groups.name, SubGroups.name, Types.name, IUCN.name 
FROM UserSpecies 
INNER JOIN Genus ON UserSpecies.genusKey = Genus.id 
INNER JOIN Family ON UserSpecies.familyKey = Family.id 
INNER JOIN Orders ON UserSpecies.orderKey = Orders.id 
INNER JOIN Groups ON UserSpecies.groupKey = Groups.id 
INNER JOIN SubGroups ON UserSpecies.subGroupKey = SubGroups.id 
INNER JOIN Types ON UserSpecies.typeKey = Types.id 
INNER JOIN IUCN ON UserSpecies.iucnKey = IUCN.id 
WHERE UserSpecies.id = %d"

When I run the above, I do not get any errors, but it simply does not retrieve the data.

Note: All tables and columns are correct.

What am I missing?

David Sanford
  • 735
  • 12
  • 26
  • Do you use this from python? Try to do it in sqlite console with certain id. – Michael Oct 16 '17 at 06:17
  • Please provide some sample data and your desired output. (See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) for how to add some.) – CL. Oct 16 '17 at 07:37

1 Answers1

1

It's likely you have missing information in you database.

The "inner join" statement retrieve data only if there is one (or more) match with the joined table.

In your case it means that if some data are missing in one of the joined tables (Genus, Family, etc..) you won't get any result. For example if there is no Family with id = UserSpecies.familyKey (in the Family table) you will get no data.

You can try using an "left join" instead of "inner join" : this will return a result even if there is no match. In this case the corresponding values will be null : if there is no Family matching you will get the value "null" for Family.name. With this modified request you will easily spot the missing data (if any) :

"SELECT UserSpecies.commonName, UserSpecies.commonNameFR, UserSpecies.commonNameES, UserSpecies.commonNameDE, UserSpecies.speciesName, UserSpecies.speciesRegion, UserSpecies.speciesDetails, UserSpecies.maxSize, UserSpecies.creditSource, UserSpecies.UserCreated, UserSpecies.genusUC, UserSpecies.familyUC, UserSpecies.orderUC, UserSpecies.groupUC, UserSpecies.subGroupUC, UserSpecies.authority, Genus.name, Family.name, Orders.name, Groups.name, SubGroups.name, Types.name, IUCN.name 
FROM UserSpecies 
LEFT JOIN Genus ON UserSpecies.genusKey = Genus.id 
LEFT JOIN Family ON UserSpecies.familyKey = Family.id 
LEFT JOIN Orders ON UserSpecies.orderKey = Orders.id 
LEFT JOIN Groups ON UserSpecies.groupKey = Groups.id 
LEFT JOIN SubGroups ON UserSpecies.subGroupKey = SubGroups.id 
LEFT JOIN Types ON UserSpecies.typeKey = Types.id 
LEFT JOIN IUCN ON UserSpecies.iucnKey = IUCN.id 
WHERE UserSpecies.id = %d"

If you want more details on the different flavors of the "join" keyword you can have a look here : What is the difference between "INNER JOIN" and "OUTER JOIN"?

Be carefull : sqlite only support a subset of the join operations.

Frédéric Branca
  • 166
  • 4