Full Join assistance

Databases
1

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


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:

#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. :frowning:

2

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

Take a look at SELECT INTO OUTFILE in the MySQL manual page on SELECT :slight_smile:

4

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

5

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


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

Try the delete without the “*”

7

Try

DELETE FROM opc_options WHERE ItemID NOT IN (Select ID FROM opc_items)

8

Thanks - that got me a step closer:

#1054 - Unknown column ‘opc_items.ID’ in ‘where clause’

The column definitely exists

9

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)

10

Thank you, that query worked!

PHP ver 5.1.5

11

“impertinent” lol

i know just how you feel

:cool: