Need help with a mysql join query

Question

I have a BOOK table that stores books, and a SIMI_BOOK table that stores the similarity values of any 2 books. Below are the keys used by the 2 tables.

BOOK: bid btitle
SIMI_BOOK: bid1 bid2 similarity

I need a join query that could fetch all the information about the OTHER book.

For example, when a user visits a particular book page (bookA), it can fetch all the books in SIMI_BOOK with either bid1=bookA or bid2=bookA, then join bid2 or bid1 with BOOK respectively to fetch the info about the OTHER book.

Any ideas on how to achieve that?
Thank you in advance.

Answer 1

What about:

SELECT b2.*
  FROM Book B2
  JOIN Simi_Book SB ON SB.Bid2 = B2.Bid AND SB.Bid1 = ?
UNION
SELECT b1.*
  FROM Book B1
  JOIN Simi_Book SB ON SB.Bid1 = B1.Bid AND SB.Bid2 = ?

Only one of the two queries will return a value - unless you have rows with both (Bid1 = X, Bid2 = Y) and (Bid1 = Y, Bid2 = X) in the Simi_Book table. The '?' placeholder is the value of the book ID that you do have; the same value should be supplied twice.

Answer 2

You can try something similar to:

SELECT * (or what you need) 
FROM SIMI_BOOK JOIN BOOK ON (BOOK.bid = SIMI_BOOK.bid1)
WHERE SIMI_BOOK.bid2 = nnn (your primary book id)
UNION
SELECT * (or what you need) 
FROM SIMI_BOOK JOIN BOOK ON (BOOK.bid = SIMI_BOOK.bid2)
WHERE SIMI_BOOK.bid1 = nnn (your primary book id)

Answer 3

You'll need LEFT JOIN and AS statements.

Do the following.

1. fetch all books in SIMI_BOOK
2. do a left join for bid1
3. do another left join for bid2

Hope this helps.