MySQL left join with multiple views
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.
btongeorge
Updated on July 10, 2022Comments
-
btongeorge almost 2 years
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.
-
btongeorge over 12 yearsSo simple! Thanks very much, with that last piece of info I was able to make my query work. I removed the WHERE clause altogether as suggested and it works a treat.