Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL left join with multiple views

I have the following query, which I designed to compile data from a number of views based on client data.

SELECT 
  vw_clients.client_id, 
  name, 
  exts, 
  vms, 
  ivrs, 
  queues, 
  conf10, 
  conf20, 
  conf30
FROM 
  vw_clients, 
  vw_exts, 
  vw_vms, 
  vw_ivrs, 
  vw_queues, 
  vw_conf10, 
  vw_conf20, 
  vw_conf30
WHERE 
  vw_clients.client_id = vw_exts.client_id AND 
  vw_clients.client_id = vw_vms.client_id AND 
  vw_clients.client_id = vw_ivrs.client_id AND 
  vw_clients.client_id = vw_queues.client_id AND
  vw_clients.client_id = vw_conf10.client_id AND
  vw_clients.client_id = vw_conf20.client_id AND
  vw_clients.client_id = vw_conf30.client_id;

The query works fine so long as there are records in every view relating to the records in vw_clients. However I need to modify this to use a left join, so that it returns all records from vm_clients and only those from the other views that have records for those clients.

I've read about left joins, but at most I have only found info on joining one or maybe two tables - but I need to join 8. Do I do a left join on vw_clients.client_id to the corresponding client_id field in all of the views? What's the syntax for that?

Would be grateful for any help. I'm very close to solving this issue and I think this is the last piece of the puzzle!

Many thanks.

like image 238
btongeorge Avatar asked Dec 03 '11 00:12

btongeorge


People also ask

Can we join two views in MySQL?

That's one of the points of using views, you can (mostly) treat them like tables. @barmar The only problem is that MySQL doesnot support Full outer join for views. @pnkjmndhl It doesn't support full outer join for tables, either. You emulate it for views the same way you emulate it for ordinary tables.

Can you left join view and table?

You can use left join by putting vw_clients in the first in the from list, then all other tables followed after left join. The left join can join only two tables or one "result set" and a table,where the result set is the result of the former join.

Can LEFT join produce more rows?

Left joins can increase the number of rows in the left table if there are multiple matches in the right table.


1 Answers

You can use left join by putting vw_clients in the first in the from list, then all other tables followed after left join. The left join can join only two tables or one "result set" and a table,where the result set is the result of the former join.

In your case:

SELECT 
    T0.client_id, name, exts, vms, ivrs, queues, conf10, conf20, conf30
FROM 
    vw_clients T0
    left join  vw_exts T1 on T0.client_Id=T1.client_id
    Left join  vw_vms T2 on ...
    ...
Where ...

Maybe here you don't need where clause.

like image 81
chance Avatar answered Oct 19 '22 08:10

chance