1

I've got a script where checked in employee's can see the stock inventory from each other, linked with their personal stock location, so each checked in employee can see which items are in stock at different locations. However, I want the main stock (id of 1, which is not attached to an employee) to be showed always, but I can't get the query right because one of the where statements is clearly not correct:

`stock_locations`.`location_id` = 1 AND `workschedule`.`checkedIn` = 1 AND

Rememeber, the main stock is not linked to an employee, so it doesn't show up at the workschedule table. If I remove the first statement, It clearly shows up all the checked in employee's with their location, but that doesn't give me the main stock. If I remove the second statement, it only shows me the main stock.

How can I solve this issue within SQL? This is btw the full statement:

SELECT `item_quantities`.`item_id`, `stock_locations`.`location_name`, `item_quantities`.`quantity`, `people`.`first_name` FROM `item_quantities` JOIN `stock_locations` ON `item_quantities`.`location_id` = `stock_locations`.`location_id` JOIN `items` ON `item_quantities`.`item_id` = `items`.`item_id` LEFT JOIN `workschedule` ON `workschedule`.`linked_storage` = `stock_locations`.`location_id` LEFT JOIN `people` ON `workschedule`.`employee_id` = `people`.`person_id` WHERE `stock_locations`.`location_id` = 1 AND `workschedule`.`checkedIn` = 0 AND `items`.`unit_price` != 0 AND `items`.`deleted` = 0 AND `stock_locations`.`deleted` = 0 NULL

Thanks in advance!

Improve this question

3 Answers 3

Reset to default
2

Make it an OR statement inside of parens.

 (`stock_locations`.`location_id` = 1 OR `workschedule`.`checkedIn` = 1) AND

This will return all records that match either the main stock or the employee.

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

Comments

1

You need to use the OR operator. Clearly both things can't happen at the same time, so you need to specify each set of acceptable conditions.

SELECT `item_quantities`.`item_id`, `stock_locations`.`location_name`, `item_quantities`.`quantity`, `people`.`first_name` FROM `item_quantities` JOIN `stock_locations` ON `item_quantities`.`location_id` = `stock_locations`.`location_id` JOIN `items` ON `item_quantities`.`item_id` = `items`.`item_id` LEFT JOIN `workschedule` ON `workschedule`.`linked_storage` = `stock_locations`.`location_id` LEFT JOIN `people` ON `workschedule`.`employee_id` = `people`.`person_id` WHERE `stock_locations`.`location_id` = 1 OR ( AND `workschedule`.`checkedIn` = 1 AND `items`.`unit_price` != 0 AND `items`.`deleted` = 0 AND `stock_locations`.`deleted` = 0 NULL )
Improve this answer

Comments

0

You have LEFT JOIN, but your WHERE clause turns them into inner joins. Fixing that will probably fix your problem:

SELECT . . . FROM item_quantities iq JOIN stock_locations sl ON iq.`location_id` = sl.`location_id` JOIN items i ON iq.`item_id` = i.`item_id` LEFT JOIN workschedule ws ON ws.`linked_storage` = sl.`location_id` AND ws.`checkedIn` = 0 LEFT JOIN --------^ people p ON ws.`employee_id` = p.`person_id` WHERE sl.`location_id` = 1 AND i.`unit_price` != 0 AND i.`deleted` = 0 AND sl.`deleted` = 0
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.