join 2 sql queries and concatenate the results?

Question

I have these 2 queries that almost give me the data I need:

SELECT  Products.ItemID, Products.ItemID + '-' + ModifierListItems.ItemID AS SizeItemID
FROM         ModifierLists INNER JOIN
                      ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                      ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                      Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Size'
ORDER BY Products.ItemID

SELECT  Products.ItemID, ModifierListItems.ItemID AS ColorItemID
FROM         ModifierLists INNER JOIN
                      ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                      ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                      Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Color'
ORDER BY Products.ItemID

The final output I am looking for is the SizeItemID concatenated with the ColorItemID joined on the ItemID.

Sample results of the 2 queries:

ItemID    SizeItemID
-------   ----------
A          A-S
A          A-M
B          B-M
B          B-L

ItemID     ColorItemID
-------    -----------
A            BLK
A            WHT
B            BLK
B            WHT
B            GRN

The results I am looking for would be the following:

FinalItemID
-----------
A-S-BLK
A-S-WHT
A-M-BLK
A-M-WHT
B-M-BLK
B-M-WHT
B-M-GRN
B-L-BLK
B-L-WHT
B-L-GRN

Answer 1

Well, the cleanest looking way would be to put each query into it's own view. It also might help you understand what you're doing a little better. So if we took the queries and created views:

CREATE VIEW v_ProductSize
AS
SELECT  Products.ItemID, Products.ItemID + '-' + ModifierListItems.ItemID AS SizeItemID
FROM         ModifierLists INNER JOIN
                      ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                      ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                      Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Size'
ORDER BY Products.ItemID


CREATE VIEW v_ProductColor
AS
SELECT  Products.ItemID, ModifierListItems.ItemID AS ColorItemID
FROM         ModifierLists INNER JOIN
                      ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                      ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                      Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Color'
ORDER BY Products.ItemID

Then you have two simple views you can use normally, right? So your query would be:

SELECT ps.SizeItemID + '-' + pc.ColorItemID FROM v_ProductSize ps JOIN v_ProductColor pc ON ps.ItemID=pc.ItemID

See how that works? You're just doing a normal join like you would any other table. Now, say you don't want to create views, or for some reason don't have permission. You can just skip the view part itself, and use subqueries. You're just replacing the reference to the view, with the query itself in parentheses.

So it would look like this:

SELECT ps.SizeItemID + '-' + pc.ColorItemID
FROM (
        SELECT  Products.ItemID, Products.ItemID + '-' + ModifierListItems.ItemID AS SizeItemID
         FROM         ModifierLists INNER JOIN
                               ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                               ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                               Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
         WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Size'
         ORDER BY Products.ItemID
) ps
JOIN 
     (    SELECT  Products.ItemID, ModifierListItems.ItemID AS ColorItemID
    FROM         ModifierLists INNER JOIN
                          ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                          ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                          Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
    WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Color'
    ORDER BY Products.ItemID
) pc ON ps.itemID=pc.ItemID

Now... all that said, and with my coffee finally kicking in, you can greatly simplify the query into one with a one line modification:

SELECT  Products.ItemID, Products.ItemID + '-' + colors.ItemID + '-' + sizes.ItemID AS SizeColorItemID
FROM         ModifierLists 
INNER JOIN  ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID 
INNER JOIN ModifierListItems sizes ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID 
                                    --Put modifier list name in join clause
                                    AND ModifierListName='Size'
INNER JOIN ModifierListItems colors ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID
                                    --same here, but for color
                                    AND ModifierListName = 'Color'
INNER JOIN Products ON ProductModifierLists.ItemID = Products.ItemID 
                    AND ProductModifierLists.ManufacturerID = Products.ManufacturerID               
WHERE     (Products.ManufacturerID = 262)  --Remove modifierlistitem here so you can use it in the join clauses
ORDER BY Products.ItemID

So since we're really pulling the same information in both original queries, with just a single different condition... we can move the modifierlistitem.name into the join clause, then join to that same table again with a different condition. Then we alias the table based on which condition we used (size, color.) That way you can just refer to the alias and pull the correct item id, and concatenate them all at once.

Answer 2

Try this:

You just need make both the queries as derived tables and join on ItemID

Select a.SizeItemID+'-'+b.ColorItemID as FinalItemID
FROM
    (SELECT  Products.ItemID, Products.ItemID + '-' + ModifierListItems.ItemID AS SizeItemID
    FROM         ModifierLists INNER JOIN
                          ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                          ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                          Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
    WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Size'
    )a
JOIN
    (SELECT  Products.ItemID, ModifierListItems.ItemID AS ColorItemID
    FROM         ModifierLists INNER JOIN
                          ProductModifierLists ON ModifierLists.ModifierListID = ProductModifierLists.ModifierListID INNER JOIN
                          ModifierListItems ON ModifierLists.ModifierListID = ModifierListItems.ModifierListID INNER JOIN
                          Products ON ProductModifierLists.ItemID = Products.ItemID AND ProductModifierLists.ManufacturerID = Products.ManufacturerID
    WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Color'
    )b
on a.ItemID=b.ItemID
ORDER BY  a.FinalItemID

Answer 3

You can take your two queries and join them together on ItemId to construct the final value.

In the following query, I also use aliases on your table names. Many people find meaningful aliases easier to read than long table names:

with tsize as (
     SELECT p.ItemID,
            p.ItemID + '-' + mli.ItemID AS SizeItemID
     FROM ModifierLists ml INNER JOIN
           ProductModifierLists pml 
           ON ml.ModifierListID = pml.ModifierListID INNER JOIN
           ModifierListItems mli
           ON ml.ModifierListID = mli.ModifierListID INNER JOIN
           Products p
           ON pml.ItemID = Products.ItemID AND
              pml.ManufacturerID = p.ManufacturerID
     WHERE (p.ManufacturerID = 262) AND ModifierListName='Size'
    ),
     tcolor as (
     SELECT  p.ItemID, mli.ItemID AS ColorItemID
     FROM ModifierLists moli INNER JOIN
          ProductModifierLists pml
          ON ml.ModifierListID = pml.ModifierListID INNER JOIN
          ModifierListItems mli
          ON ml.ModifierListID = mli.ModifierListID INNER JOIN
          Products p
          ON pml.ItemID = Products.ItemID AND
             pml.ManufacturerID = p.ManufacturerID
     WHERE     (Products.ManufacturerID = 262) AND ModifierListName='Color'
    )
select SizeItemID+'-'+ColorItemID
from tsize join tcolor
    on tsize.itemid = tcolor.ItemID