troubleshooting Question

selecting distinct on 2 columns

Avatar of iceman19330
iceman19330 asked on
MySQL ServerSQL
9 Comments1 Solution348 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros