Mysql statement (syntax error on FULL JOIN)

Question

What is wrong with my sql statement, it says that the problem is near the FULL JOIN, but I'm stumped:

SELECT `o`.`name` AS `offername`, `m`.`name` AS `merchantName` 
FROM `offer` AS `o` 
FULL JOIN `offerorder` AS `of` ON of.offerId = o.id 
INNER JOIN `merchant` AS `m` ON o.merchantId = m.id 
GROUP BY `of`.`merchantId`

Please be gentle, as I am not a sql fundi

fundi = "the base of a hollow organ or that part of the organ farthest from its opening" — VoodooChild
– VoodooChild, Commented Jan 25, 2011 at 20:59
MySQL doesn't support full joins, see stackoverflow.com/questions/2384298/… — BoltClock
– BoltClock, Commented Jan 25, 2011 at 20:59
I don't see the need for the OFFERORDER reference, unless you need merchants in there (which doesn't require the need for FULL JOIN, even if MySQL supported the syntax). Also, backticks are only necessary for escaping MySQL keywords - which none of the above are. — OMG Ponies
– OMG Ponies, Commented Jan 25, 2011 at 21:03
Those errors are good, because they educate people to properly name tables and columns. There's no value in hiding that from those who are learning. — OMG Ponies
– OMG Ponies, Commented Jan 25, 2011 at 21:29

RichardTheKiwi · Accepted Answer · 2011-01-25 21:53:17Z

3

MySQL doesn't offer full join, you can either use

a pair of LEFT+RIGHT and UNION; or
use a triplet of LEFT, RIGHT and INNER and UNION ALL

The query is also very wrong, because you have a GROUP BY but your SELECT columns are not aggregates.

After you convert this properly to LEFT + RIGHT + UNION, you still have the issue of getting an offername and merchantname from any random record per each distinct of.merchantid, and not even necessarily from the same record.

Because you have an INNER JOIN condition against o.merchant, the FULL JOIN is not necessary since "offerorder" records with no match in "offer" will fail the INNER JOIN. That turns it into a LEFT JOIN (optional). Because you are grouping on of.merchantid, any missing offerorder records will be grouped together under "NULL" as merchantid.

This is a query that will work, for each merchantid, it will show just one offer that the merchant made (the one with the first name when sorted in lexicographical order).

SELECT MIN(o.name) AS offername, m.name AS merchantName 
FROM offer AS o 
LEFT JOIN offerorder AS `of` ON `of`.offerId = o.id 
INNER JOIN merchant AS m ON o.merchantId = m.id 
GROUP BY `of`.merchantId, m.name

Note: The join o.merchantid = m.id is highly suspect. Did you mean of.merchantid = m.id? If that is the case, change the LEFT to RIGHT join.

edited Jan 25, 2011 at 21:53

answered Jan 25, 2011 at 21:08

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

5 Comments

OMG Ponies Over a year ago

Since when is an answer just a load of questions for the OP to clarify? This is a comment, not an answer.

RichardTheKiwi Over a year ago

@OMG I don't see any question marks - can you point them out? I am pointing out flaws in the SQL to help the asker progress in learning SQL.

OMG Ponies Over a year ago

Discussion but no example == OP questions "how do I do that?" If you won't answer, the community will treat you appropriately.

OMG Ponies Over a year ago

Why does it take threat of public shaming for you to give a real answer?

RichardTheKiwi Over a year ago

@omg Check all 3 revisions. Each is a valid answer. Feel free to check my answers on other questions - no "threats" required; I just improve on answers when I see more detail.

Collectives™ on Stack Overflow

Mysql statement (syntax error on FULL JOIN)

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related