SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot frosco's Avatar
    Join Date
    Jul 2003
    Location
    WA state
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Full Join assistance

    I have tried to use the search option on this forum several times and it returns a blank page each time, so I apologize I wasn't able to search for help on this topic.

    This is my query
    Code:
    SELECT * FROM `opc_options` FULL JOIN `opc_items` ON `opc_options`.`ItemID` = `opc_items`.`ID`;
    ...for a full join. I've never done it, no idea what I'm doing, but it's giving me this error:

    Code:
    #1064 - 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 'LIMIT 0, 30' at line 2
    What am I missing? I have googled for the proper syntax and I'm having no luck at all.

  2. #2
    SitePoint Zealot frosco's Avatar
    Join Date
    Jul 2003
    Location
    WA state
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I edited the above to JOIN rather than FULL JOIN, and it is working, but I need to create an SQL Dump - I don't get that option. Maybe JOIN isn't what I want?

    I need records from only Table2 where Table2.ItemID = Table1.ID

    ...then I need to dump them.

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Take a look at SELECT INTO OUTFILE in the MySQL manual page on SELECT
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by frosco View Post
    I edited the above to JOIN rather than FULL JOIN
    JOIN is the same as INNER JOIN, and so you should write it that way

    and it's clear that you did not want FULL OUTER JOIN anyway -- where did you get that idea from?

    the reason i ask is that there are some tutorials out there which don't do a good enough job of explaining the difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot frosco's Avatar
    Join Date
    Jul 2003
    Location
    WA state
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 - it comes from me being completely confused and tired. And Google.

    The SELECT...INTO OUTFILE doesn't allow me ~ permissions error.

    Really I just want to remove impertinent records. Why is this so difficult? lol
    Code:
    DELETE * FROM `opc_options`
    WHERE `opc_items`.`ID` != `opc_options`.`ItemID`;
    In my brain that's what I want to do - and it doesn't work.

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frosco View Post
    r937 - it comes from me being completely confused and tired. And Google.

    The SELECT...INTO OUTFILE doesn't allow me ~ permissions error.

    Really I just want to remove impertinent records. Why is this so difficult? lol
    Code:
    DELETE * FROM `opc_options`
    WHERE `opc_items`.`ID` != `opc_options`.`ItemID`;
    In my brain that's what I want to do - and it doesn't work.
    Try the delete without the "*"

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Try

    DELETE FROM opc_options WHERE ItemID NOT IN (Select ID FROM opc_items)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    SitePoint Zealot frosco's Avatar
    Join Date
    Jul 2003
    Location
    WA state
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks - that got me a step closer:

    #1054 - Unknown column 'opc_items.ID' in 'where clause'

    The column definitely exists

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Hmmmm.....that should have worked - what version of mySQL are you using?

    You could try putting the tic marks around the table and field names, but that shouldn't cause the problem I don't think...

    You could also try
    DELETE FROM `opc_options` WHERE NOT EXISTS (Select `ID` FROM `opc_items` WHERE `ID` = `opc_options`.`ItemID`)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    SitePoint Zealot frosco's Avatar
    Join Date
    Jul 2003
    Location
    WA state
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, that query worked!

    PHP ver 5.1.5

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by frosco View Post
    Really I just want to remove impertinent records.
    "impertinent" lol

    i know just how you feel

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •