Join queries to select tables
I just started learning about Join in SQL. I have gone through various online and offline tutorial to understand it. Then I started doing some questions to find out how much I understand. Below are the tables
Developer Table 1) ID NUMBER 2) NAME VARCHAR2 Customer table 1)ID NUMBER 2)NAME VARCHAR2 Project Table 1)ID NUMBER 2)CUSTOMER_ID NUMBER (NULL for internal projects) 3)NAME VARCHAR2 4)TYPE NUMBER (0 - Scrum, 1 - Fixed price, 2 - Proof of Concept) 5)START_DATE DATE (NULL when not started yet) 6)END_DATE DATE (NULL when not finished yet) Task Table 1) ID NUMBER 2)NAME VARCHAR2 3)PROJECT_ID NUMBER 4)TYPE NUMBER (0 - Deployment, 1 - Support, 2 - Ticket, 3 – Task) 5)START_DATE DATE (NULL when not started yet) 6)END_DATE DATE (NULL when not finished yet) Developer_Task Table 1) DEV_ID NUMBER 2)TASK_ID NUMBER Now i want to write query for these question: 1)Which developer spent time on a project that was actually already finished and for which customer was it? 2)How many support tasks did each developer work on last year for Proof of Concept projects? 3)Which developer did not participate on a Scrum project yet?
Joins need some common attribute. Based on that we need to write queries. I am not able to understand how these tables are related and how to write queries for these?
SELECT * FROM Developer D WHERE D.ID IN( SELECT DEV_ID FROM Developer_TASK DT JOIN Tasks T ON DT.TASK_ID = T.ID JOIN Project P ON P.ID = T.PROJECT_ID WHERE P.END_DATE > T.END_DATE )
"Check with current datetime" Already ended projects
SELECT * FROM Developer D WHERE D.ID IN( SELECT DEV_ID FROM Developer_TASK DT JOIN Tasks T ON DT.TASK_ID = T.ID JOIN Project P ON P.ID = T.PROJECT_ID WHERE P.END_DATE < GETDATE() )
SELECT DEV_ID, Name, COUNT(*) AS Entries FROM Developer D JOIN Developer_Task DT ON D.ID = DT.DEV_ID JOIN Tasks T ON TD.Task_ID = T.ID GROUP BY DEV_ID, YEAR(T.START_DATE), Name
SELECT * FROM Developer WHERE DEV_ID NOT IN(SELECT DEV_ID FROM Developer_TASK DT JOIN Tasks T ON DT.TASK_ID = T.ID JOIN Project P ON P.ID = T.PROJECT_ID)
SQL JOIN, JOIN Syntax, JOIN Differences, 3 tables, table2: Second table matching_column: Column common to both the tables. Example Queries(FULL JOIN): SELECT Student.NAME,� Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Which developer spent time on a project that was actually already finished and for which customer was it?
select p.id project_id, p.name project_name, d.name dev_name, case p.customer_id when 0 then 'Internal' else c.name end cust_name from project p left join customer c on c.id = p.customer_id join task t on t.project_id = p.id join developer_task dt on dt.task_id = t.id join developer d on d.id = dt.dev_id where p.end_date is not null
These are basic joins.
p.end_date is not null is responsible for filtering finished projects. I do not know if
customer table contains row for internal project so I used
case construction and
left join to not lose rows for such projects.
How many support tasks did each developer work on last year for Proof of Concept projects?
select id, name, nvl(cnt, 0) cnt from developer d left join ( select dev_id, count(1) cnt from project p join task t on t.project_id = p.id join developer_task dt on dt.task_id = t.id where p.type = 2 and t.start_date <= trunc(sysdate, 'year') - 1 and (add_months(trunc(sysdate, 'year'), -12) <= t.end_date or t.end_date is null) group by dev_id) t on t.dev_id = d.id
Here I grouped at first number of tasks for proof of concept projects for each developer. Date filtering is somewhat tricky, please study it carefully. Last step was to show data for all developers so I used
left join to show all, even if they did not participate in such projects. They have count = 0.
Which developer did not participate on a Scrum project yet?
select * from developer where id not in ( select dev_id from project p join task t on t.project_id = p.id join developer_task dt on dt.task_id = t.id where p.type = 0)
Find dev_id for all Scrum projects. Then use
not in. You can also use
count. Oracle often allows to do task in several ways.
Here is the dbfiddle demo with all working queries and sample data I imagined to have material to check if syntax is correct and if results looks OK.
Learn SQL: Join multiple tables, Joins allow you to link data from two or more tables together into a single query result--from one single SELECT statement. A "Join" can be recognized in a SQL� SQL JOIN tables query type 2 – LEFT JOIN In LEFT JOIN, all the records from the table on the left of the LEFT JOIN clause are retrieved. By contrast, from the table on the right of the LEFT JOIN clause, only those rows are selected where the values for the columns involved in the LEFT JOIN query are the same.
If you want to know those questions you first will need to add a Developer foreign key in Project table. Then, you will be able to know Developer actions in Project table. Greetings
SQL, In this case, you can add a WHERE clause to the query to select, from the result of the join, the rows with NULL values in all of the columns from� LEFT JOIN – Tables order matters While the order of JOINs in INNER JOIN isn’t important, the same doesn’t stand for the LEFT JOIN. When we use LEFT JOIN in order to join multiple tables, it’s important to remember that this join will include all rows from the table on the LEFT side of the JOIN. Let’s rearrange the previous query:
Table Joins, A SQL JOIN combines records from two tables. A JOIN locates related column values in the two tables. A query can contain zero, one, or multiple JOIN operations. INNER JOIN is the same as JOIN; the keyword INNER is optional.
How to LEFT JOIN Multiple Tables in SQL, FULL JOIN − returns rows when there is a match in one of the tables. SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. CARTESIAN JOIN − returns the Cartesian product of the sets of records from the two or more joined tables.
I am using a query with aggregate function (sum) to generate the sum of each product: the result looks like this: product_name | sum(qty) product_1 | 100 product_2 | 200 product_5 | 300 now i want to join the above result to another table called products. so that i will have a summary like this: product_name | sum(qty) product_1 | 100 product_2 | 200 product_3 | 0 product_4 | 0 product_5 | 300
- Does your table information not list the foreign keys/relationships between the tables? However, looking at your tables and there columns, it's not too difficult to guess what they might be - e.g. you have an ID column in the CUSTOMER table, and a CUSTOMER_ID column in the PROJECTS table; those seem like they have a relationship between them.
- How to fetch negative value means did not participate value from third query?
- you could maybe use MINUS or an outer join.
- for "Which developer did not participate on a Scrum project yet?"
- yes. You outer join the scrum tasks to the developers table and find the ones that have a null task id, or you list the developer ids and minus the scrum task developer ids. Depends on what information you want to display in your final output - do you just need developer ids? If so, probably MINUS is best, otherwise the outer join. Or maybe even
where not exists ...
- Thanks for reply but WHERE DEV_ID NOT IN(SELECT DEV_ID FROM Developer_TASK DT) how ensure us about scrum ??
- Correct, I deleted it
- I think in second query Proof of concept also need to consider?? what you say Lasse...Never mind i will add that .....Thank you for you help...
- Is it correct way to check project already finished in the first query or any other way we can do it???
- Is the edit I just did what you want? "List all that havent ended now"
- should i use inner join or any other join??
- for "Which developer did not participate on a Scrum project yet?" question i need to inner join developer then Developer_Task then then then Project to get scrum details . Am i right??? so many inner joins