CODEWITHSUNDEEP
mysqljoinsql-order-by
Enkay
Enkay

Reputation: 1915

order results based on join conditions order

There are 3 mysql tables.

BOOKS have ID, AUTHOR1, AUTHOR2, PUBLISHER1, PUBLISHER2
AUTHORS have ID, WEBSITE
PUBLISHERS have ID, WEBSITE

Not all publishers or authors have a website.

I have a query that returns matching sets of author and publisher websites for a given book ID. If they all have a website, this would mean 2 rows.

SELECT AUTHORS.WEBSITE as WEB1, PUBLISHERS.WEBSITE as WEB2
FROM BOOKS
LEFT OUTER JOIN AUTHORS ON (ID=AUTHOR1 OR ID=AUTHOR2)
LEFT OUTER JOIN PUBLISHERS ON (ID=PUBLISHER1 OR ID=PUBLISHER2)
WHERE BOOK.ID ='12345'
LIMIT 2

While this works, it ends up returning the row matching the second author and publisher before the first author and publisher. Possibly based on alphabetical order.

How can I make this query return the row containinting the websites of AUTHOR1 and PUBLISHER1 first?

Upvotes: 1

Views: 108

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

I'd treat them as separate joins and use a COALESCE.

SELECT COALESCE(A1.WEBSITE, A2.WEBSITE) as WEB1, 
       COALESCE(P1.WEBSITE, P2.WEBSITE) as WEB2
FROM BOOKS B
LEFT OUTER JOIN AUTHORS A1 ON A1.ID=B.AUTHOR1 
LEFT OUTER JOIN AUTHORS A2 ON A2.ID=B.AUTHOR2
LEFT OUTER JOIN PUBLISHERS P1 ON P1.ID=B.PUBLISHER1 
LEFT OUTER JOIN PUBLISHERS P2 ON P2.ID=B.PUBLISHER2
WHERE B.ID ='12345'

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

Since there is no order by clause in the query, you can be sure that results being ordered alphabetically is just plain coincidence.

Anyway

SELECT AUTHORS.WEBSITE as WEB1, PUBLISHERS.WEBSITE as WEB2
FROM BOOKS
LEFT OUTER JOIN AUTHORS ON (ID=AUTHOR1)
LEFT OUTER JOIN PUBLISHERS ON (ID=PUBLISHER1)
WHERE BOOK.ID ='12345'
UNION
SELECT AUTHORS.WEBSITE as WEB1, PUBLISHERS.WEBSITE as WEB2
FROM BOOKS
LEFT OUTER JOIN AUTHORS ON (ID=AUTHOR2)
LEFT OUTER JOIN PUBLISHERS ON (ID=PUBLISHER2)
WHERE BOOK.ID ='12345'
LIMIT 2

Upvotes: 0

Related Questions