Taking two tables, 1-to-many, how can I filter down the many table and then join ALL the matches in the 1 table?
I apologize in advance for the length, the solution may well be trivial, just wanted to be as informative as I could.
The Tables
I have two tables of note: items and products, which is a 1 to many relationship. One item can have multiple product which are variations in color and material. Brand is an external category table that doesn't have to much part to play in this select statement.
So an item is, for example, a specific shoe, e.g. a "park avenue" shoe. A product is, for example, merlot burnished calfskin. And the brand would just be Allen Edmonds. Overall you get an Allen Edmonds park avenue shoe in merlot burnished calfskin.
Missing results in a "show almost everything" search
Someone decided to create a manual flag to associate the default color and material with a shoe, so that when you search, each type of shoe only shows up once, and when you click on it you can find it's other colors and materials. That's fine, but some shoes have no default material and color set. As an unfortunate result, those without at least one default set don't show up in the search.
Current Select Statement
Here is the current select, which filters out everything that doesn't have a default manually set:
SELECT DISTINCT items.ItemId
, items.Name
, items.BrandCategoryId
, items.CatalogPage
, items.GenderId
, items.PriceRetail
, items.PriceSell
, items.PriceHold
, items.Descr
, items.FlagStatus as ItemFlagStatus
, products.ImagetnURL
, products.FlagDefault
, products.ProductId
, products.Code as ProductCode
, products.Name as ProductName
, brands.Name as BrandName
FROM items
, products
, brands
WHERE items.ItemId = products.ItemId
AND items.BrandCode = brands.Code
AND items.FlagStatus != 'U'
AND products.FlagStatus != 'U'
AND products.FlagDefault = 'Y';
Not my choice of code, I suspect that the "DISTINCT" part of that statement is a bad idea, but I'm not exactly clear how to get rid of it.
The big problem I'm having right now, though is that final line
AND products.FlagDefault = 'Y'
that filters out everything that doesn't have at least one manual default set.
Edit: Here's an explain for the query:
+----+-------------+----------+--------+-----------------------------------------------------------+---------+---------+-------------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+-----------------------------------------------------------+---------+---------+-------------------------+-------+--------------------------------+
| 1 | SIMPLE | brands | ALL | NULL | NULL | NULL | NULL | 38 | Using temporary |
| 1 | SIMPLE | products | ALL | FlagStatus,FlagStatus_2,FlagStatus_3,flagstatusanddefault | NULL | NULL | NULL | 16329 | Using where; Using join buffer |
| 1 | SIMPLE | items | eq_ref | PRIMARY,BrandCode,FlagStatus,FlagStatus_2,FlagStatus_3 | PRIMARY | 4 | sherman.products.ItemId | 1 | Using where |
+----+-------------+----------+--------+-----------------------------------------------------------+---------+---------+-------------------------+-------+--------------------------------+
3 rows in set (0.01 sec)
And here is a describe on products, items, a开发者_JAVA百科nd brands:
mysql> describe products;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| ProductId | int(11) | NO | PRI | NULL | auto_increment |
| ItemId | int(11) | YES | | NULL | |
| Code | varchar(15) | YES | MUL | NULL | |
| Name | varchar(100) | YES | | NULL | |
| MaterialId | int(11) | YES | MUL | NULL | |
| PriceRetail | decimal(6,2) | YES | | NULL | |
| PriceSell | decimal(6,2) | YES | | NULL | |
| PriceHold | decimal(6,2) | YES | | NULL | |
| Cost | decimal(6,2) | YES | | NULL | |
| FlagDefault | char(1) | NO | | N | |
| FlagStatus | char(1) | YES | MUL | NULL | |
| ImagetnURL | varchar(50) | YES | | NULL | |
| ImagefsURL | varchar(50) | YES | | NULL | |
| ImagelsURL | varchar(50) | YES | | NULL | |
| DateStatus | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| DateCreated | timestamp | YES | | NULL | |
+-------------+--------------+------+-----+-------------------+-----------------------------+
16 rows in set (0.02 sec)
mysql> describe items
-> ;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| ItemId | int(11) | NO | PRI | NULL | auto_increment |
| Code | varchar(25) | YES | | NULL | |
| Name | varchar(100) | YES | MUL | NULL | |
| BrandCode | char(2) | YES | MUL | NULL | |
| CatalogPage | int(3) | YES | | NULL | |
| BrandCategoryId | int(11) | YES | | NULL | |
| TypeId | int(11) | YES | MUL | NULL | |
| StyleId | int(11) | YES | MUL | NULL | |
| GenderId | int(11) | YES | MUL | NULL | |
| PriceRetail | decimal(6,2) | YES | | NULL | |
| PriceSell | decimal(6,2) | YES | | NULL | |
| PriceHold | decimal(6,2) | YES | | NULL | |
| Cost | decimal(6,2) | YES | | NULL | |
| PriceNote | longtext | YES | | NULL | |
| FlagTaxable | char(1) | YES | | NULL | |
| FlagStatus | char(1) | YES | MUL | NULL | |
| FlagFeatured | char(1) | YES | | NULL | |
| MaintFlagStatus | char(1) | YES | | NULL | |
| Descr | longtext | YES | | NULL | |
| DescrNote | longtext | YES | | NULL | |
| ImagetnURL | varchar(50) | YES | | NULL | |
| ImagefsURL | varchar(50) | YES | | NULL | |
| ImagelsURL | varchar(50) | YES | | NULL | |
| DateCreated | date | NO | | 0000-00-00 | |
| DateStatus | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
25 rows in set (0.00 sec)
mysql> describe brands;
+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| BrandId | int(11) unsigned | NO | PRI | NULL | auto_increment |
| Code | varchar(6) | YES | | NULL | |
| PriceCode | varchar(4) | YES | | NULL | |
| Name | varchar(50) | YES | | NULL | |
| WebsiteURL | varchar(50) | YES | | NULL | |
| LogoURL | varchar(50) | YES | | NULL | |
| LogoTopURL | varchar(50) | YES | | NULL | |
| BrandURL | varchar(50) | YES | | NULL | |
| Descr | longtext | YES | | NULL | |
| DescrShort | longtext | YES | | NULL | |
| BeltDescr | longtext | YES | | NULL | |
| ImageURL | varchar(50) | YES | | NULL | |
| SaleImageURL | varchar(50) | YES | | NULL | |
| SaleCode | varchar(6) | YES | | NULL | |
| SaleDateBeg | date | YES | | NULL | |
| SaleDateEnd | date | YES | | NULL | |
| FlagStatus | char(1) | YES | | NULL | |
| DateStatus | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| DateCreated | timestamp | YES | | NULL | |
+--------------+------------------+------+-----+-------------------+-----------------------------+
19 rows in set (0.00 sec)
Possibilities that I am exploring
Subselect that grinds everything to a halt
I have a select statement that might, in a perfect, zero-execution-time world, work, by selecting the products the first product for each item, ordered by that flagdefault field, e.g.:
AND products.productid =
(select productid
from products
where products.itemid = items.itemid
AND products.FlagStatus != 'U'
order by FlagDefault='Y'
, itemid
limit 1);
replacing the check for a manually toggled default with an id that's only ordered by default, even if it's not toggled, and only takes the first result.
That statement grinds to a halt, and actually causes other use on the site to put mysql statements into deadlock (I suppose because reading of those tables is making them unavailable elsewhere).
Join that makes sure one table is distinct and not the next?
One way to get around it that might work is doing a:
select distinct ItemId from products ORDER BY default
And then just going further to obtain data for those itemids specifically, but I'm not sure how to make that happen in a single statement, not sure how to join select distincts well, and I expect that even making that select "distinct" in the first place isn't ideal, since it's selecting more than is needed to begin with and then cutting them down afterwards, but I don't have a better alternative for determining distinctness, really.
Advice?
In general, the select statement could use a lot of improvement, and specifically I could really use some advice on how to filter down the results for the most specific table and only -then- join upstream to the table that is the "one" in the one to many relationship.
Remove from WHERE:
AND products.FlagStatus != 'U'
AND products.FlagDefault = 'Y'
Add to FROM:
(
(SELECT ProductId
FROM products
WHERE FlagStatus != 'U' AND FlagDefault = 'Y')
UNION
(SELECT MIN(ProductId)
FROM products
WHERE FlagStatus != 'U'
GROUP BY ItemId
HAVING MAX(FlagDefault) != 'Y')
) AS defaults
Add to WHERE:
AND defaults.ProductId = products.ProductId
I'm using the term "non-hidden" for rows which have FlagStatus != 'U', since I'm assuming that's what the flag is for.
The first SELECT gives the ProductId of all default products, and the second one gives a ProductId for all the items without a default product. Hidden items are filtered by both, so if a default product has been hidden, a non-default product is displayed instead. When concatenated, you get a ProductId for every item that has some non-hidden product.
I'm assuming FlagDefault can only have values 'Y' or 'N'. The second query filters out the items having a default product by using MAX(FlagDefault), which works because 'Y' > 'N'.
By joining this to the products table of the original query, instead of filtering with FlagDefault, you should get the same results as the original, except you also get one row for every item which does not have a default product.
I've tested this query, but I haven't tested it with your original one since I don't have any meaningful data (read: your data) to test it against. This one works, so the combination should also work. For the same reason, I don't have any real numbers about performance - and I'm not an expert on query performance, either (more like a newbie). However, from what I've heard, subqueries in the WHERE clause are supposed to be bad for the performance, but in the FROM clause they should be okay. So, test it, I hope it's fast enough and fits the job.
As others mentioned, if you haven't got an index for the products.ItemId and BrandCode columns, you should definitely add them. You should also consider if requiring every item to have one hand-picked default would be okay, or maybe ditching the hand-picked defaults and always using random ones. Another thing to consider is if you really need the data from a product when there is no default - could you live without the image url, product name (use the item name?) and product code for those products?
Edit: One more possibility: You could change products.FlagDefault to items.DefaultProductId. That way it'd be easier to find out if an item has a default product and it enforces only one default product per item.
SELECT
items.ItemId,
items.Name,
items.BrandCategoryId,
items.CatalogPage,
items.GenderId,
items.PriceRetail,
items.PriceSell,
items.PriceHold,
items.Descr,
items.FlagStatus as ItemFlagStatus,
T3.ImagetnURL,
T3.FlagDefault,
T3.ProductId,
T3.Code as ProductCode,
T3.Name as ProductName,
brands.Name as BrandName
FROM items INNER JOIN
(
SELECT DISTINCT
T1.ItemId,
T1.ImagetnURL,
T1.FlagDefault
T1.ProductId,
T1.Code
T1.Name,
T1.FlagStatus
FROM
products AS T1 LEFT JOIN
products AS T2 ON T1.products.ProductId = T2.products.ProductId
AND T2.FlagDefault = 'Y'
) AS T3 ON items.ItemId = T3.ItemId INNER JOIN
brands ON items.BrandCode = brands.Code
WHERE items.FlagStatus != 'U'
AND T3.FlagStatus != 'U'
I'm not sure I understand fully the FlagStatus and the FlagDefault. For an item with no default, do all its products have products.FlagDefault != 'Y' ?
If yes, can you try this? It will (hopefully return all items with NULLs in the products fields for items with no default):
SELECT items.ItemId
, items.Name
, items.BrandCategoryId
, items.CatalogPage
, items.GenderId
, items.PriceRetail
, items.PriceSell
, items.PriceHold
, items.Descr
, items.FlagStatus as ItemFlagStatus
, products.ImagetnURL
, products.FlagDefault
, products.ProductId
, products.Code as ProductCode
, products.Name as ProductName
, brands.Name as BrandName
FROM items
LEFT JOIN
products
ON items.ItemId = products.ItemId
AND products.FlagDefault = 'Y'
JOIN
brands
ON items.BrandCode = brands.Code
;
The LEFT JOIN:
LEFT JOIN
products
ON items.ItemId = products.ItemId
AND products.FlagDefault = 'Y'
is equivalent to:
LEFT JOIN
( SELECT *
FROM products
WHERE products.FlagDefault = 'Y'
) AS p
ON items.ItemId = p.ItemId
So, it does, as you ask, "filters down the results for the most specific table and only -then- joins upstream to ..."
When using LEFT JOINs, the result can be different if you place the filtering conditions you have, at the ONclause, or later after all JOINS at the WHERE clause.
I am not sure about performance as you did not post table structure and sizes or explain plan, but how about a UNION between your first query (items with default products) and a query which fetches one product per item, only for items with no default product?
It's a bit long, but give it a shot - let me know if it gets you the correct data and how long it takes...
(SELECT items.ItemId
, items.Name
, items.BrandCategoryId
, items.CatalogPage
, items.GenderId
, items.PriceRetail
, items.PriceSell
, items.PriceHold
, items.Descr
, items.FlagStatus as ItemFlagStatus
, products.ImagetnURL
, products.FlagDefault
, products.ProductId
, products.Code as ProductCode
, products.Name as ProductName
, brands.Name as BrandName
FROM items
JOIN products ON items.ItemId = products.ItemId
JOIN brands ON items.BrandCode = brands.Code
WHERE items.FlagStatus != 'U'
AND products.FlagStatus != 'U'
AND products.FlagDefault = 'Y'
GROUP BY items.ItemId)
UNION
(SELECT items.ItemId
, items.Name
, items.BrandCategoryId
, items.CatalogPage
, items.GenderId
, items.PriceRetail
, items.PriceSell
, items.PriceHold
, items.Descr
, items.FlagStatus as ItemFlagStatus
, products.ImagetnURL
, products.FlagDefault
, products.ProductId
, products.Code as ProductCode
, products.Name as ProductName
, brands.Name as BrandName
FROM items
JOIN products ON items.ItemId = products.ItemId
JOIN brands ON items.BrandCode = brands.Code
WHERE items.FlagStatus != 'U'
AND products.FlagStatus != 'U'
AND products.FlagDefault != 'Y'
AND items.ItemId NOT IN
(SELECT DISTINCT itemId
FROM products
WHERE products.FlagDefault = 'Y')
GROUP BY items.ItemId)
加载中,请稍侯......
精彩评论