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.
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.
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.
Left joins can increase the number of rows in the left table if there are multiple matches in the right 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With