Hi there,
I'm new on this list because of a long query I can't write
successfully. I guess I could post my question on a more general SQL
list but since my database engine is MySQL I post it here. It's a bit
long to read because of the care taken to explain all things. Please
forgive me if I'm annoying any people here...
To keep things simple, let's say I've got a table created like this :
CREATE TABLE family
(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent SMALLINT UNSIGNED NULL,
type ENUM ('boy', 'girl') NOT NULL
)
In this table 'family', rows can be related to each other using the
(internal) foreign key 'parent'. The attribute 'type' is just there
for the interest of my example. In my application, the real table is
much more complicated. Note that each row can have only one parent : a
father OR a mother.
After a few INSERTs, I've got this table :
+----+--------+------+
| id | parent | type |
+----+--------+------+
| 1 | NULL | boy |
| 2 | NULL | girl |
| 3 | 1 | boy |
| 4 | 1 | boy |
| 5 | 1 | girl |
| 6 | 1 | girl |
| 7 | 1 | girl |
| 8 | 3 | boy |
| 9 | 3 | girl |
| 10 | 3 | girl |
+----+--------+------+
10 rows in set (0.13 sec)
If we look at this, we can say :
- Boy #1 is orphan.
- Boy #1 has two sons (#3 and #4) and three daughters (#5 to #7).
- Girl #2 is orphan.
- Girl #2 has no children.
- Boy #3 has row #1 as parent (father).
- Boy #3 has one son (#8) and two daughters (#9 and #10).
- Boy #3 has one brother (#4) and three sisters (#5 to #7).
- Boy #4 has row #1 as parent (father).
... and so on.
What I want to do is getting all rows in one query. Quite easy... But
things get harder if I want to count children and siblings for each
row in the same time. My first try was like this :
SELECT me.id, me.parent,
COUNT(son.id) AS sons,
COUNT(daughter.id) AS daughters,
COUNT(brother.id) AS brothers,
COUNT(sister.id) AS sisters
FROM family AS me
LEFT JOIN family AS son ON son.parent=me.id AND son.type='boy'
LEFT JOIN family AS daughter ON daughter.parent=me.id
AND daughter.type='girl'
LEFT JOIN family AS brother ON brother.parent=me.parent
AND brother.type='boy' AND brother.id!=me.id
LEFT JOIN family AS sister ON sister.parent=me.parent AND
sister.type='girl' AND sister.id!=me.id
GROUP BY me.id
But it didn't work (some rows have too much children or siblings) :
+----+--------+------+-----------+----------+---------+
| id | parent | sons | daughters | brothers | sisters |
+----+--------+------+-----------+----------+---------+
| 1 | NULL | 6 | 6 | 0 | 0 |
| 2 | NULL | 0 | 0 | 0 | 0 |
| 3 | 1 | 6 | 6 | 6 | 6 |
| 4 | 1 | 0 | 0 | 3 | 3 |
| 5 | 1 | 0 | 0 | 4 | 4 |
| 6 | 1 | 0 | 0 | 4 | 4 |
| 7 | 1 | 0 | 0 | 4 | 4 |
| 8 | 3 | 0 | 0 | 0 | 2 |
| 9 | 3 | 0 | 0 | 1 | 1 |
| 10 | 3 | 0 | 0 | 1 | 1 |
+----+--------+------+-----------+----------+---------+
10 rows in set (0.17 sec)
Then, I tried the following query to find out the problem :
SELECT me.id, me.parent,
son.id AS sons,
daughter.id AS daughters,
brother.id AS brothers,
sister.id AS sisters
LEFT JOIN family AS son ON son.parent=me.id AND son.type='boy'
LEFT JOIN family AS daughter ON daughter.parent=me.id
AND daughter.type='girl'
LEFT JOIN family AS brother ON brother.parent=me.parent
AND brother.type='boy' AND brother.id!=me.id
LEFT JOIN family AS sister ON sister.parent=me.parent AND
sister.type='girl' AND sister.id!=me.id
In fact, I've got repeated values for several counts in my result set
because of the multiple LEFT JOIN's :
+----+--------+------+-----------+----------+---------+
| id | parent | sons | daughters | brothers | sisters |
+----+--------+------+-----------+----------+---------+
| 1 | NULL | 3 | 5 | NULL | NULL |
| 1 | NULL | 3 | 6 | NULL | NULL |
| 1 | NULL | 3 | 7 | NULL | NULL |
| 1 | NULL | 4 | 5 | NULL | NULL |
| 1 | NULL | 4 | 6 | NULL | NULL |
| 1 | NULL | 4 | 7 | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL |
| 3 | 1 | 8 | 9 | 4 | 5 |
| 3 | 1 | 8 | 9 | 4 | 6 |
| 3 | 1 | 8 | 9 | 4 | 7 |
| 3 | 1 | 8 | 10 | 4 | 5 |
| 3 | 1 | 8 | 10 | 4 | 6 |
| 3 | 1 | 8 | 10 | 4 | 7 |
| 4 | 1 | NULL | NULL | 3 | 5 |
| 4 | 1 | NULL | NULL | 3 | 6 |
| 4 | 1 | NULL | NULL | 3 | 7 |
| 5 | 1 | NULL | NULL | 3 | 6 |
| 5 | 1 | NULL | NULL | 3 | 7 |
| 5 | 1 | NULL | NULL | 4 | 6 |
| 5 | 1 | NULL | NULL | 4 | 7 |
| 6 | 1 | NULL | NULL | 3 | 5 |
| 6 | 1 | NULL | NULL | 3 | 7 |
| 6 | 1 | NULL | NULL | 4 | 5 |
| 6 | 1 | NULL | NULL | 4 | 7 |
| 7 | 1 | NULL | NULL | 3 | 5 |
| 7 | 1 | NULL | NULL | 3 | 6 |
| 7 | 1 | NULL | NULL | 4 | 5 |
| 7 | 1 | NULL | NULL | 4 | 6 |
| 8 | 3 | NULL | NULL | NULL | 9 |
| 8 | 3 | NULL | NULL | NULL | 10 |
| 9 | 3 | NULL | NULL | 8 | 10 |
| 10 | 3 | NULL | NULL | 8 | 9 |
+----+--------+------+-----------+----------+---------+
32 rows in set (0.17 sec)
So, I tried to add the DISTINCT keyword to all the COUNT's of my first
query :
SELECT me.id, me.parent,
COUNT(DISTINCT son.id) AS sons,
COUNT(DISTINCT daughter.id) AS daughters,
COUNT(DISTINCT brother.id) AS brothers,
COUNT(DISTINCT sister.id) AS sisters
FROM family AS me
LEFT JOIN family AS son ON son.parent=me.id AND son.type='boy'
LEFT JOIN family AS daughter ON daughter.parent=me.id
AND daughter.type='girl'
LEFT JOIN family AS brother ON brother.parent=me.parent
AND brother.type='boy' AND brother.id!=me.id
LEFT JOIN family AS sister ON sister.parent=me.parent AND
sister.type='girl' AND sister.id!=me.id
GROUP BY me.id
And it nearly worked... Except that the NULL values are counted as
well, what's terrible for me :
+----+--------+------+-----------+----------+---------+
| id | parent | sons | daughters | brothers | sisters |
+----+--------+------+-----------+----------+---------+
| 1 | NULL | 2 | 3 | 1 | 1 |
| 2 | NULL | 1 | 1 | 1 | 1 |
| 3 | 1 | 1 | 2 | 1 | 3 |
| 4 | 1 | 1 | 1 | 1 | 3 |
| 5 | 1 | 1 | 1 | 2 | 2 |
| 6 | 1 | 1 | 1 | 2 | 2 |
| 7 | 1 | 1 | 1 | 2 | 2 |
| 8 | 3 | 1 | 1 | 1 | 2 |
| 9 | 3 | 1 | 1 | 1 | 1 |
| 10 | 3 | 1 | 1 | 1 | 1 |
+----+--------+------+-----------+----------+---------+
10 rows in set (0.18 sec)
So, I would like to know if there's any way to force the
COUNT(DISTINCT...) construct to ignore NULL values or if it's possible
to achieve the same result in a completely reformulated query...
Thanks a lot in advance to all SQL experts that could help me.
Denis.
---
Denis BAURAIN (aka Bob Morane)
FNRS Research Fellow
Lab Genetics of Microorganisms
Dept Plant Biology - B22
University of Liege (Sart Tilman)
B-4000 Liege (BELGIUM)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
