selecting distinct on 2 columns

The issue is that on the site we list the sku with options for the queen (we dont have the king, full or twin info) and then I need to show the additional dimensions for King, Full and Twin so I pulled an excel sheet with all the manufactures information into a single table (16k rows) and am trying to filter the information (its down to 11k on a where statement selecting for sizes).  

What I would like to do is for each of the queen skus on the website find the corresponding dimension as well if there is a dimension for King, Twin and Full based on the parent sku field.

So output would look like:
2-ABC-10-20,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30,King,88,27,23.345,2-ABC-10
2-ABC-21-30,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53,Full,88,27,23.345,2-ABC-10
2-ABC-12-30,Twin,88,27,23.345,2-ABC-10

Open in new window


Additionally there would need to filter out these
2-ABC-10-20,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30,King,88,27,23.345,2-ABC-10
2-ABC-21-30,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53,Full,88,27,23.345,2-ABC-10
2-ABC-12-30,Twin,88,27,23.345,2-ABC-10
2-ABC-10-20-Conventional,Queen,85,26,23.345,2-ABC-10 --filter
2-ABC-20-30-Conventional,King,88,27,23.345,2-ABC-10 --filter
2-ABC-21-30-Conventional,Cali King,88,27,23.345,2-ABC-10 --filter
2-ABC-22-37-Conventional,37" King,88,27,23.345,2-ABC-10 --filter
2-ABC-23-53-Conventional,Full,88,27,23.345,2-ABC-10 --filter
2-ABC-12-30-Conventional,Twin,88,27,23.345,2-ABC-10 --filter

Open in new window

So here is my starting query
SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Parent SKU` 
FROM TABLE2
WHERE Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin'
AND `SKU with Options` not like '%Conventional'

Open in new window


My query brings in
2-ABC-10-20,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30,King,88,27,23.345,2-ABC-10
2-ABC-21-30,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53,Full,88,27,23.345,2-ABC-10
2-ABC-12-30,Twin,88,27,23.345,2-ABC-10
2-ABC-10-20-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-30-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-30-Conventional,Twin,88,27,23.345,2-ABC-10
2-ABC-10-11,Queen,85,26,23.345,2-ABC-10
2-ABC-20-11,King,88,27,23.345,2-ABC-10
2-ABC-21-11,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-11,37" King,88,27,23.345,2-ABC-10
2-ABC-23-11,Full,88,27,23.345,2-ABC-10
2-ABC-12-11,Twin,88,27,23.345,2-ABC-10
2-ABC-10-11-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-11-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-11-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-11-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-11-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-11-Conventional,Twin,88,27,23.345,2-ABC-10
2-ABC-10-15,Queen,85,26,23.345,2-ABC-10
2-ABC-20-15,King,88,27,23.345,2-ABC-10
2-ABC-21-15,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-15,37" King,88,27,23.345,2-ABC-10
2-ABC-23-15,Full,88,27,23.345,2-ABC-10
2-ABC-12-15,Twin,88,27,23.345,2-ABC-10
2-ABC-10-15-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-15-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-15-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-15-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-15-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-15-Conventional,Twin,88,27,23.345,2-ABC-10
2-ABC-10-21,Queen,85,26,23.345,2-ABC-10
2-ABC-20-21,King,88,27,23.345,2-ABC-10
2-ABC-21-21,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-21,37" King,88,27,23.345,2-ABC-10
2-ABC-23-21,Full,88,27,23.345,2-ABC-10
2-ABC-12-21,Twin,88,27,23.345,2-ABC-10
2-ABC-10-21-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-21-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-21-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-21-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-21-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-21-Conventional,Twin,88,27,23.345,2-ABC-10

Open in new window


There can be 50+ different skus with options for each parent sku due to size, finish and wood type, hence why I am having an issue just getting the single king, queen, full, twin plus the occasional cali king or 37" king/queen odd size.
iceman19330Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
First, try adjusting the WHERE to fix what I believe is your intent.

SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Parent SKU`
FROM TABLE2
WHERE Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin'
AND `SKU with Options` not like '%Conventional'


