SQLTeam.com | Weblogs | Forums

How would I write this join query?


#1

I need to display the first and last names of people who have sold items at a price of over $20, I know I need to use a join, but im not sure which type. I thought I would need to join the author and title tables as the title table has a price column. Can anyone shed some light on this? I'm a newbie so try not to get too technical. I also only need to display the first and last names of the authors. Here are the column names for my tables:

Authors:
au_id
au_lname
au_fname
Discounts:
discounttype
stor_id
Employee:
emp_id
job_id
pub_id
fname
Pub_info:
pub_id
logo
Publishers:
pub_id
pub_name
Sales
stor_id
ord_numb
title_id
Titleauthor:
au_id
title_id
au_ord
Titles:
title_id
pub_id
price


#2

To confirm a sales has been made, you'd first go thru sales table. From there you can go to titles table thru title_id, and now you can filter on price>=20. To get to the authors table (to get first and last name), you need to go to titleauthor thru title_id, and now you can go to authors thru au_id. I don't see how you can find out, who sold the item (employee table), as emp_id is not mentioned in any table but employee table itself.

How about showing us the table descriptions as create statements, sample data as insert statements, and your expedted output (based on your sample data). This way we are able to better help you.


#3

If it helps here's what I have so far in my query:

select au_lname, au_fname
from authors JOIN titleauthor
on authors.au_id=titleauthor.au_id

Using this I have all of the names of authors listed, just don't know how to filter by price as price is listed under the "title" table


#4

You can join to title table thru title_id from titleauthor. Then you can filter on price field. But you still need to join to sales table, otherwise you'd get titles on stock (not sold) where price >= 20. Also you need to figure out at way to join to employee table, if you want to know, who sold the title.