CODEWITHSUNDEEP
sqlmariadbmariasql
James
James

Reputation: 1

In Mariadb, my select queries on multiple tables return excessive rows

I'm very new to Maria db but I haven't found a solution to this problem. I have a database with several tables. Two of the tables are employees and customers. They have no relation to each other.

create table clients (id int, name varchar(50), city varchar (20))
create table workers (id int, name varchar (50), city varchar(20))

I'm trying to write a select query that will return the name of a city, the total number of workers, and the total number of clients in that city. The clients table has over 2000 entries and the workers table has 60.

The problem is that if I do this:

Select workers.id, clients.id from workers, clients;

I get 120,000 rows (60 * 2000). How do I achieve my goal?

Sorry if the formatting is weird.

Upvotes: 0

Views: 861

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13016

Here's what you need.

select
  t1.city,
  sum(t1.workers),
  sum(t1.clients)
from
  (
    select
      city,
      1 as 'workers',
      0 as 'clients'
    from
      workers
    union all
    select
      city,
      0 as 'workers',
      1 as 'clients'
    from
      clients
  ) t1
group by
  t1.city;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can use union all and group by:

select city, sum(num_clients) as num_clients,
       sum(num_workers) as num_workers
from ((select city, count(*) as num_clients, 0 as num_workers
       from clients 
       group by city
      ) union all
      (select city, 0 as num_clients, count(*) as num_workers
       from workers 
       group by city
      ) 
     ) c
group by city;

Upvotes: 1

Related Questions