Solved

combining two resultsets into one

Posted on 2008-10-21
3
368 Views
Last Modified: 2010-04-21
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.
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'

Open in new window

0
Comment
Question by:JDEE8297
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
Comment Utility
You have no beginning select or from statements.  Your derived table has a beginning { instead of ( and has no ending ).

I'll give you a general fixup here, but you'll need to ensure your table names are correct.

select {What} -- where {WHAT} represents the fields you want to select
from Where a
join Where2 b
on a.Field=b.Field -- where field
join (select a.id,b.col1,sum(b.something) sumsomething from Tablea a join tableb b on a.id=b.id group by a.id,b.col1) c
-- ^^ this is a derived table of a.id and b.col1 that is now referred to as c
on a.id=c.id

where a.SomeField = 'SomeValue'
select * from 

(select 

        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

from What_Table_Do_You_Want_Your_First_RecordsToComeFrom prd

join WhatOtherTable tp

on prd.what=tp.what

join WhatOtherTableslc SLC

on tp.What=slc.what
 

) 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'

Open in new window

0
 

Author Comment

by:JDEE8297
Comment Utility
I don't have access to my work machine, but something must have gone wrong when I cut and paste this into here, but thanks for the quick reply.
0
 

Author Closing Comment

by:JDEE8297
Comment Utility
thank you for your help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now