Search code examples
mysqlsqlsequelpro

SQL Querying multiple tables throwing syntax error?

I was hoping someone could help me figure out what I am doing wrong with a SQL query I am trying to run from within Sequel Pro. The error message says

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id INNER JOIN directory_person ON directory_twitter.id = directory_person.id WH' at line 1

And the query that I have written is

SELECT directory_twitter.id, directory_twitter.name, directory_twitter.screen_name,
       directory_twitter.followers, directory_group.id, directory_group.title 
FROM directory_twitter, directory_group
INNER JOIN directory_person_groups 
ON directory_person_groups.person_id = directory_twitter.id, 
   directory_person_groups.group_id = directory_group.id
INNER JOIN directory_person 
ON directory_twitter.id = directory_person.id
WHERE directory_person.appears_in_directory = "1"

I am trying to get the query to return the name of the users (directory_twitter.name), their screen name (directory_twitter.screen_name), their total number of followers (directory_twitter.followers), and the name of the group they are in (directory_group.title). Unfortunately the way our tables are set up, the only way I can think to join the group to the user is by INNER JOIN-ing a third table (directory_person_groups) where the group ID and the user ID are both present.

Finally, the only users that I want to be returned by this query are those who are in our directory (WHERE directory_person.appears_in_directory = "1"). In the table directory_person, directory_person.id = directory_twitter.id.

I have been trying to figure out what my error is for hours and I've made no progress. Is everything correct except a syntax error that I am unfamiliar with? Any and help is greatly appreciated. Thank you!

EDIT: All of the columns of the tables that I'm querying are below.

directory_twitter: id, person_id (which is the same value as id), screen_name, name, followers, user_id (I'm not sure where else this selector is used in the database, it is a different value from id and person_id).

directory_group: id (different from directory_twitter.id), slug(a slug of the title), title, screen_name (the Twitter handle of the group, e.g. CNN for @cnn)

directory_person_groups: id (I'm not sure where else if anywhere this value appears in the database, it is different from both directory_twitter.id and directory_group.id), person_id, group_id

directory_person: id (the same as directory_twitter.id which is the same as directory_twitter.person_id), title (this value is different from directory_group.title), first_name, last_name, appears_in_directory

Solution

  • Try this query but you need to add some indexes or it will take a very long time to run. I would suggest you make the columns in WHERE clause as indexes before I run it if I were you.

    SELECT DT.name, DT.screen_name, DT.followers, DG.title
FROM directory_twitter AS DT, directory_group AS DG, directory_person_groups AS DPG
WHERE DPG.person_id=DT.person_id AND DPG.group_id=DG.id AND DT.person_id IN 
                                    ( SELECT DP.id
                                      FROM  directory_person AS DP
                                                              WHERE appears_in_directory='1'
                                    ) ;