hi everyone,
I have a query where i need to find out orders that have 3 diffident Part-codes. so it has to have all three part codes. this is a sample of the table:
THM-1264 181858 HOG A
THM-1269 181858 HOG A
THM-1225 181858 HOG A
THM-1225 181858 HOG A
THM-1225 181858 HOG A
THM-1225 181858 HOG A
WLB-2001 181820 CWS A
WLB-2001 181820 CWS A
WLB-2001 181820 CWS A
THM-1536 181820 CWS A
THM-2054 181820 CWS A
THM-2054 181820 CWS A
THM-2054 181820 CWS A
THM-2054 181820 CWS A
THM-1536 181820 CWS A
THM-1536 181820 CWS A...
query that doesn't do want i want:
SELECT PART_CODE, Q.QUOTE_NUM, Q.QUOTE_NUM_PREF, Q.QUOTE_NUM_SUFF
FROM QUOTE Q
JOIN QUOTE_ITEM QI ON QI.QUOTE_ID = Q.QUOTE_ID
JOIN BOM_PIECE BP ON BP.QUOTE_ITEM_ID = QI.QUOTE_ITEM_ID
WHERE (PART_CODE like '%THM-1369%') and
(PART_CODE like '%THM-1352%') and
(PART_CODE like '%THM-1307%')
WHERE PART_CODE Like 'THM-1369%'
OR PART_CODE Like 'THM-1352%'
OR PART_CODE Like 'THM-1307%'
Thanks Jeff. I get nothing in return..
i have also try this but not working like i want to because still bring record where only one Partcode there and I need only order that have all 3 parts..ahgg
SELECT DISTINCT Q.QUOTE_NUM, Q.QUOTE_NUM_PREF, Q.QUOTE_NUM_SUFF
FROM QUOTE Q
left JOIN QUOTE_ITEM QI ON QI.QUOTE_ID = Q.QUOTE_ID
left JOIN BOM_PIECE BP ON BP.QUOTE_ITEM_ID = QI.QUOTE_ITEM_ID
WHERE PART_CODE in ('THM-1369', 'THM-1352', 'THM-1307')
Group by Q.QUOTE_NUM, Q.QUOTE_NUM_PREF, Q.QUOTE_NUM_SUFF
having count (PART_CODE) > 3
Try moving "distinct" from select into count
SELECT PART_CODE, Q.QUOTE_NUM, Q.QUOTE_NUM_PREF, Q.QUOTE_NUM_SUFF
FROM QUOTE Q
JOIN QUOTE_ITEM QI ON QI.QUOTE_ID = Q.QUOTE_ID
JOIN BOM_PIECE BP ON BP.QUOTE_ITEM_ID = QI.QUOTE_ITEM_ID
INNER JOIN (
SELECT Q.QUOTE_NUM
FROM QUOTE Q
JOIN QUOTE_ITEM QI ON QI.QUOTE_ID = Q.QUOTE_ID
JOIN BOM_PIECE BP ON BP.QUOTE_ITEM_ID = QI.QUOTE_ITEM_ID
WHERE (PART_CODE like '%THM-1307%') or
(PART_CODE like '%THM-1352%') or
(PART_CODE like '%THM-1369%')
GROUP BY Q.QUOTE_NUM
HAVING
MAX(CASE WHEN PART_CODE like '%THM-1307%' then 1 else 0 end) = 1 AND
MAX(CASE WHEN PART_CODE like '%THM-1352%' then 1 else 0 end) = 1 AND
MAX(CASE WHEN PART_CODE like '%THM-1369%' then 1 else 0 end) = 1
) AS part_match ON part_match.QUOTE_NUM = Q.QUOTE_NUM AND
((PART_CODE like '%THM-1307%') or
(PART_CODE like '%THM-1352%') or
(PART_CODE like '%THM-1369%'))
Scott tanks
ran the query but it only gave me one order with all three parts but on the table I have more order with all those 3 parts..
I'm not sure what you mean. You talk about "order", but no column is named "order" or "order_number", or similar. So I just guessed that QUOTE_NUM was maybe equivalent to "order", but that may not be correct, it was just a guess.
I am sorry yes that is correct QuoteNum so i need All quotes that have all those PartCodes.. when i run this query I get the last quote that do have all those three part..But I need all the quotes that have those 3 parts
Run the inner query by itself and see how many quote numbers it returns. Also, you never indicate which table the "PART_CODE" column comes from, and that makes it more difficult too. You have to remember that we know NOTHING about your tables and the alias/custom names, such as "orders", that you use for different columns.
SELECT Q.QUOTE_NUM
FROM QUOTE Q
JOIN QUOTE_ITEM QI ON QI.QUOTE_ID = Q.QUOTE_ID
JOIN BOM_PIECE BP ON BP.QUOTE_ITEM_ID = QI.QUOTE_ITEM_ID
WHERE (PART_CODE like '%THM-1307%') or
(PART_CODE like '%THM-1352%') or
(PART_CODE like '%THM-1369%')
GROUP BY Q.QUOTE_NUM
HAVING
MAX(CASE WHEN PART_CODE like '%THM-1307%' then 1 else 0 end) = 1 AND
MAX(CASE WHEN PART_CODE like '%THM-1352%' then 1 else 0 end) = 1 AND
MAX(CASE WHEN PART_CODE like '%THM-1369%' then 1 else 0 end) = 1
This isn't clear on what your expectations are - can you provide sample data and desired results? Do you want all the quotes that have only those 3 parts - or any quote that has one or more of those parts - or a quote that has all 3 of those parts and any other parts?
Which table contains the PART_CODE? I am assuming the QUOTE_ITEM table...if so, then query that table to identify all quote items that have those 3 part codes:
SELECT QUOTE_ITEM, PART_CODE
FROM QUOTE_ITEM qi
WHERE PART_CODE Like 'THM-1369%'
OR PART_CODE Like 'THM-1352%'
OR PART_CODE Like 'THM-1307%'
GROUP BY QUOTE_ITEM
If you want the quote items that have one or more of the above - add:
HAVING count(*) > 0
If you want the quote items that have 3 or more items - any combination of those 3 parts add:
HAVING count(*) > 2
If you want the quote items to have of of each part code add:
HAVING
MAX(CASE WHEN PART_CODE like '%THM-1307%' then 1 else 0 end) = 1 AND
MAX(CASE WHEN PART_CODE like '%THM-1352%' then 1 else 0 end) = 1 AND
MAX(CASE WHEN PART_CODE like '%THM-1369%' then 1 else 0 end) = 1
Once you have identified the quote items to be included - you can then JOIN that to the actual quote - putting the above query in either a CTE or derived table.
Using a CTE:
WITH quoteItems
AS (
SELECT QI.QUOTE_ITEM
, PART_CODE
FROM QUOTE_ITEM QI
WHERE QI.PART_CODE LIKE 'THM-1369%'
OR QI.PART_CODE LIKE 'THM-1352%'
OR QI.PART_CODE LIKE 'THM-1307%'
GROUP BY QI.QUOTE_ITEM
HAVING MAX(CASE WHEN QI.PART_CODE LIKE 'THM-1307%' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN QI.PART_CODE LIKE 'THM-1352%' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN QI.PART_CODE LIKE 'THM-1369%' THEN 1 ELSE 0 END) = 1
)
SELECT QI.PART_CODE
, Q.QUOTE_NUM
, Q.QUOTE_NUM_PREF
, Q.QUOTE_NUM_SUFF
FROM QUOTE Q
JOIN quoteItems QI ON QI.QUOTE_ITEM = Q.QUOTE_ITEM
JOIN BOM_PIECE BP ON BP.QUOTE_ITEM = QI.QUOTE_ITEM
i am assuming that the part code begins with the specified values and is not actually in the middle of the part code. If the part code is something like {value}THM-nnnn{more values} then you do need the leading wildcard - but if not then removing the leading wildcard could improve performance if there is an index on the part code.
If this isn't what you are looking for - then you need to provide sample data (create table statements and insert statements) with expected results.