SQL Querying multiple tables throwing syntax error?

Question

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

score 1 · Accepted Answer · answered Jul 08 '14 at 07:01

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'
                                    ) ;

DoubleM · Answer 2 · 2014-07-08T06:30:45.907

0

Try this

SELECT DIRECTORY_TWITTER.ID, 
       DIRECTORY_TWITTER.NAME, 
       DIRECTORY_TWITTER.SCREEN_NAME, 
       DIRECTORY_TWITTER.FOLLOWERS, 
       DIRECTORY_GROUP.ID, 
       DIRECTORY_GROUP.TITLE 
FROM   DIRECTORY_TWITTER,
       DIRECTORY_PERSON_GROUPS,
       DIRECTORY_PERSON,
       DIRECTORY_GROUP 
       INNER JOIN DIRECTORY_PERSON_GROUPS 
               ON DIRECTORY_PERSON_GROUPS.PERSON_ID = DIRECTORY_TWITTER.ID 
                  AND 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 changed the comma to AND

edited Jul 08 '14 at 06:30

answered Jul 08 '14 at 06:05

DoubleM

454
1
3
11

It is now giving me a different error which says >Unknown column 'directory_twitter.id' in 'on clause' – J Gegs Jul 08 '14 at 06:08
do you have a column called ID in the directory_twitter table? – DoubleM Jul 08 '14 at 06:11
With @Gidil 's edit the error now says (with the name of my database replaced in brackets for privacy's sake) >Table '[mydatabase].DIRECTORY_TWITTER' doesn't exist. However, I'm looking at the table right now, it definitely exists. And it definitely has a column called id, yes. – J Gegs Jul 08 '14 at 06:12
From your Query I can say you have one database and you are querying two tables. We don't need to know what your database name is. However, if know what columns are in those two tables we can help you debug it. I'll re-write the query using allies but it won't change anything – DoubleM Jul 08 '14 at 06:22
try it now I added two tables to FROM clause - not sure if that is necessary though – DoubleM Jul 08 '14 at 06:31
I added all of the columns as an edit to my question. I'm sorry about the confusing naming scheme, I realize it's a bit hard to parse. Thank you very much for your help so far. With your latest edit there is yet another error, this one reads "Not unique table/alias: 'DIRECTORY_PERSON_GROUPS'" – J Gegs Jul 08 '14 at 06:34

SQL Querying multiple tables throwing syntax error?

2 Answers2