The highlighted syntax finds king, queen, or full size beds as well as twin size beds that do not contain the word Conventional at the end of SKU.  I suspect you want the below.

SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Parent SKU`
FROM TABLE2
WHERE `SKU with Options` not like '%Conventional'
AND (Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin')


Parentheses makes a difference here.

Let me know if that looks better.  My next thought, is you could use `SKU with Options` like a specific pattern that both excludes "conventional" and other options you may not want.
SharathData EngineerCommented:
The information you provided is not sufficient to provide any suggestion (at least to me). Explain with some simple examples, what data you have in table, what are you expecting any why (in plain English)?
iceman19330Author Commented:
Kevin that did remove those pesky "Conventional" skus.  :)

This is what I am left with -- this is a small set for example purposes
1-ABC-01-23	King	82	86	14.25	1-ABC-02
1-ABC-01-33	King	82	86	14.25	1-ABC-02
1-ABC-01-03	King	82	86	14.25	1-ABC-02
1-ABC-01-43	King	82	86	14.25	1-ABC-02
1-ABC-01-53	King	82	86	14.25	1-ABC-02
1-ABC-01-13	King	82	86	14.25	1-ABC-02
1-ABC-01-81	King	82	86	14.25	1-ABC-02
1-ABC-01-71	King	82	86	14.25	1-ABC-02
1-ABC-01-11	King	82	86	14.25	1-ABC-02
1-ABC-01-21	King	82	86	14.25	1-ABC-02
1-ABC-01-61	King	82	86	14.25	1-ABC-02
1-ABC-01-01	King	82	86	14.25	1-ABC-02
1-ABC-01-41	King	82	86	14.25	1-ABC-02
1-ABC-01-51	King	82	86	14.25	1-ABC-02
1-ABC-01-14	King	82	86	14.25	1-ABC-02
1-ABC-02-23	Queen	66	86	14.25	1-ABC-02
1-ABC-02-33	Queen	66	86	14.25	1-ABC-02
1-ABC-02-03	Queen	66	86	14.25	1-ABC-02
1-ABC-02-43	Queen	66	86	14.25	1-ABC-02
1-ABC-02-53	Queen	66	86	14.25	1-ABC-02
1-ABC-02-13	Queen	66	86	14.25	1-ABC-02
1-ABC-02-81	Queen	66	86	14.25	1-ABC-02
1-ABC-02-71	Queen	66	86	14.25	1-ABC-02
1-ABC-02-11	Queen	66	86	14.25	1-ABC-02
1-ABC-02-21	Queen	66	86	14.25	1-ABC-02
1-ABC-02-61	Queen	66	86	14.25	1-ABC-02
1-ABC-02-01	Queen	66	86	14.25	1-ABC-02
1-ABC-02-41	Queen	66	86	14.25	1-ABC-02
1-ABC-02-51	Queen	66	86	14.25	1-ABC-02
1-ABC-02-14	Queen	66	86	14.25	1-ABC-02
1-ABC-05-23	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-33	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-03	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-43	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-53	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-13	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-81	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-71	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-11	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-21	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-61	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-01	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-41	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-51	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-14	Cal King	78	90	14.25	1-ABC-02
1-ABC-11-23	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-33	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-03	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-43	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-53	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-13	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-81	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-71	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-11	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-21	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-61	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-01	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-41	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-51	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-14	King	82	87.5	40 or 44	1-ABC-12
1-ABC-12-23	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-33	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-03	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-43	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-53	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-13	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-81	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-71	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-11	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-21	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-61	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-01	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-41	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-51	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-14	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-15-23	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-33	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-03	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-43	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-53	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-13	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-81	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-71	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-11	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-21	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-61	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-01	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-41	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-51	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-14	Cal King	78	91.5	40 or 44	1-ABC-12

Open in new window


And this would be what I was looking for
1-ABC-01-23	King	82	86	14.25	1-ABC-02
1-ABC-02-23	Queen	66	86	14.25	1-ABC-02
1-ABC-05-23	Cal King	78	90	14.25	1-ABC-02
1-ABC-11-23	King	82	87.5	40 or 44	1-ABC-12
1-ABC-12-14	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-15-23	Cal King	78	91.5	40 or 44	1-ABC-12

Open in new window


I had thought about using an IN statement to load the 900+ skus from the product database which is only the Queen size (ex: 1-ABC-02-23) and then using that to look up the parent sku (1-ABC-02) to get the other sizes for that parent sku (1-ABC-02), however I am still left with the wondrous issue of all the finish options available for the 1 size -- 1-ABC-02 has 12 for King alone.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Kevin CrossChief Technology OfficerCommented:
Yes, it is difficult to discern what you are after, which is why I started with the obvious (at least perceived) logic error.

Although, in re-reading, the following would make sense.

- select Queen bed '2-ABC-10-11', which has parent SKU '2-ABC-10'
- find every SKU that also has parent SKU '2-ABC-10'
- filter to only SKUs with same ending option as the queen bed
2-ABC-10-11,Queen,85,26,23.345,2-ABC-10
2-ABC-20-11,King,88,27,23.345,2-ABC-10
2-ABC-21-11,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-11,37" King,88,27,23.345,2-ABC-10
2-ABC-23-11,Full,88,27,23.345,2-ABC-10
2-ABC-12-11,Twin,88,27,23.345,2-ABC-10

Open in new window


To get the above, you match on both parent SKU and the following substring:
SUBSTRING_INDEX(`SKU with Options`, '-', -1)

If this is not the desired result, then it is helpful to have a more plain breakdown of sample data, desired business logic (in English), and expected output as Sharath said.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iceman19330Author Commented:
What would that above query look like?
Kevin CrossChief Technology OfficerCommented:
How are you currently passing it the SKU relating to the Queen selected by user?  I will start with that query and show you an example from there once I get back online.
iceman19330Author Commented:
SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Grouping by fixture (*)`
FROM TABLE2
WHERE `SKU with Options` IN ('2-ABC-10-01','2-ABC-40-03','2-ABC-50-03','2-ABC-60-03','2-ABC-30-01','2-ABC-40-01','2-ABC-50-01','2-ABC-60-01','2-ABC-35-01','1-ABC-22-01','1-ABC-12-01')
AND `SKU with Options` not like '%Water Borne'
AND (Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin')
SharathData EngineerCommented:
On what basis, you are selecting few records when there are lot of matching reocrds?
for ex, the below Queen is in 1-ABC-12 but not selected in the final set.
1-ABC-12-51      Queen      66      87.5      40 or 44      1-ABC-12
Kevin CrossChief Technology OfficerCommented:
@iceman19330:

I guess I was expecting the application to pass in the ID of a specific queen bed, then you use the results of some initial query (e.g., to get parent SKU) as the filter for the query you are working on here.  Maybe I am misunderstanding the usage.

Given my understanding, I would think your query would look something like the below.
SELECT ab.`SKU with Options`, ab.Size, ab.`Product Width`
     , ab.`Product Length`, ab.`Product Height`, ab.`Parent SKU` 
FROM TABLE2 ab
JOIN (
    SELECT SUBSTRING_INDEX(`SKU with Options`, '-', -1) `Option`
         , `Parent SKU`
    FROM TABLE2
    WHERE `SKU with Options` = 'your id from initial selection'
) qb ON qb.`Parent SKU` = ab.`Parent SKU`
    AND qb.`Option` = SUBSTRING_INDEX(ab.`SKU with Options`, '-', -1)
WHERE ab.`SKU with Options` not like '%Conventional'
AND (
    ab.Size like '%King' 
    OR ab.Size like '%Queen' 
    OR ab.Size like 'Full' 
    OR ab.Size like 'Twin'
);

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.