frosco — 2010-10-26T03:17:32-04:00 — #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.
frosco — 2010-10-26T03:30:24-04:00 — #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.
scallioxtx — 2010-10-26T03:57:45-04:00 — #3
Take a look at SELECT INTO OUTFILE in the MySQL manual page on SELECT
r937 — 2010-10-26T04:45:40-04:00 — #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
frosco — 2010-10-26T11:46:45-04:00 — #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.
jurn — 2010-10-26T12:53:00-04:00 — #6
Try the delete without the "*"
davemaxwell — 2010-10-26T13:10:14-04:00 — #7
DELETE FROM opc_options WHERE ItemID NOT IN (Select ID FROM opc_items)
frosco — 2010-10-26T13:10:29-04:00 — #8
Thanks - that got me a step closer:
1054 - Unknown column 'opc_items.ID' in 'where clause'
The column definitely exists
davemaxwell — 2010-10-26T13:24:47-04:00 — #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
opc_options WHERE NOT EXISTS (Select
frosco — 2010-10-26T14:59:01-04:00 — #10
Thank you, that query worked!
PHP ver 5.1.5
r937 — 2010-10-26T16:18:36-04:00 — #11
i know just how you feel