0

For example, I have two tables:

CREATE TABLE `augus1dl_ecom`.`test` 
(
     `id` INT NOT NULL AUTO_INCREMENT, 
     `cn_no` VARCHAR(10) NOT NULL,
     `amt` DECIMAL(8,2) NOT NULL, 
     PRIMARY KEY (`id`)
) ENGINE = MyISAM;

CREATE TABLE `augus1dl_ecom`.`test2` 
(
     `id` INT NOT NULL AUTO_INCREMENT,
     `docket_id` VARCHAR(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, 
     `cn_id` INT NOT NULL, 
     PRIMARY KEY (`id`)
) ENGINE = MyISAM;

INSERT INTO `augus1dl_ecom`.`test` (`id`, `cn_no`, `amt`) 
VALUES (NULL, 'A', '100'), (NULL, 'B', '200');

INSERT INTO `augus1dl_ecom`.`test2` (`id`, `docket_id`, `cn_id`) 
VALUES (NULL, 'DK1', '1'), (NULL, 'DK2', '2'), (NULL, 'DK3', '1') ;

In the test table the cn_no is unique. But in test2 table, a single cn_no (refers to cn_id) could be inserted more than once.

I mean test.id = test2.cn_id.

Now I run the following query:

SELECT a.* 
FROM test2 a, test b 
WHERE b.id = a.cn_id AND b.cn_no = 'A'

It runs successfully. But I don't need data, I need the number of rows/entries. So I run the following query to retrieve number of rows (count) from test, depending condition which is in test2:

SELECT count(a.*) 
FROM test2 a, test b 
WHERE b.id = a.cn_id AND b.cn_no = 'A';

But the query fails with an error. This question was suggested as duplicate of SQL: Combine Select count(*) from multiple tables but it is not. Here I want count(*) depending on the data which is available in another table. How can I do this correctly?

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
user3764374
  • 41
  • 1
  • 9
  • Possible duplicate of [SQL: Combine Select count(\*) from multiple tables](http://stackoverflow.com/questions/1279569/sql-combine-select-count-from-multiple-tables) – Hossam Apr 15 '17 at 22:08
  • Just use `SELECT count(*)` instead of `count(a.*)`. You would get the same amount of lines event if `a.*` worked. `count` counts a single column. `count(*)` is just a shortcut that means "count number of lines, I do not care for which column. `count(a.*)` is equivalent to `count(id, cn_no, ...)` which is invalid syntax. – Viliam Aboši Apr 15 '17 at 22:08
  • But `count (column_name)` does not count field with `NULL` values. It could be a problem in future, working with other scenario. – user3764374 Apr 15 '17 at 22:49
  • Count is an aggregate function so you need to add a group by clause, ie group by b.id – James Apr 16 '17 at 17:57

1 Answers1

0

Try the following query

SELECT COUNT(a.*) FROM test2 a INNER JOIN test b ON b.id=a.cn_id WHERE b.cn_no='A' GROUP BY(b.id);

new query

SELECT COUNT(*)
FROM
(
    SELECT a.* FROM test2 a INNER JOIN test b ON b.id=a.cn_id WHERE WHERE b.cn_no='A' 
) AS final

I suggest you use inner join they are easy and very efficient. Hope it helps. if it doesn't do let me know I haven't tested it.

Ali Rasheed
  • 2,765
  • 2
  • 18
  • 31