3

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

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
Stephan Grobler
  • 469
  • 1
  • 5
  • 17
  • 2
    fundi = "the base of a hollow organ or that part of the organ farthest from its opening" – VoodooChild Jan 25 '11 at 20:59
  • 3
    MySQL doesn't support full joins, see http://stackoverflow.com/questions/2384298/mysql-full-outer-join-syntax-error/2384312#2384312 – BoltClock Jan 25 '11 at 20:59
  • +1 @VoodooChild I was stuck at that word too. : P @BoltClock +1 for the link – Nishant Jan 25 '11 at 21:01
  • 1
    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 Jan 25 '11 at 21:03
  • @OMG backticks is a `style` preference. some people live by it – RichardTheKiwi Jan 25 '11 at 21:08
  • That "style" serves no purpose, no benefit whatsoever. It makes the query less readable for the effort with no reward. – OMG Ponies Jan 25 '11 at 21:18
  • @OMG See discussions http://stackoverflow.com/questions/261455 and http://stackoverflow.com/questions/1962859 among others. Consistent use of backticks help beginners like OP stay away from errors when using DBs with multi-word names. – RichardTheKiwi Jan 25 '11 at 21:26
  • 2
    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 Jan 25 '11 at 21:29
  • 1
    thank you all for all your comments and answers! – Stephan Grobler Jan 26 '11 at 09:59

1 Answers1

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Since when is an answer just a load of questions for the OP to clarify? This is a comment, not an answer. – OMG Ponies Jan 25 '11 at 21:19
  • @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. – RichardTheKiwi Jan 25 '11 at 21:23
  • Discussion but no example == OP questions "how do I do that?" If you won't answer, the community will treat you appropriately. – OMG Ponies Jan 25 '11 at 21:37
  • Why does it take threat of public shaming for you to give a real answer? – OMG Ponies Jan 25 '11 at 21:58
  • 1
    @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. – RichardTheKiwi Jan 25 '11 at 22:02