Multiple Select & INNER JOIN Multiple Where clause

Question

I have 4 tables
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

issue 1. aggregate function without group by issue 2. perhaps no matching record with inner join. So better provide some sample data and expected result. — Abhik Chakraborty
– Abhik Chakraborty, Commented May 13, 2015 at 17:04

Uueerdo · Accepted Answer · 2015-05-13 17:22:20Z

3

we_vehicles.veh_status cannot equal 1 and 0 simultaneously.

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.

edited May 13, 2015 at 17:22

answered May 13, 2015 at 17:04

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

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

5 Comments

Gammer Over a year ago

so what will be the solution if i want the count of 0 and 1's from the same we_vehicles.veh_status ?

xQbert Over a year ago

@uueerdo unless you believe this proof... pleacher.com/mp/mhumor/onezero2.html

xQbert Over a year ago

@shafee WHERE we_vehicles.veh_status in ('1','0') AND we_clients.client_id= 3 OR...

WHERE ((we_vehicles.veh_status='1') OR                     (we_vehicles.veh_status='0')) AND                     (we_clients.client_id= 3)

Gammer Over a year ago

@xQbert i want both 0 and 1 with separate as clause, How that can be done.

Uueerdo Over a year ago

@Shafee I am not positive what you mean, but see the edit above ...in a moment.

Shafizadeh · Accepted Answer · 2015-05-13 17:24:39Z

0

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)

answered May 13, 2015 at 17:24

Shafizadeh

10.5k16 gold badges60 silver badges95 bronze badges

Collectives™ on Stack Overflow

Multiple Select & INNER JOIN Multiple Where clause

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related