CODEWITHSUNDEEP
sqlsql-server
ReadingIsHard
ReadingIsHard

Reputation: 9

SQL Server Matching INNER JOIN RESULTS

I have a homework question that says:
"List the book codes for each pair of books that have the same price. (For example, one such pair would be book 0200 and book 7559, because the price of both books is $8.00.) In addition to both book codes, include the price. The first book code listed should be the major sort key, and the second book code should be the minor sort key."
My code looks like: 

   USE Henry;
SELECT DISTINCT BOOK1.BOOK_CODE, BOOK1.PRICE
FROM BOOK
    AS BOOK1
    JOIN BOOK
    AS BOOK2
    ON (BOOK1.BOOK_CODE = BOOK2.BOOK_CODE)
    AND (BOOK1.PRICE = BOOK2.PRICE)
WHERE BOOK1.PRICE = BOOK2.PRICE
ORDER BY BOOK1.PRICE, BOOK1.BOOK_CODE;

I can't figure out how to match the prices together and make them list next to each other. Can someone help me with this?

Upvotes: 1

Views: 119

Answers (2)

Zorkolot
Zorkolot

Reputation: 2017

The directions state:

"In addition to both book codes, include the price."

So you have to change your select to display those fields.

In your code you join on BOOK1_Code = BOOK2_Code but this can't be correct since you are looking for different books with the same price. You need to join on price, as the comments indicate.

The problem with a simple INNER JOIN with insufficient joining conditions, is that it will match BOOK1 price to BOOK2 price, and still do the same vice versa- matching BOOK2 price to BOOK1 price. It will also match the same book to itself, by price. Hence the same pair of books will be listed twice and the book itself listed against itself once.

To avoid this, you can enforce a condition on the INNER JOIN where the BOOK1.Code is different than the BOOK2.Code. I would do this by using BOOK1.Code < BOOK2.CODE, since this avoids matching the same book code to itself and ensures only lesser book codes are compared to greater book codes.

USE Henry;
SELECT BOOK1.BOOK_CODE, BOOK2.BOOK_CODE, BOOK1.PRICE
FROM BOOK AS BOOK1
    INNER JOIN BOOK AS BOOK2
      ON  BOOK1.PRICE = BOOK2.PRICE    
      AND BOOK1.BOOK_CODE < BOOK2.BOOK_CODE --2nd ON condition 
                                            --eliminates repeats
ORDER BY BOOK1.BOOK_CODE, BOOK2.BOOK_CODE

Edit: you also don't need the distinct.

Upvotes: 1

Obie
Obie

Reputation: 477

The question isn't really clear on how to handle situations where prices match among more than two books. I'm guessing you don't want to show redundant information:

SELECT  BOOK1.BOOK_CODE BOOK_CODE_1, BOOK2.BOOK_CODE BOOK_CODE_2, BOOK1.PRICE
FROM BOOK
AS BOOK1
JOIN BOOK
AS BOOK2
ON (BOOK1.BOOK_CODE < BOOK2.BOOK_CODE)
AND (BOOK1.PRICE = BOOK2.PRICE)
WHERE NOT EXISTS(  --This will remove some redundant info
  select * 
  from BOOK BOOK3 
  where BOOK3.BOOK_CODE<BOOK1.BOOK_CODE and BOOK3.PRICE=BOOK1.PRICE
  )
ORDER BY BOOK1.BOOK_CODE, BOOK2.BOOK_CODE;

Upvotes: 1

Related Questions