How to combine 4 queries into 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to combine 4 queries into 1

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2009-02-13 : 21:27:19

I know reading the title you must be thinking what is he talking about, I will try and explain this best I can. I am trying to combine 4 queries into 1. The first is my search query the fields highlighted in red are alaises in the other queries. below that you will see the queries which were created to produce those fields. I am baffled how to do this any help would nbe much appreciated

SELECT *
FROM dbo.installation, dbo.person, dbo.Company
WHERE (dbo.person.city = london) OR (dbo.installation.Fuel_ID = gas) OR (allowanceDistributed between 100000 AND 200000) OR (position between 1000 AND 2000) OR (myDivision between 5 AND 10) AND dbo.person.Company_ID = 1

Below find the other queries

SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE PermitIdentifier = MMColParam AND year1 between 2008 AND 2012

SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM dbo.compliance
WHERE PermitIdentifier = MMColParam AND dbo.compliance.year1 between 2008 AND 2012

SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1 and compliance.year1 = '2008'
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id [url][/url][url][/url][url][/url][url][/url]

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-13 : 23:32:32

using union all u can do this

for eg:

select a, b
from table1
union all
select c,d
from table2
union all
select e,null
from table3

Note : col a,col b,col c, col d, col e should be of hetergeneous datatype ( same datatype )

Ats
Starting Member

32 Posts

Posted - 2009-02-14 : 01:22:59

I have managed to play around with it a bit I now have 3 queries rather than 4 but i still get an error while I try and execute it.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-14 : 01:24:00

what error u got ??

and also post the query which u had tried...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 01:44:23

this is a quick & dirty solution. i know you can optimize this a lot. but for that i need more info on your reqmnt. also do you know that
FROM dbo.installation, dbo.person, dbo.Company gives you cartesian product of three tables. is it really what you want?


SELECT m.*
FROM
(SELECT relevant columns here....
FROM dbo.installation, dbo.person, dbo.Company)m
INNER JOIN
(
SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision 
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1 
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier 
WHERE compliance.Company_ID = 1 and compliance.year1 = '2008'
GROUP BY compliance.Company_ID, compliance.year1) AS a 
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs 
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier 
WHERE compliance.Company_ID = 1 
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id
)t
ON t.company_id=dbo.Company.company_id
CROSS JOIN
(
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE PermitIdentifier = MMColParam AND year1 between 2008 AND 2012
)ad
CROSS JOIN
(
SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM dbo.compliance
WHERE PermitIdentifier = MMColParam AND dbo.compliance.year1 between 2008 AND 2012
)p
WHERE (m.city = london) OR (m.Fuel_ID = gas) OR (ad.allowanceDistributed between 100000 AND 200000) OR (p.position between 1000 AND 2000) OR (t.myDivision between 5 AND 10) AND m.Company_ID = 1

Ats
Starting Member

32 Posts

Posted - 2009-02-14 : 10:50:50

Thanks visakh your soloution has been very help full but it still does not work. I will explain what I am trying to do I am creating a website and want to add an advanced search function the code would be simple if it did not have aggregate values in it. which i need to combine the queries.

I get the following error.

Msg 107, Level 16, State 3, Line 1
The column prefix 'Company' does not match with a table name or alias name used in the query.

I did not understand what you meant by cartesian product of 3 tables.

I did the code slightly just changing the WHERE permitidntifier to WHERE company_id.

I hope this makes sense

I will paste code below

SELECT m.*
FROM
(SELECT *
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1 and compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id
)t
ON t.company_id = Company.company_id
CROSS JOIN
(
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE compliance.Company_ID = 1 AND year1 between 2008 AND 2012
)ad
CROSS JOIN
(
SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.Company_ID = 1 AND compliance.year1 between 2008 AND 2012
)p
WHERE (m.city = london) OR (m.Fuel_ID = gas) OR (ad.allowanceDistributed between 100000 AND 200000) OR (p.position between 1000 AND 2000) OR (t.myDivision between 5 AND 10) AND m.Company_ID = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 10:56:40

see this to understand cartesian product

http://en.wikipedia.org/wiki/Cartesian_product

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 10:58:47

and your query should be this i guess


SELECT m.*
FROM
(SELECT *
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision 
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1 
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier 
WHERE compliance.Company_ID = 1 and compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a 
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs 
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier 
WHERE compliance.Company_ID = 1 
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id
)t
ON t.company_id = m.company_id
CROSS JOIN
(
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE compliance.Company_ID = 1 AND year1 between 2008 AND 2012
)ad
CROSS JOIN
(
SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.Company_ID = 1 AND compliance.year1 between 2008 AND 2012
)p
WHERE (m.city = london) OR (m.Fuel_ID = gas) OR (ad.allowanceDistributed between 100000 AND 200000) OR (p.position between 1000 AND 2000) OR (t.myDivision between 5 AND 10) AND m.Company_ID = 1

Ats
Starting Member

32 Posts

Posted - 2009-02-14 : 11:23:03

Thanks visakh i now get the following error Msg 8156, Level 16, State 1, Line 1
The column 'ID' was specified multiple times for 'm'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 11:41:19

replace * by actual column names inside first query . make it

SELECT col1,col2,....
FROM installation, person, Company

Ats
Starting Member

32 Posts

Posted - 2009-02-15 : 14:55:24

Hi Visakh, Im still getting the same error Msg 8156, Level 16, State 1, Line 1
The column 'name' was specified multiple times for 'm'.

SELECT m.*
FROM
(SELECT installation.name, installation.company_id, installation.name, installation.permitidentifier,installation.countrycode,fuel_id,person.name, person.zipcode, person.status_id, company.company_name
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1 and compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE compliance.Company_ID = 1 AND year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.Company_ID = 1 AND compliance.year1 between 2008 AND 2012)p
WHERE (m.city = london) OR (m.Fuel_ID = gas) OR (ad.allowanceDistributed between 100000 AND 200000) OR (p.position between 1000 AND 2000) OR (t.myDivision between 5 AND 10) AND m.Company_ID = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 21:29:52

quote:
Originally posted by Ats

Hi Visakh, Im still getting the same error Msg 8156, Level 16, State 1, Line 1
The column 'name' was specified multiple times for 'm'.

SELECT m.*
FROM
(SELECT installation.name, installation.company_id, ~~installation.name~~, installation.permitidentifier,installation.countrycode,fuel_id,person.name, person.zipcode, person.status_id, company.company_name
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1 and compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.Company_ID = 1
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE compliance.Company_ID = 1 AND year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.Company_ID = 1 AND compliance.year1 between 2008 AND 2012)p
WHERE (m.city = london) OR (m.Fuel_ID = gas) OR (ad.allowanceDistributed between 100000 AND 200000) OR (p.position between 1000 AND 2000) OR (t.myDivision between 5 AND 10) AND m.Company_ID = 1

you're reapeating same column there.remove one of them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources