JDEE8297
asked on
combining two resultsets into one
I am trying to combine the result of two queries into one result set, and return that back to my web page.
Table a
fields (id, invtid, price, theme)
table b
id, invtid, theme)
I want to pull back all of the results from a where the theme is 'XXX"
and then incorporate the results of table b into the resultset where the contents of B equal 'XXX' for theme.
And I have tried a number of ways of trying to do this as you can see from the code sample down below, right now I am getting a syntax error right after I try the left outer join on B, but even if I get this working I don't think it will work the way I want it to work.
So if anyone has any ideas on how to do this I would appreciate your help. Thanks.
Table a
fields (id, invtid, price, theme)
table b
id, invtid, theme)
I want to pull back all of the results from a where the theme is 'XXX"
and then incorporate the results of table b into the resultset where the contents of B equal 'XXX' for theme.
And I have tried a number of ways of trying to do this as you can see from the code sample down below, right now I am getting a syntax error right after I try the left outer join on B, but even if I get this working I don't think it will work the way I want it to work.
So if anyone has any ideas on how to do this I would appreciate your help. Thanks.
prd.clearanceprice,
tp.recordid,
sic.sitecategories_id,
dbo.Func_Product_GetMenuParentId(sic.sitecategories_id),
dbo.func_priceproduct_getprice_absolute(prd.invtid, prd.[SellInMult]),
prd.parentinvtid,
sic.seo_url,
sic.template_image,
sic.template_colour,
sic.template_default,
sic.layer1_src) A
LEFT OUTER JOIN
{SELECT tp.invtid,
prd.descr,
prd.clearance,
prd.clearanceprice,
tp.recordid,
dbo.func_priceproduct_getprice_absolute(prd.invtid, prd.[SellInMult]) AS price,
prd.parentinvtid,
sic.seo_url,
sic.sitecategories_id AS sitecategory_id,
dbo.Func_Product_GetMenuParentId(sic.sitecategories_id) AS menu_parent,
sic.template_image,
sic.template_colour,
sic.template_default,
sic.layer1_src,
sicc.extcode
FROM themesproducts tp WITH ( NOLOCK )
INNER JOIN products prd ON prd.invtid = tp.invtid
LEFT JOIN skuitemcharacter sicc ON sicc.extcode = 'GIN'
LEFT JOIN SiteItemCategory sic ON sic.invtid = tp.invtid
WHERE tp.themeid_fk = 144 --and
-- sic.is_primary=1
AND ( prd.parentinvtid IS NOT NULL
AND LEN(prd.parentinvtid) = 0
)
GROUP BY tp.invtid,
prd.descr,
prd.clearance,
prd.clearanceprice,
tp.recordid,
sic.sitecategories_id,
dbo.Func_Product_GetMenuParentId(sic.sitecategories_id),
dbo.func_priceproduct_getprice_absolute(prd.invtid, prd.[SellInMult]),
prd.parentinvtid,
sic.seo_url,
sic.template_image,
sic.template_colour,
sic.template_default,
sic.layer1_src,
sicc.extcode) B
ON a.invtid = b.invtid --b.extcode = 'GIN'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you for your help.
ASKER