I have three tables as listed below
FORMAT -- Columns are format_id, format_desc
VIDEO_FORMAT -- columns areVIDEO_FORMAT_ID, VIDEO_ID, FORMAT_ID,COST
RENTAL -- Coulmns are RENTAL_ID, DATE_OUT, DATE_DUE, DATE_IN, DELIVERY_STATUS, LATE_FEE, CUSTOMER_ID, VIDEO_FORMAT_ID
I need to find List customer id, format description and cost of video format for those customers who rented a video.
I wrote the query
Select r.customer_id, f.format_desc, vf.cost from rental r, format f,video_format vf where f.format_id in (select format_id from video_format) and r.video_format_id = vf.video_format_id
this is not fetching exact results. Please help.
Would help to know what kind of results you're getting but I think I can see.
Select r.customer_id, f.format_desc, vf.cost
from rental r, format f,video_format vf
where vf.format_id = f.format_id
and r.video_format_id = vf.video_format_id
Can't be sure without a bit more info if that will fix you. But I'm not sure why you went with trying to get a relationship using an IN with a subquery. Hope it helps.