Multiple Select＆INNER JOIN Multiple Where子句

I have 4 tables
we_vehicles_coordinates
we_vehicles
we_drivers
we_clients

我有4个表we_vehicles_coordinates we_vehicles we_drivers we_clients

SELECT mrk_lat,we_clients.client_id,mrk_lng ,engine_status ,created_date,live_fuel,
                    count(we_drivers.driver_id) as total_drivers,
                    count(we_vehicles.veh_id) as total_veh,
                    count(we_vehicles.veh_status) as run_veh,
                    count(we_vehicles.veh_status) as stop_veh
                    FROM `we_vehicles_coordinates` 
                INNER JOIN we_vehicles ON
                    we_vehicles.veh_id = we_vehicles_coordinates.veh_id
                INNER JOIN we_drivers ON
                    we_drivers.veh_id = we_vehicles.veh_id
                INNER JOIN we_clients ON 
                    we_clients.client_id = we_vehicles.client_id
                    WHERE (we_vehicles.veh_status='1') AND
                    (we_vehicles.veh_status='0') AND
                    (we_clients.client_id= 3)

It gives me null and 0. Tried a lot but didn't got success.
Thanks

它给了我null和0.尝试了很多但没有成功。谢谢

2 个解决方案

#1

we_vehicles.veh_status cannot equal 1 and 0 simultaneously.

we_vehicles.veh_status不能同时等于1和0。

or as it was put by a comment below:

或者正如以下评论所述:

SELECT /* stuff here */
   , SUM(IF(we_vehicles.veh_status = 1, 1, 0) AS run_veh
   , SUM(IF(we_vehicles.veh_status = 0, 1, 0) AS stop_veh
FROM /* more stuff */
WHERE (we_vehicles.veh_status in ('1','0') AND (we_clients.client_id= 3)
;

Note: I am not sure the JOINs you are using are necessarily appropriate for the final result you want, and using a GROUP BY might be appropriate as well.

注意:我不确定您使用的JOIN是否一定适合您想要的最终结果,并且使用GROUP BY也可能是合适的。

#2

According to your table and something you want, I suggest instead of using join use union (and If you want to duplicate rows, use union all)

根据你的表和你想要的东西,我建议不要使用join join union(如果你想复制行,请使用union all)

#1