Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 343
Sheena Avatar asked Sep 19 '11 23:09

Sheena


1 Answers

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;
like image 180
ain Avatar answered Nov 15 '22 17:11

ain