SQLite script SELECT from multiple tables

Question

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?

Do you use this from python? Try to do it in sqlite console with certain id. — Michael
– Michael, Commented Oct 16, 2017 at 6:17
Please provide some sample data and your desired output. (See How to format SQL tables in a Stack Overflow post? for how to add some.) — CL.
– CL., Commented Oct 16, 2017 at 7:37

Frédéric Branca · Accepted Answer · 2017-10-16 16:32:10Z

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.

Collectives™ on Stack Overflow

SQLite script SELECT from multiple tables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related