CODEWITHSUNDEEP
mysqlsqldatabasejoin
Karuppiah RK
Karuppiah RK

Reputation: 3964

join multiple queries error

First query

SELECT a.*,
       ifnull(cnt_all,0) total_drivers,
       ifnull(cnt_active,0) active_drivers,
       ifnull(cnt_idle,0) idle_drivers
FROM ta_agent a
LEFT JOIN
  (SELECT agent_id,
          count(*) cnt_all
   FROM ta_drivers
   GROUP BY agent_id) cnt ON a.agent_id=cnt.agent_id
LEFT JOIN
  (SELECT agent_id,
          count(*) cnt_idle
   FROM ta_drivers
   WHERE last_viewed=0
   GROUP BY agent_id) idle ON a.agent_id=idle.agent_id
LEFT JOIN
  (SELECT agent_id,
          count(*) cnt_active
   FROM ta_drivers
   WHERE last_viewed=1
   GROUP BY agent_id) active ON a.agent_id=active.agent_id

Second query

SELECT FROM_UNIXTIME(date_of_registration, '%Y-%m-%d %H:%i:%s') AS user_registeredon
FROM ta_agent a,
     ta_subscription s
WHERE s.agent_id = a.agent_id

These two queries working fine when I run separately. I want to join these two queries. I have tried to join these two queries but I got this error Every derived table must have its own alias

I have tried this

select a.*, ifnull(cnt_all,0) total_drivers,ifnull(cnt_active,0) active_drivers, ifnull(cnt_idle,0) idle_drivers
    from ta_agent a left join (select agent_id, count(*) cnt_all
                       from ta_drivers
                       group by agent_id) cnt on a.agent_id=cnt.agent_id

    left join (select agent_id, count(*) cnt_idle
                       from ta_drivers
                       where last_viewed=0
                       group by agent_id) idle on a.agent_id=idle.agent_id

    left join (select agent_id, count(*) cnt_active
                       from ta_drivers
                       where last_viewed=1
                       group by agent_id) active on a.agent_id=active.agent_id

    left join(SELECT FROM_UNIXTIME( date_of_registration, '%Y-%m-%d %H:%i:%s' ) AS user_registeredon FROM ta_subscription WHERE agent_id = a.agent_id)

I don't think this is the correct method to get the result..

Upvotes: 0

Views: 42

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 1549

Please mentioned how do you want to join this two query.. Looking for something like below:

SELECT *
FROM
  (SELECT a.*,
          ifnull(cnt_all,0) total_drivers,
          ifnull(cnt_active,0) active_drivers,
          ifnull(cnt_idle,0) idle_drivers
   FROM ta_agent a
   LEFT JOIN
     (SELECT agent_id,
             count(*) cnt_all
      FROM ta_drivers
      GROUP BY agent_id) cnt ON a.agent_id=cnt.agent_id
   LEFT JOIN
     (SELECT agent_id,
             count(*) cnt_idle
      FROM ta_drivers
      WHERE last_viewed=0
      GROUP BY agent_id) idle ON a.agent_id=idle.agent_id
   LEFT JOIN
     (SELECT agent_id,
             count(*) cnt_active
      FROM ta_drivers
      WHERE last_viewed=1
      GROUP BY agent_id) active ON a.agent_id=active.agent_id)tempaliasA
LEFT JOIN
  (SELECT FROM_UNIXTIME(date_of_registration, '%Y-%m-%d %H:%i:%s') AS user_registeredon,
          agent_id
   FROM ta_agent a,
        ta_subscription s
   WHERE s.agent_id = a.agent_id)tempaliasB ON tempaliasA.agent_id = tempaliasB.agent_id

Upvotes: 1

Frazz
Frazz

Reputation: 3043

If you're willing to do it without all those joins, then this should do it:

SELECT a.*,
  (SELECT COUNT(*) 
    FROM ta_drivers
    WHERE agent_id=a.agent_id
  ) AS total_drivers,
  (SELECT COUNT(*) 
    FROM ta_drivers
    WHERE agent_id=a.agent_id AND lastviewed=1
  ) AS active_drivers,
  (SELECT COUNT(*) 
    FROM ta_drivers
    WHERE agent_id=a.agent_id AND lastviewed=0
  ) AS idle_drivers,
  (SELECT FROM_UNIXTIME(date_of_registration, '%Y-%m-%d %H:%i:%s')
    FROM ta_subscription
    WHERE agent_id=a.agent_i
  ) AS user_registered

I'd add that maybe the problem with what you were trying to do is that in both single queries you are using an alias "a"... when you combine them then the resulting query is ambiguous.

Upvotes: 0

Related Questions