Page 1 of 1

Get table1, COUNT of table1 in table2

Posted: Sun Feb 26, 2006 5:18 am
by anjanesh
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

Posted: Sun Feb 26, 2006 9:24 am
by feyd

Code: Select all

SELECT COUNT(`id`) FROM `t2` GROUP BY `id`
I don't see a reason why it'd require a join.

Posted: Sun Feb 26, 2006 10:18 am
by anjanesh
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

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');
I want this output

Code: Select all

1 , value 1 , 3
2 , value 2 , 6
3 , value 3 , 2
4 , value 4 , 0

Posted: Sun Feb 26, 2006 10:39 am
by Gambler
Can you do sub-queries?

Posted: Sun Feb 26, 2006 10:44 am
by anjanesh
Will work on my PC - since Im using MySQL 5.0 but my shared host is using 4.0 or 3.23 I think - so I was thinking this would work using JOINS ?

Posted: Sun Feb 26, 2006 5:58 pm
by Gambler

Code: 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

Posted: Mon Feb 27, 2006 3:15 am
by raghavan20

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 0

Posted: Wed Mar 01, 2006 11:16 pm
by anjanesh
Thanks 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.