Multiple Inner Joins - MySQL
Romulus
I have 2 tables, Task and Transaction, which look like this:
Task
Transaction
I would like to create a query which return information from both tables.
Columns: name, priority, waiting, error, done, total, status
where:
- name = Task.name
- priority = Task.priority
- waiting = count(transaction.id) WHERE task.id = transaction.task and transaction.status = 1
- error = count(transaction.id) WHERE task.id = transaction.task and transaction.status = 2
- done = count(transaction.id) WHERE task.id = transaction.task and transaction.status = 3
- total = count(transaction.id) WHERE task.id = transaction.task
- status = task.status
I tried with INNER JOIN but I get a wrong result:
SELECT tk.name, tk.priority, waiting.waiting, error.error, done.done, total.total
FROM task AS tk, transaction AS tran
INNER JOIN (
SELECT count(id) AS waiting
FROM transaction
WHERE status = 1
) AS waiting
INNER JOIN (
SELECT count(id) AS error
FROM transaction
WHERE status = 3
) AS error
INNER JOIN (
SELECT count(id) AS done
FROM transaction
WHERE status = 4
) AS done
INNER JOIN (
SELECT count(id) AS total
FROM transaction
) AS total;
Could you please help me creating this query? I'm getting the columns waiting, error, done, total with a count of all the transactions. Instead it should get the number of transaction WHERE task.id = transaction.task and transaction.status = 1,2,3.
Taryn
A few issues with your existing query. First, you aren't joining your two tables together on any column. It looks like you can join on task.id and transaction.task. Second. you should be able to get each of the total by using an aggregate function with some conditional logic, like a CASE expression:
SELECT tk.name, tk.priority,
sum(case when tran.status = 1 then 1 else 0 end) waiting,
sum(case when tran.status = 3 then 1 else 0 end) error,
sum(case when tran.status = 4 then 1 else 0 end) done,
count(*) total
FROM task AS tk
INNER JOIN transaction AS tran
ON tk.id = tran.task
GROUP BY tk.name, tk.priority;
This type of query is known as a PIVOT, it's where you take the values from your rows and converts them into rows. Inside the CASE expression, you'll only get the total for each status.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Subset data by matching values, then running cumsum() as frequency
- Next: How to make UISlider have discrete intervals? For example intervals of 5000
Related
Related Related
- 1
mySQL multiple inner joins
- 2
mySQL multiple inner joins
- 3
Multiple inner joins in MySql not working
- 4
MySQL optimize multiple inner joins
- 5
MySQL query with multiple inner joins on same table
- 6
SQL Multiple inner joins
- 7
mysql inner joins query
- 8
MYSQL Inner Joins with OR condition
- 9
MySQL - Display multiple rows in one field (tables with inner joins)q
- 10
Multiple inner joins returning Error
- 11
Multiple Inner Joins with Polymorphic Association
- 12
Inner joins selecting multiple columns
- 13
How to use multiple inner joins
- 14
MYSQL query with 3 INNER JOINs
- 15
Using inner joins and aliases in mysql
- 16
mixture of inner and outer joins in mySQL
- 17
mysql - multiple joins
- 18
Multiple joins in MySQL table
- 19
Optimize multiple JOINs in MySQL
- 20
Multiple JOINS - Mysql
- 21
MySQL joins multiple tables
- 22
Multiple inner joins to get a single record by sorting
- 23
Get data from multiple inner joins
- 24
Create multiple INNER JOINS with Sequelize ORM
- 25
Create multiple INNER JOINS with Sequelize ORM
- 26
where condition failed on multiple inner joins in sql
- 27
PDO multiple inner joins appear to be looping
- 28
How to improve query performance for multiple inner joins?
- 29
Multiple Inner Joins from same table
Comments