Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

selecting distinct on 2 columns

Posted on 2014-01-08
9
Medium Priority
?
313 Views
Last Modified: 2014-02-05
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.
0
Comment
Question by:iceman19330
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39766121
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39766131
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)?
0
 

Author Comment

by:iceman19330
ID: 39766216
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39766224
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.
0
 

Author Comment

by:iceman19330
ID: 39766288
What would that above query look like?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39766344
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.
0
 

Author Comment

by:iceman19330
ID: 39766383
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')
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39769796
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39770716
@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

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question