I need to create a view with the record count from 3 different tables but with the same columns.

That is, these queries

SELECT COUNT (*) AS count_a from table1 ta
SELECT COUNT (*) AS count_b from table2 tb
SELECT COUNT (*) AS count_c from table3 tc

Introduced in a view, the join can be a solution but I think this would take a lot of work, and likewise being a different count in each table, when using join it would discard some, if in a table there are 100, 200, 15. No I know if the join would be the solution to this.

I wanted to use the GROUP_CONCAT but reviewing a bit does not seem to be a solution, anyway. In simple words I want the count of different tables in a view so that it is displayed

Desired result

Greetings and thanks in advance

PS: right now I do not remember well how the JOIN works

CodePudding user response：

try a query like this:

SELECT
(SELECT count(*) FROM table1) AS count_a,
(SELECT count(*) FROM table2) AS count_b,
(SELECT count(*) FROM table3) AS count_c;

CodePudding user response：

If you want to display only the result of these 3 counts, without any join between tables. I suggest to use a script like this :

create view desired_result
as select * from (
(SELECT COUNT(*) AS count_a from table1) t1,
(SELECT COUNT(*) AS count_b from table2) t2,
(SELECT COUNT(*) AS count_c from table3) t3);

You can try it with this fiddle link

CodePudding user response：

If you like without JOIN:

SELECT
(SELECT count(*) as count_a FROM table1) AS count_a,
(SELECT count(*) as count_b FROM table2) AS count_b,
(SELECT count(*) as count_c FROM table3) AS count_c;

If you like with JOIN:

SELECT ta.count_a, tb.count_b, tc.count_c FROM
 (
SELECT COUNT (*) AS count_a, 1 AS rn from table1) ta
JOIN (
SELECT COUNT (*) AS count_b, 1 AS rn from table2) tb
ON ta.rn = tb.rn
 JOIN (
SELECT COUNT (*) AS count_c, 1 AS rn from table3)  tc
ON ta.rn = tc.rn