Get table1, COUNT of table1 in table2

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Get table1, COUNT of table1 in table2

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post by Gambler »

Can you do sub-queries?
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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 ?
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.
Post Reply