CODEWITHSUNDEEP
selectjoinsqlite
Sheena
Sheena

Reputation: 16212

sqlite3 select from multiple tables 'where' stuff

I'm trying to query multiple tables at once. Say I have a table named PRESCHOOLERS and I have another one called FAVORITE_GOOEY_TREATS, with a foreign key column in the PRESCHOOLERS table referencing the id field of FAVORITE GOOEY TREAT. What would I do if I wanted to get a list of preschoolers with their first names alongside their favorite treats. I mean something like:

first_name    |    treat
john          |  fudge
sally         |  ice-cream

Here's what I'm trying, but I've got a syntax error on the where part. 

SELECT PRESCHOOLERS.first_name, FAVORITE_GOOEY_TREATS.name as treat
FROM PRESCHOOLERS, FAVORITE_GOOEY_TREATS
WHERE PRESCHOOLERS.favorite_treat = FAVORITE_GOOEY_TREATS.id and PRESCHOOLERS.age>15;

As far as I know this kind of thing is alright by sql standards, but sqlite3 doesn't much like it. Can someone point me at some examples of similar queries that work?

Upvotes: 13

Views: 40057

Answers (1)

ain
ain

Reputation: 22749

Try

SELECT PRESCHOOLERS.first_name, FAVORITE_GOOEY_TREATS.name as treat
FROM PRESCHOOLERS
JOIN FAVORITE_GOOEY_TREATS ON PRESCHOOLERS.favorite_treat = FAVORITE_GOOEY_TREATS.id
WHERE PRESCHOOLERS.age > 15;

Upvotes: 16

Related Questions