If One Query Field Includes X, Include All of X in Another Field

[zgadson]

I'm sure this is entirely possible, but being relatively new to SQL I have absolutely no clue where to even start.

Basically I have warehouse storage for several different clients and a spreadsheet for each client showing where in the warehouse each client's products are located. So rather than having a spreadsheet thousands of lines long to list EVERY warehouse location (Aisle, Slot, Row, etc.) and using only a fraction of the sheet for locations where my client's product is stored, I'd like to include only full aisles where my client's product is stored.

So for example, if "Client X" has any product at all in aisles A, B, and F, the query should include all of those aisles. This way my sheet is only a few hundred lines long and limited to relevant aisles.

My query is messy (pulling data from AS400 which is less than ideal), but the relevant fields are AISLE and STNAME. So where STNAME = "Client X", if AISLE = "A", the query should show all of A whether STNAME is Client X or not.

SELECT

CONCAT(CONCAT(LTRIM(RTRIM(MAAISL)), '_'),LTRIM(RTRIM(MASLOT))) AS LOCATION,

LTRIM(MAAISL) AS "AISLE",

RTRIM(LTRIM(STNAME)) AS "STR.NAME",
LTRIM(ITDSC1) AS "ITM.DESC",

SUM(CASE WHEN ITUNQ3 = ' ' THEN CEIL(QTY/ITUNQ2) ELSE CEIL((QTY/ITUNQ2)/ITUNQ3) END) AS "PLT.QTY"

FROM WHSECLIENT
WHERE STNAME= 'CLIENT X'
ORDER BY MAAISL ASC

Would be incredibly grateful for any help! Thanks!

 

[smozgur]

If I understand your question correctly, you want to return ALL data from the AISLEs that particular client has any product in it. So, if CLIENT X has products in A, B, then you want all data (including all products from other clients as well) to be returned from A, B. Sounds right?

I can't test the SQL below. However, according to my assumption above, what you need is a sub query that returns the AISLE letters and using IN operator to query those AISLEs only.

SELECT
    CONCAT(CONCAT(LTRIM(RTRIM(MAAISL)), '_'),LTRIM(RTRIM(MASLOT))) AS LOCATION,
    LTRIM(MAAISL) AS "AISLE",
    RTRIM(LTRIM(STNAME)) AS "STR.NAME",
    LTRIM(ITDSC1) AS "ITM.DESC",
    SUM(CASE WHEN ITUNQ3 = ' ' THEN CEIL(QTY/ITUNQ2) ELSE CEIL((QTY/ITUNQ2)/ITUNQ3) END) AS "PLT.QTY"
FROM WHSECLIENT
WHERE MAAISL IN (
    SELECT MAAISL
    FROM WHSECLIENT 
    WHERE STNAME = "CLIENT X"
    GROUP BY MAAISL
)
ORDER BY MAAISL ASC

 

[zgadson]

If I understand your question correctly, you want to return ALL data from the AISLEs that particular client has any product in it. So, if CLIENT X has products in A, B, then you want all data (including all products from other clients as well) to be returned from A, B. Sounds right?

I can't test the SQL below. However, according to my assumption above, what you need is a sub query that returns the AISLE letters and using IN operator to query those AISLEs only.

SELECT
    CONCAT(CONCAT(LTRIM(RTRIM(MAAISL)), '_'),LTRIM(RTRIM(MASLOT))) AS LOCATION,
    LTRIM(MAAISL) AS "AISLE",
    RTRIM(LTRIM(STNAME)) AS "STR.NAME",
    LTRIM(ITDSC1) AS "ITM.DESC",
    SUM(CASE WHEN ITUNQ3 = ' ' THEN CEIL(QTY/ITUNQ2) ELSE CEIL((QTY/ITUNQ2)/ITUNQ3) END) AS "PLT.QTY"
FROM WHSECLIENT
WHERE MAAISL IN (
    SELECT MAAISL
    FROM WHSECLIENT
    WHERE STNAME = "CLIENT X"
    GROUP BY MAAISL
)
ORDER BY MAAISL ASC

Yes! Yes! Yes! Thank you so much! Exactly what I was looking for!

 

[smozgur]

Yes! Yes! Yes! Thank you so much! Exactly what I was looking for!

Wonderful! Glad to hear it helps.