SQL Query based on multiple where conditions

Question

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!

Chris Allwein · Accepted Answer · 2017-08-04 15:54:32Z

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.

answered Aug 4, 2017 at 15:54

Chris Allwein

2,6081 gold badge21 silver badges24 bronze badges

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

Comments

Jacobm001 · Accepted Answer · 2017-08-04 15:53:19Z

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
    )

Gordon Linoff · Accepted Answer · 2017-08-04 15:55:08Z

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

answered Aug 4, 2017 at 15:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Query based on multiple where conditions

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related