Dan Dan - 1 month ago 8
SQL Question

SQL displaying details from 4 different tables

My aim is to have a SQL query that shows details of session - id, suburb of the branch the session is scheduled for, name of the movie, screen capacity and the session date. only sessions scheduled on 3rd or 13th of May

SELECT DISTINCT BRANCH.BRANCHID, BRANCH.BRANCHSUBURB, MOVIENAME, SCREENCAPACITY, SESSIONDATE
FROM SESSIONS, SCREEN, BRANCH, MOVIE
WHERE SESSIONDATE = '3-MAY-2016'
OR SESSIONDATE = '13-MAY-2016';


this does not do exactly what I want as there is ALOT more data displayed that I have.

The table setup is in the picture below.
click here for picture

Answer

You should use inner join to get desired result instead of Distinct keyword here.

Try this

SELECT b.BRANCHID, b.BRANCHSUBURB, m.MOVIENAME, sc.SCREENCAPACITY, se.SESSIONDATE
FROM  SESSIONS se INNER JOIN SCREEN sc on se.SCREENID = sc.SCREENID
INNER JOIN BRANCH b ON b.BRANCHID = se.BRNACHID
INNER JOIN MOVIE m ON m.MOVIEID = se.MOVIEID
WHERE se.SESSIONDATE BETWEEN '3-MAY-2016' AND '13-MAY-2016';
Comments