Solved

combining two resultsets into one

Posted on 2008-10-21
3
369 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
ID: 22772737
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
ID: 22772990
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
ID: 31508570
thank you for your help.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

896 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

15 Experts available now in Live!

Get 1:1 Help Now