Select a row AND associated rows
-
Trying to figure out the way to do this that isn't a least- as possible.
Schema is:
Item Table (primary key, details about the item)
Item-to-Item table (a one-to-many back to Item, because an Item can be related to many other items). For ease, I'll call it Master Items.If I want to select items, easy:
SELECT * FROM Items as i WHERE CRITERIA
If I want to select all the Masters of a matching item Item, easy:
SELECT mi.* FROM Items as i INNER JOIN ItemsToItems as i2 ON i.item_no = i2.child_item_no INNER JOIN Items as mi ON i2.master_item_no = mi.item_no WHERE CRITERIA
In the Search function of the system, if your criteria matches an Item that has Master Items, you should return the Master Items in the same data set. So I was thinking of this:
SELECT i.* FROM Items as i WHERE CRITERIA UNION ALL SELECT mi.* FROM Items as i INNER JOIN ItemsToItems as i2 ON i.item_no = i2.child_item_no INNER JOIN Items as mi ON i2.master_item_no = mi.item_no WHERE CRITERIA
The only problem is that CRITERIA is a fairly hefty set of criteria-- both in terms of SQL performance, AND in terms of actual code. I don't know if I want to maintain the criteria code twice-- makes it very hard to read, and is just begging for bugs to be introduced when someone forgets to copypasta new criteria into both WHERE clauses.
Any thoughts?
-
SELECT mi.* FROM Items as i INNER JOIN ItemsToItems as i2 ON i.item_no = i2.child_item_no INNER JOIN Items as mi ON i2.master_item_no = mi.item_no OR i2.child_item_no = mi.item_no WHERE CRITERIA
That would work wouldn't it?
Edit: or
mi.item_no IN (i2.master_item_no, i2.child_item_no)
-
@Jaloopa said in Select a row AND associated rows:
That would work wouldn't it?
It wouldn't find any items that don't have master items. (Sorry, forgot to mention that some items have 0 master items).
-
@Lorne-Kates that throws a spanner in the works.
Could you left join
ItemsToItems
and then have the master Items condition link back to the child items?SELECT mi.* FROM Items as i LEFT JOIN ItemsToItems as i2 ON i.item_no = i2.child_item_no INNER JOIN Items as mi ON mi.item_no IN (i2.master_item_no, i.item_no) WHERE CRITERIA
-
Would this work?
SELECT i.* FROM Items as i WHERE CRITERIA ), tmpMasterItems as ( SELECT mi.* FROM tmpItems as i INNER JOIN ItemsToItems as i2 ON i.item_no = i2.child_item_no INNER JOIN Items as mi ON i2.master_item_no = mi.item_no ) select * from tmpItems union select * from tmpMasterItems
-
@Karla said in Select a row AND associated rows:
Would this work?
Yeah, it looks like that would work, was thinking along that lines. Get all the child items based on CRITERIA. Then just pull in all the master item numbers. Then sort the results and do pagination.
Test suite (we have hidden categories, long story):
DECLARE @Items table (item_no int, name nvarchar(50)) DECLARE @ItemsToItems table (child_item_no int, master_item_no int) DECLARE @Category table (cat_id int, cat_name nvarchar(50), is_hidden bit) DECLARE @ItemToCat table (cat_id int, item_id int) INSERT INTO @Items (item_no, name) VALUES (1, 'No Master'), (2, 'X Two Master In Visible Category'), (3, 'Master of X Visible 1'), (4, 'Master of X Visible 2'), (5, 'Y One Master'), (6, 'Master of Y'), (7, 'No Master 2'), (8, 'X Two Master But I Am Hidden'), (9, 'Master of Hidden 1'), (10, 'Master of Hidden 2') INSERT INTO @ItemsToItems (child_item_no, master_item_no) VALUES (2, 3), (2, 4), (5, 6), (8, 9), (8, 10) INSERT INTO @Category (cat_id, cat_name, is_hidden) VALUES (1, 'Regular Category', 0), (2, 'Hidden Category', 1) INSERT INTO @ItemToCat (item_id, cat_id) VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 2), (9, 1), (10, 1) DECLARE @found TABLE (item_no int) INSERT INTO @found(item_no) SELECT item_no FROM @Items as i WHERE i.name like 'X Two%' -- This is the giant complex criteria -- Now, additionally, insert the Master items for any item in @found that is a child item INSERT INTO @found(item_no) SELECT mi.item_no FROM @found as i INNER JOIN @ItemsToItems as i2 ON i.item_no = i2.child_item_no INNER JOIN @Items as mi ON mi.item_no = i2.master_item_no -- Do the final select SELECT *, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY name) as [row_number] FROM @found as f INNER JOIN @Items as i ON f.item_no = i.item_no -- Simulate the "primary category" logic INNER JOIN @ItemToCat as ic ON i.item_no = ic.item_id INNER JOIN @Category as c ON ic.cat_id = c.cat_id -- Simulate the "primary category" logic WHERE c.is_hidden = 0
-
@Lorne-Kates Cool. Fuck you give me money.
-
@Karla said in Select a row AND associated rows:
@Lorne-Kates Cool. Fuck you give me money.
-
I didn't spend a lot of time thinking of a complete solution, but for the part where you say that you have a large bunch of conditions used in WHERE clauses in multiple queries, you should create a view with those conditions. That way, you only have to maintain it in one place.