I have three tables, lets say t1, t2, t3 with columns as
t1.c1, t1.c2, t1.c3
t2.c4, t2.c1, t2.c5
t3.c6, t3.c4
t1.c1, t2.c4 and t3.c6 are primary, autoincrements fields of their respective tables.
t2.c1 is a foreign key in t2 referring to t1.c1 in t1
t3.c4 is a foreign key in t3 referring to t2.c4 in t2
I want to get the following output for any given value of t2.c5 (say X)
SORRY - Mistake in output columns!
I want output to be
t2.c4, t1.c2, count
and not
t2.c1, t1.c2, count
where count is the number of records in t3 for the given value of t2.c5 and must be zero if no record exists where t2.c5=X and t3.c4=t2.c4
Can this be done in one select query?
Here is sample data:
Table: t1
+------+-------+------+
| c1 | c2 | c3 |
+------+-------+------+
| 1 | 11111 | 111 |
| 2 | 22222 | 222 |
| 3 | 33333 | 333 |
+------+-------+------+
Table: t2
+------+------+------+
| c4 | c1 | c5 |
+------+------+------+
| 1 | 1 | 11 |
| 2 | 1 | 11 |
| 3 | 2 | 11 |
| 4 | 2 | 12 |
| 5 | 3 | 12 |
| 6 | 2 | 12 |
+------+------+------+
Table: t3
+------+------+
| c6 | c4 |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 5 |
+------+------+
For t2.c5=11, the output is
+-------------------+
t2.c4 | t1.c2| Count
+-------------------+
1 | 11111| 2
2 | 11111| 0
3 | 22222| 0
+-------------------+
For t2.c5=12, the output is
+-------------------+
t2.c4 | t1.c2| Count
+-------------------+
4 | 22222| 0
5 | 33333| 1
6 | 22222| 0
Try this query:
SELECT t2.c4, t1.c2, COUNT(t3.c6)
FROM t1
INNER JOIN t2 ON t1.c1 = t2.c1
LEFT JOIN t3 ON t3.c4 = t2.c4
WHERE t2.c5 = '11'
GROUP BY t2.c4, t1.c2
Output:
c4 c2 count
-------------
1 11111 2
2 11111 0
3 22222 0
With this query:
SELECT t2.c4, t1.c2, COUNT(t3.c6) as count
FROM t1
INNER JOIN t2 ON t1.c1 = t2.c1
LEFT JOIN t3 ON t3.c4 = t2.c4
WHERE t2.c5 = '12'
GROUP BY t2.c4, t1.c2
output is:
c4 c2 count
-----------------
4 22222 0
5 33333 1
6 22222 0
Эта статья взята из Интернета, укажите источник при перепечатке.
Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.
я говорю два предложения