Accessing the foreign-key also with group by in SQL

I have the following database schema:

Book(ISBN, Name, Genre)
Details(ISBN, Price, Total_pages)

Where ISBN is the primary-key for Book table, and foreign-key for the Details table.

Now I need to find the most expensive book in each genre. I have tried like this:

SELECT
        Genre
        ,MAX (Price) AS MAX_Price
    FROM
        Book
        ,Details
    GROUP BY
        Genre
;

But it gives me wrong ans. How can I fix this?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try the following query.It may helps you.

SELECT
    Book.Genre
    ,MAX(Details.Price) AS MAX_Price
FROM
    Book
    INNER JOIN Details
    ON Book.ISBN = Details.ISBN
GROUP BY
    Book.Genre
;

Method 2

you should use join syntax to tell database which relation you want when you select multi table

select Genre,Max(Price) as MAX_Price
    from Book
    -- point relation condition
    left join Details on (Book.ISBN=Details.ISBN)
    group by Genre


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x