Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

combining two resultsets into one

Posted on 2008-10-21
3
371 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

829 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