Hi,
I'm including your post to the forum as well.
The problem I think is the fact that you need to
do LEFT JOIN in two directions which quite often
don't turn out what you want.
So what you can do is to do two queries, UNION
them together and form a derived table that you
then do your final SELECT FROM,
something like this:
SELECT Id, Name, SUM(Cars), SUM(Houses)
FROM ((SELECT p.id AS Id,
p.name AS Name,
COUNT(c.id) AS Cars,
0 AS Houses
FROM person p
LEFT JOIN car c ON c.person_id=p.id
GROUP BY p.id)
UNION
(SELECT p.id AS Id,
p.name AS Name,
0 AS Cars,
COUNT(h.id)
FROM person p
LEFT JOIN house h ON h.person_id=p.id
GROUP BY p.id)) AS tablea
GROUP BY Id
HTH,
/Johan
select * from person
+----+---------+
| id | name |
+----+---------+
| 1 | amit |
| 2 | don |
| 3 | prakash |
| 4 | dave |
+----+---------+
select * from car
+----+-----------+----------------+
| id | person_id | name |
+----+-----------+----------------+
| 1 | 1 | maruti 800 |
| 2 | 1 | opel corsa |
| 3 | 4 | toyota innova |
| 4 | 4 | hyundai santro |
+----+-----------+----------------+
select * from house
+----+-----------+---------------------------+
| id | person_id | address |
+----+-----------+---------------------------+
| 1 | 1 | 27 Ambedkar Road, Pune |
| 2 | 2 | 55 East 3rd Ave, San Jose |
| 3 | 2 | 21 Oak Blvd, San Jose |
+----+-----------+---------------------------+
3 rows in set (0.00 sec)
I'm trying to get a single select statement which displays the id, name
of person and the number of cars and houses they own. (person_id is the
foreign key in car, house table that links to person table)
the query should output someting like
-------------------------------------
id name Num cars Num houses
-------------------------------------
1 amit 2 1
2 don 0 2
3 prakash 0 0
4 dave 2 0
Yashesh Bhatia skrev:
hey thx for the reply..
here's my query..
http://forums.mysql.com/read.php?20,119150,119150#msg-119150
thx.
yashesh bhatia.
On 10/4/06, Rob Desbois <[EMAIL PROTECTED]> wrote:
Yes, ask away :)
> Hi,
Is the the right group to post for questions with SQL Queries ?
thx.
yashesh bhatia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
<
---------- Original Message ----------
FROM: "Yashesh Bhatia" <[EMAIL PROTECTED]>
TO: mysql@lists.mysql.com
DATE: Wed, 4 Oct 2006 17:36:38 +0530
SUBJECT: Help with SQL Queries
Hi,
Is the the right group to post for questions with SQL Queries ?
thx.
yashesh bhatia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
