Geek Answers Handbook

Mysql statement (syntax error with FULL JOIN)

What's wrong with my sql expression, it says the problem is next to the FULL JOIN, but I'm at a dead end:

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 careful since I am not sql fundi

+3
source share
1 answer

MySQL does not offer a complete connection, you can use

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

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

After correctly converting to LEFT + RIGHT + UNION, you still have the problem of getting the offer name and seller name from any random record on each individual of.merchantid and not even necessarily from the same record.

Since you have an INNER JOIN clause against o.merchant, a FULL JOIN is not required, since the "offerorder" entries without matching the "offer" will fail the INNER JOIN. This turns it into a LEFT JOIN (optional). Since you are grouping on of.merchantid , any missing offerorder entries will be grouped together under "NULL" as the seller.

This is a query that will work; for each merchant, he will show only one offer made by the seller (one with the name when sorting 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 connection o.merchantid = m.id very suspicious. Did you mean of.merchantid = m.id ? If so, change LEFT to RIGHT join.

+3
source

More articles:


All Articles