Mysql Select from different tables

Question

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

Sudantha · Accepted Answer · 2010-10-31 13:47:16Z

1

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

answered Oct 31, 2010 at 13:47

Sudantha

16.3k45 gold badges110 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nipuna Over a year ago

Yes rather than going for such a large query, you better do that using views. It has its advantages but also disadvantages.

Anush Prem · Accepted Answer · 2011-01-06 15:43:53Z

try this query

SELECT
    count(cid) as CALLS,
    count(fid) as FAILED_CALLS,
    DateHour,
    vendor as Vendor,
    customer as Customer,
    Sum(duration) as Duration
FROM
    (
        SELECT
            c.id as cid,
                    NULL as fid,
            date_format(connect_time,"%Y-%m-%d %H") as DateHour,
            cu.name as customer,
            v.name as vendor,
            c.duration
        FROM
            calls as c
                left join
                    customers as cu
                        on
                            cu.i_customer = c.i_customer
                left join
                    vendors as v
                        on
                            v.i_vendor = c.i_vendor
        WHERE
            date_format(connect_time,"%Y-%m-%d %H") between CURDATE() and NOW()
        GROUP BY
            DateHour, customer, vendor

        UNION

        SELECT
                    NULL as cid,
            c.id as fid,
            date_format(connect_time,"%Y-%m-%d %H") as DateHour,
            cu.name as customer,
            v.name as vendor
        FROM
            failed_calls as c
                left join
                    customers as cu
                        on
                            cu.i_customer = c.i_customer
                left join
                    vendors as v
                        on
                            v.i_vendor = c.i_vendor
        WHERE
            date_format(connect_time,"%Y-%m-%d %H") between CURDATE() and NOW()
        GROUP BY
            DateHour, customer, vendor
    ) as tmp_table
GROUP BY
    DateHour, vendor, customer;

I didnt try running it. But i hope this will work in the way you expect. If it didnt thn please give me ur db structure ( or preferably create table ) and some dummy data. I will write the proper query for you. :)

Collectives™ on Stack Overflow

Mysql Select from different tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related