Select a row AND associated rows


  • Trolleybus Mechanic

    Trying to figure out the way to do this that isn't a least-:wtf: 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?


  • kills Dumbledore

    @Lorne-Kates

    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)


  • Trolleybus Mechanic

    @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).


  • kills Dumbledore

    @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
    

  • Trolleybus Mechanic

    @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.


  • Trolleybus Mechanic

    @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.


Log in to reply