0

I have a lengthy MySQL query I'm attempting to use a FULL OUTER JOIN on, which does not exist in MySQL:

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
ORDER BY RAND() DESC;

The LEFT JOIN above is where I'm looking to instead use FULL OUTER JOIN logic. I understand it has to use multiple JOINs with UNION, but my attempts have resulted in various syntax errors.

What's the correct syntax to change the above to a FULL OUTER JOIN logic?

I'm trying this:

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT OUTER JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
UNION ALL
select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
FROM j25_mt_cats as c,
j25_mt_links as l
RIGHT OUTER JOIN j25_mt_cfvalues AS cf ON cf.link_id = l.link_id,
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
 AND cf.link_id IS NULL
ORDER BY RAND() DESC;

But it takes minutes to run, then returns #126 - Incorrect key file for table '/tmp/#sql_4f82_0.MYI'; try to repair it when I think I'm closer to the correct syntax. The disk isn't full or even close. Nor is this a table, it's just a query I'm running in phpMyAdmin.

Improve this question
6
  • You should see this stackoverflow.com/questions/4796872/full-outer-join-in-mysql Commented Sep 23, 2013 at 3:28
  • @ThịtBòTái Yes, that's the syntax I tried and I'm getting syntax errors and the sort. Edited my attempt into question. Commented Sep 23, 2013 at 3:30
  • hi @Zeno, ur table is crashed, please repair it, this is the references link that u can refer, dev.mysql.com/doc/refman/5.0/en/repair-table.html Commented Sep 23, 2013 at 6:19
  • Do not mix implicit joins (using , and where conditions) with explicit joins (using JOIN keywords) Commented Sep 23, 2013 at 7:00
  • @a_horse_with_no_name Does that mean I need to write this from scratch? Commented Sep 24, 2013 at 3:01

2 Answers 2

Reset to default
1

This problem occurs, tipically, when you fill the temporary directory, and in your case, probably, your query creates a huge temporary file.

Apart optimize your query, try monitoring your partition where the temporary directory is located show variables like 'tmpdir'; during your query.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yep, 3GB file being created in /tmp confirmed.
1

if possible please provide table design , sample column, the problem of unknown column i guess is because of your bottom join missing some cast

select c.cat_id,c.cat_name as cat_name, 
c.cat_desc, c.cat_image, mi.filename, 
l.link_id, l.user_id, l.address,l.city, 
l.country,l.link_created,l.link_desc, 
l.email,l.fax,l.link_hits, l.link_modified,
l.link_name,l.postcode, l.price,l.link_rating, 
l.state,l.telephone,l.link_votes,
l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT OUTER JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id)

//you mi is missng here [Start]

j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
AND mi.link_id = l.link_id AND mi.ordering < 2  
AND c.cat_id = cl.cat_id and c.cat_published = 1 
AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
AND cf.link_id IS NULL

// END

UNION ALL
select c.cat_id,c.cat_name as cat_name, 
c.cat_desc, c.cat_image, mi.filename, 
l.link_id, l.user_id, l.address,l.city, 
l.country,l.link_created,l.link_desc, 
l.email,l.fax,l.link_hits, l.link_modified,
l.link_name,l.postcode, l.price,l.link_rating, 
l.state,l.telephone,l.link_votes,
l.website, l.link_id, l.link_visited, cf.value

//u forgot to define c,l,cf at here

FROM j25_mt_cats as c, j25_mt_links as l 
RIGHT OUTER JOIN j25_mt_cfvalues as cf ON cf.link_id = l.link_id,
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
AND mi.link_id = l.link_id AND mi.ordering < 2  
AND c.cat_id = cl.cat_id and c.cat_published = 1 
AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
AND cf.link_id IS NULL
ORDER BY RAND() DESC;
Improve this answer

3 Comments

Thanks, strangely enough it's the mi one I'm getting an error on. Updated my question.
top query missing mi~
Weird, that makes it try to query for minutes then errors with #126 - Incorrect key file for table '/tmp/#sql_4f82_0.MYI'; try to repair it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.