MYSQL join condition match only one row based on priority

Question

Closed. This question needs debugging details. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65843012/mysql-join-condition-match-only-one-row-based-on-priority

Answer 1

SELECT COALESCE(p2.id, p1.id) id, COALESCE(p2.post_title, p1.post_title) post_title
FROM wp_icl_translations t1
JOIN wp_posts p1 ON t1.element_id = p1.id
LEFT JOIN wp_icl_translations t2 ON t1.trid = t2.trid AND t2.language_code = 'fa'
LEFT JOIN wp_posts p2 ON t2.element_id = p2.id
WHERE t1.language_code = 'en'
ORDER BY id

fiddle

---

I want to use * somehow to get all columns that's really important .. both tables complete create in the fiddle here : https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=37f8af9fd14c60f4ea23fd4778b86806 – steve moretz

If you need complete columns then you must add one more tables copies and extract the whole rows from them. Primary key needed for such join:

SELECT p0.*, t0.*
FROM wp_icl_translations t1
JOIN wp_posts p1 ON t1.element_id = p1.id
LEFT JOIN wp_icl_translations t2 ON t1.trid = t2.trid AND t2.language_code = 'fa'
LEFT JOIN wp_posts p2 ON t2.element_id = p2.id
JOIN wp_icl_translations t0 ON t0.translation_id = COALESCE(t2.translation_id, t1.translation_id)
JOIN wp_posts p0 ON p0.id = COALESCE(p2.id, p1.id)
WHERE t1.language_code = 'en'
ORDER BY id

fiddle