SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)

    OK, I know this is going to be blatently obvious...

    I am trying to execute the following query:
    Code:
    SELECT SUBSCRIPTIONS.CAT_ID, CAT_STATUS, CAT_NAME, CAT_SUBSCRIPTION, SUBSCRIPTIONS.FORUM_ID, F_STATUS, F_SUBJECT, F_SUBSCRIPTION, 
                 SUBSCRIPTIONS.TOPIC_ID, T_STATUS, T_SUBJECT, SUBSCRIPTIONS.MEMBER_ID, M_NAME
    		FROM FORUM.SUBSCRIPTIONS 
    		INNER JOIN FORUM.MEMBERS ON SUBSCRIPTIONS.MEMBER_ID = MEMBERS.MEMBER_ID
    		LEFT JOIN FORUM.CATEGORY ON SUBSCRIPTIONS.CAT_ID  = CATEGORY.CAT_ID 
    		LEFT JOIN FORUM.FORUM    ON SUBSCRIPTIONS.FORUM_ID  = FORUM.FORUM_ID
    		LEFT JOIN FORUM.TOPICS   ON SUBSCRIPTIONS.TOPIC_ID  = TOPICS.TOPIC_ID
    but am getting the following error message:
    Code:
    Syntax Error(missing operator) in query expression 'SUBSCRIPTIONS.MEMBER_ID = MEMBERS.MEMBER_ID
    		LEFT JOIN FORUM.CATEGORY ON SUBSCRIPTIONS.CAT_ID  = CATEGORY.CAT_ID 
    		LEFT JOIN FORUM.FORUM    ON SUBSCRIPTIONS.FORUM_ID  = FORUM.FORUM_ID
    		LEFT JOIN FORUM.TOPICS   ON SUBSCRIPTIONS.TOPIC_ID  = TOPICS.'.
    Now, I usually don't code like this. I personally hate using the INNER JOIN syntax and would prefer to break the rest out to other queries, but this is for a group project that wants the number of queries minimized to zero and I need to follow the convention they are following. Any idea on what I am missing or have screwed up? I'm sure it's very simple and is beating me in the head, but I just don't see it...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think MySQL requires LEFT OUTER JOIN.

    Try to vary the case, all upper-case is hard to read.

    Code:
    SELECT S.CAT_ID, 
           CAT_STATUS, 
           CAT_NAME, 
           CAT_SUBSCRIPTION, 
           s.FORUM_ID, 
           F_STATUS, 
           F_SUBJECT, 
           F_SUBSCRIPTION, 
           S.TOPIC_ID, 
           T_STATUS, 
           T_SUBJECT, 
           S.MEMBER_ID, 
           M_NAME
      FROM SUBSCRIPTIONS S
     INNER JOIN MEMBERS  M ON S.MEMBER_ID = M.MEMBER_ID
      LEFT JOIN CATEGORY C ON S.CAT_ID    = C.CAT_ID 
      LEFT JOIN FORUM    F ON S.FORUM_ID  = F.FORUM_ID
      LEFT JOIN TOPICS   T ON S.TOPIC_ID  = T.TOPIC_ID

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    Sorry, should have been more specific. This SELECT is for an Access database...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  4. #4
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dave, could you send me a copy of the database? Would be easier to work out.


  5. #5
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This?
    Code:
    SELECT FORUM_CATEGORY.CAT_ID, FORUM_CATEGORY.CAT_NAME, FORUM_FORUM.FORUM_ID, FORUM_FORUM.F_SUBJECT, FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_SUBSCRIPTIONS.SUBSCRIPTION_ID, FORUM_MEMBERS.M_NAME
    FROM (((FORUM_SUBSCRIPTIONS LEFT JOIN FORUM_CATEGORY ON
    FORUM_SUBSCRIPTIONS.CAT_ID = FORUM_CATEGORY.CAT_ID) LEFT JOIN FORUM_FORUM ON FORUM_SUBSCRIPTIONS.FORUM_ID = FORUM_FORUM.FORUM_ID) LEFT JOIN FORUM_TOPICS ON FORUM_SUBSCRIPTIONS.TOPIC_ID = FORUM_TOPICS.TOPIC_ID) INNER JOIN FORUM_MEMBERS ON FORUM_SUBSCRIPTIONS.MEMBER_ID = FORUM_MEMBERS.MEMBER_ID;

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    I JUST got it to work by rebuilding the query through the Access GUI and this is what it came up with. It's pretty similar to what you showed:

    Code:
    SELECT	S.SUBSCRIPTION_ID, S.MEMBER_ID, M.M_NAME,
    		S.CAT_ID, C.CAT_NAME, C.CAT_STATUS, C.CAT_SUBSCRIPTION, 
    		S.FORUM_ID, F.F_SUBJECT, F.F_STATUS, F.F_SUBSCRIPTION, 
    		S.TOPIC_ID, T.T_SUBJECT, T.T_STATUS
      FROM	(((FORUM_SUBSCRIPTIONS S INNER JOIN FORUM_MEMBERS M ON S.MEMBER_ID = M.MEMBER_ID) 
      LEFT	JOIN FORUM_TOPICS T ON S.TOPIC_ID = T.TOPIC_ID) 
      LEFT	JOIN FORUM_FORUM F ON S.FORUM_ID = F.FORUM_ID) 
      LEFT	JOIN FORUM_CATEGORY C ON S.CAT_ID = C.CAT_ID
    The parens make a difference for some reason. Thanks for the help....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •