Get table1, COUNT of table1 in table2
Moderator: General Moderators
Get table1, COUNT of table1 in table2
I have a table - t1 which has a primary field, id
I have another table t2 which has a field called id that is used to store t1's id. Therefore t2 has 0,1 or more than 1 t1's ids.
I want to get all rows of t1 with the count of each t2's t1 ids. I know I need to use a LEFT JOIN and a COUNT but cant figure out how to combine them.
Any idea of the SQL query ?
Thanks
I have another table t2 which has a field called id that is used to store t1's id. Therefore t2 has 0,1 or more than 1 t1's ids.
I want to get all rows of t1 with the count of each t2's t1 ids. I know I need to use a LEFT JOIN and a COUNT but cant figure out how to combine them.
Any idea of the SQL query ?
Thanks
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
SELECT COUNT(`id`) FROM `t2` GROUP BY `id`That works for t2 alone - I want t1's rows added with another column - which is total t1's ids in t2 - moreover - it wont show ones where there is no id in t2 yet exists in t1.
SQL Statements
I want this output
SQL Statements
Code: Select all
CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`value` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `t1` (`id`, `value`) VALUES (1, 'value 1'),
(2, 'value 2'),
(3, 'value 3'),
(4, 'value 4');
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`info` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `t2` (`id`, `info`) VALUES (1, 'info 1'),
(1, 'info 2'),
(1, 'info 3'),
(2, 'info 4'),
(2, 'info 5'),
(2, 'info 6'),
(2, 'info 7'),
(2, 'info 8'),
(2, 'info 9'),
(3, 'info 10'),
(3, 'info 11');Code: Select all
1 , value 1 , 3
2 , value 2 , 6
3 , value 3 , 2
4 , value 4 , 0Code: Select all
SELECT t1.id, t1.value, COUNT(t2.id) FROM t2 right JOIN t1 ON t1.id=t2.id GROUP BY t1.id order by t1.id- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
Code: Select all
SELECT t1.id, t1.value, count( t2.id )
FROM t1
LEFT JOIN t2 ON t2.id = t1.id
GROUP BY t2.id
ORDER BY t1.id
LIMIT 0 , 30
id value count( t2 . id )
1 value 1 3
2 value 2 6
3 value 3 2
4 value 4 0Thanks for both the solutions - it worked perfectly for the database mentioned above.
In the real database, t1 has 3000 records and t2 has 6000 records.
raghavan20 - Your query was going on forever. I had to abort mysql when I ran the query in mysql command line and close FF when run in phpMyAdmin - I had to restart my PC because of some background resource usage. I tried twice - both times same effect.
The difference between the 2 queries :
1) LEFT JOIN in Gambler vs RIGHT JOIN in raghavan20
2)GROUP BY t1.id in Gambler vs GROUP BY t2.id in raghavan20
I dont know what really caused the time-crash, but is it GROUP BY t2.id ORDER BY t1.id ? because I dont see how a LEFT or RIGHT JOIN can make much difference in this case.
In the real database, t1 has 3000 records and t2 has 6000 records.
raghavan20 - Your query was going on forever. I had to abort mysql when I ran the query in mysql command line and close FF when run in phpMyAdmin - I had to restart my PC because of some background resource usage. I tried twice - both times same effect.
The difference between the 2 queries :
1) LEFT JOIN in Gambler vs RIGHT JOIN in raghavan20
2)GROUP BY t1.id in Gambler vs GROUP BY t2.id in raghavan20
I dont know what really caused the time-crash, but is it GROUP BY t2.id ORDER BY t1.id ? because I dont see how a LEFT or RIGHT JOIN can make much difference in this case.