Becca Bohem Becca Bohem - 7 months ago 19
SQL Question

SQL: Querying Against Composite Entity

Example

Hi StackOverFlowers!

I'm trying to figure out how to query against a composite key.

In my select result, I want the book name, author, category, and selling price. So far I have

select title,category,price from books1 where books1.category='MYS';


but i'm not sure how to go about getting the author name. Any and all help is much appreciated!!

Answer

I'm not sure why you have books1 when the model show the table named books. Books is a horrible name for a table -- typically in relational databases you use the singular -- eg book.

Here is how you do a join. I hate to break it to you but this is very basic sql -- if this is a test on databases you are not going to do well on your final tomorrow.

Select a.First, a.Last 
from books b
join books_authors ab on b.b_code = ab.book_code
join authors a on ab.authorId = a.id
where b.category = 'MYS'

also all of your field names have spaces in them -- I don't know what platform you are using so I've no idea how to escape the names. Using spaces in field name is non-standard and not acually SQL. I'd advise against it whenever possible.