0

So apparently mySQL doesn't support full outer join, but it's really what I need. I've seen a bunch of blog posts and articles about emulating it with unions, but that removes duplicates. Can anyone help me out here?

Here's the query containing the full outer join (only one of a number of joins being done here); how would I translate that into something mySQL understands?

SELECT DISTINCT n.title, nr.teaser, n.nid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image,tn_img.tid as image_tid FROM node n JOIN node_revisions nr ON n.nid = nr.nid LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0) LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid JOIN term_node tn2 ON n.nid = tn2.nid FULL OUTER JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid WHERE n.status = 1 AND n.type = 'article' AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 ORDER BY n.created DESC LIMIT 3
Improve this question
4
  • You might want to a look at stackoverflow.com/questions/4796872/full-outer-join-in-mysql or stackoverflow.com/questions/2384298/… Commented May 24, 2011 at 17:27
  • I had already read those (and all the linked articles/posts), but could not extrapolate what they were suggesting to my query. This is really stretching the limits of my sql knowledge. Commented May 24, 2011 at 17:52
  • Thats cool. You might want to consider adding references to the questions you looked at to stop the question from being closed as duplicate... and of course snarky comments. Commented May 24, 2011 at 18:38
  • @EmmyS: Conrad Frix...well answered......! Commented Oct 16, 2012 at 9:27

1 Answer 1

Reset to default
1

You basically do a Union of LEFT and RIGHT JOIN.

You actually have interesting wrinkle in that you also want to limit the rows to 3. To solve that you need to

  • Limit both The "left" and "right" selects by 3
  • Then use the result of the UNION in a inline view
  • then Limit the union by 3 again

UPDATE Sadly, unless I'm mistaken you can't do this directly in a UNION so you need to add another layer of inline views prior to the UNION

The LIMITS inside the UNION will offer some performance benefit and then the limit after will give you the correct results. 

SELECT title, teaser, nid, DATE, image, image_tid FROM (SELECT title, teaser, nid, DATE, image, image_tid, created FROM (SELECT DISTINCT n.title, nr.teaser, n.nid, Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE, f.filepath AS image, tn_img.tid AS image_tid , n.created FROM node n JOIN node_revisions nr ON n.nid = nr.nid LEFT JOIN content_field_related_images cfri ON ( n.nid = cfri.nid AND cfri.delta = 0 ) LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid JOIN term_node tn2 ON n.nid = tn2.nid LEFT OUTER JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid WHERE n.status = 1 AND n.TYPE = 'article' AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 ORDER BY n.created DESC LIMIT 3) tleft UNION SELECT title, teaser, nid, DATE, image, image_tid, created FROM (SELECT DISTINCT n.title, nr.teaser, n.nid, Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE, f.filepath AS image, tn_img.tid AS image_tid , n.created FROM node n JOIN node_revisions nr ON n.nid = nr.nid LEFT JOIN content_field_related_images cfri ON ( n.nid = cfri.nid AND cfri.delta = 0 ) LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid JOIN term_node tn2 ON n.nid = tn2.nid RIGHT OUTER JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid WHERE n.status = 1 AND n.TYPE = 'article' AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 ORDER BY n.created DESC LIMIT 3) tright) t ORDER BY created DESC LIMIT 3

UPDATE Using spencer7593 and ypercube suggestions here's an alternative approach using two UNION ALL statements and no inline views. 

SELECT DISTINCT n.created, n.title, nr.teaser, n.nid, Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE, f.filepath AS image, tn_img.tid AS image_tid FROM node n JOIN node_revisions nr ON n.nid = nr.nid LEFT JOIN content_field_related_images cfri ON ( n.nid = cfri.nid AND cfri.delta = 0 ) LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid JOIN term_node tn2 ON n.nid = tn2.nid LEFT OUTER JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid WHERE n.status = 1 AND n.TYPE = 'article' AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 UNION ALL SELECT DISTINCT n.created, n.title, nr.teaser, n.nid, Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE, f.filepath AS image, tn_img.tid AS image_tid FROM node n JOIN node_revisions nr ON n.nid = nr.nid LEFT JOIN content_field_related_images cfri ON ( n.nid = cfri.nid AND cfri.delta = 0 ) LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid JOIN term_node tn2 ON n.nid = tn2.nid RIGHT JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid WHERE n.status = 1 AND n.TYPE = 'article' AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 AND cfri.field_related_images_nid IS NULL ORDER BY 1 DESC LIMIT 3
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Wow, that's scary-looking! I'll give it a shot and see what it returns. Thanks.
OK, just tried it and got this: #1054 - Unknown column 'created' in 'order clause'. I tried changing the final order by clause to n.created, but it gave me the same error, so I'm not sure what it's referring to. Also, could you explain what cfri.delta = 0 refers to?
@Emmy -- in a UNION or UNION ALL query, the ORDER BY clause references columns by position rather than by name (e.g. to get the result set ordered by the first column in the result set, ORDER BY 1). Also consider using a UNION ALL rather than a UNION to avoid elimination of duplicate rows.
@Conrad - actually, it is possible to use a UNION ALL in a query that emulates a FULL OUTER JOIN (which you would want to do to preserve duplicates) using three queries: an inner join, a left join and a right join. e.g. SELECT . FROM l INNER JOIN r UNION ALL SELECT . FROM l LEFT JOIN r WHERE r.id IS NULL UNION ALL SELECT . FROM l RIGHT JOIN r WHERE l.id IS NULL
Excuse for breaking into without having read the entire query, but can't a FULL OUTER JOIN be done using a UNION ALL of 2 queries: (SELECT . FROM l LEFT JOIN r) UNION ALL (SELECT . FROM l RIGHT JOIN r WHERE l.id IS NULL) ?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.