SQLTeam.com | Weblogs | Forums

Query help

sql2014

#1

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%')


#2

WHERE PART_CODE Like 'THM-1369%'
OR PART_CODE Like 'THM-1352%'
OR PART_CODE Like 'THM-1307%'


#3

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


#4

Try moving "distinct" from select into count


#5
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%'))

#6

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..


#7

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.


#8

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


#9

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

#10

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.