question

rajeshwari2606 avatar image
rajeshwari2606 asked

Need help with the query below.

There is a issue with an existing process so I am trying to fix that.Can you please help me with the query for the below scenario Current scenario- Retrieving only those items which doesn't have entry in the table. Condition: and not exists(select 1 from item_loc where item=im.item) where im is a reference table Expected scenario: Retrieve all those items which doesnt have a entry in the table --(already done in current scenario) and also those items which have a single entry in the table for location 2001. Item_loc table contains item and location data and reference table is item table which has list of all items with its creation date.
tags
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tatyana avatar image
Tatyana answered
I assume that when you say "a single entry in the table" you mean Item_loc table, and "2001" is stored in the "location" field of this table. Then change your WHERE clause: ... and (not exists(select 1 from item_loc where item=im.item) OR (SELECT COUNT(*) FROM Item_loc WHERE location = '2001') = 1)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.