Mysql Select from different tables

Question

Hello, I'd like to combine these four queries to get this result:

Vendors
-----------
select i_vendor, name as Vendor from Vendors

Customers
-----------
select i_customer, name as Customer from Customers

Calls
-----------
select i_vendor,i_customer, id as Call, date_format(connect_time,"%Y-%m-%d %H") as Date, duration from CDR_Vendors where connect_time between curdate() and now()

Failed_Calls
------------
select i_vendor,i_customer, id as Failed_Call, date_format(connect_time,"%Y-%m-%d %H") as Date from CDR_Vendors_Failed where connect_time between curdate() and now()

Result
------------
Date, Vendor, Customer, Count(Call), Count(Failed_Call), Sum()duration

2010-10-30 00 | Vendor1 | Customer1 | 100 | 300 | 8000
2010-10-30 01 | Vendor1 | Customer2 | 267 | 100 | 2000
2010-10-30 02 | Vendor1 | Customer3 | 388 | 20  | 100

2010-10-30 00 | Vendor2 | Customer1 | 140 | 120 | 50
2010-10-30 01 | Vendor2 | Customer2 | 102 | 309 | 529
2010-10-30 02 | Vendor2 | Customer3 | 156 | 78  | 1000

2010-10-30 00 | Vendor3 | Customer1 | 190 | 567 | 876
2010-10-30 01 | Vendor3 | Customer2 | 215 | 987 | 765
2010-10-30 02 | Vendor3 | Customer3 | 383 | 321 | 123

I tested the following query with a single customer and a single Vendor but it takes a long time

SELECT
          Vendors.name as Vendor,
          Customers.name as Customer
                  date_format(connect_time,"%Y-%m-%d %H") AS date,
                  Failed.NotConnected,
                  count(id) as calls,
                  sum(`duration`)
   FROM test.`CDR_Vendors`
       inner join Vendors on (CDR_Vendors.i_vendor = Vendors.i_vendor)
       inner join Customers on (CDR_Vendors.i_customer = Customers.i_customer)
       inner join
               (SELECT
               Vendors.name as Vendor,
               Customers.name as Customer,        
               date_format(connect_time,"%Y-%m-%d %H") AS date,
               Count(id) as NotConnected
               FROM `CDR_Vendors_Failed`
               inner join Vendors on (CDR_Vendors_Failed.i_vendor = Vendors.i_vendor)
               inner join Customers on (CDR_Vendors_Failed.i_customer = Customers.i_customer)
               WHERE
               Customers.name = "Customer1"
               and
               Vendors.name = "Vendor1"
               and connect_time between curdate() and now()
               GROUP by date
               ORDER BY date
            )Failed on Failed.date = date_format(connect_time,"%Y-%m-%d %H")
       WHERE
       Customers.name = "Customer1"
       and
       Vendors.name = "Vendor1"
       and connect_time between curdate() and now()
       GROUP by date
       ORDER BY date

Please, what is the best way to get a result ?

thank you in advance

Answer 1

I think you can take a better approach with views --> Introduction to Views