inner join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

inner join

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-03-31 : 13:29:10

How can I inner join these select statements to make this view work?

create view [NJ.HO6.Lloyds]
as
SELECT distinct rate*100 [<1 mile from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'A'
and programs.policytype = 2 and programs.DCmax = 1 and ratedata.PCmax < 9 and programs.constructiontype != 3

SELECT distinct rate*100 [1-5 miles from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'A'
and programs.policytype = 2 and programs.DCmax = 5 and ratedata.PCmax < 9 and programs.constructiontype != 3

SELECT distinct rate*100 [>5 miles from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'A'
and programs.policytype = 2 and programs.DCmax = 9999 and ratedata.PCmax < 9 and programs.constructiontype != 3

---Group B
SELECT distinct rate*100 [All milage]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'B'
and programs.policytype = 2 and ratedata.PCmax < 9 and programs.constructiontype != 3

---Group C
SELECT distinct rate*100 [All milage]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'C'
and programs.policytype = 2 and ratedata.PCmax < 9 and programs.constructiontype != 3

---Group D

SELECT distinct rate*100 [<1 mile from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'D'
and programs.policytype = 2 and programs.DCmax = 1 and ratedata.PCmax < 9 and programs.constructiontype != 3

SELECT distinct rate*100 [1-5 miles from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'D'
and programs.policytype = 2 and programs.DCmax = 5 and ratedata.PCmax < 9 and programs.constructiontype != 3

SELECT distinct rate*100 [>5 miles from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'D'
and programs.policytype = 2 and programs.DCmax = 9999 and ratedata.PCmax < 9 and programs.constructiontype != 3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 13:36:17

How about just UNION?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-03-31 : 13:48:28

that is great thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 13:50:28

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-03-31 : 13:58:37

The only problem is when I us UNION it removes the header.....that I need when I export to excel...............how do I keep the header?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 14:00:18

What header are you referring to? Please show us a data example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-03-31 : 14:07:21

I am creating a view then exporting that view into excel. What I need to show up is [>5 miles from coast],[1-5 miles from coast],[>5 miles from coast] above each out put. let me know if that makes sense. Thanks for your help. the union is bluring them all together under the same header.

create view [**********]
as
SELECT distinct rate*100 [<1 mile from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'A'
and programs.policytype = 2 and programs.DCmax = 1 and ratedata.PCmax < 9 and programs.constructiontype != 3

union

SELECT distinct rate*100 [1-5 miles from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'A'
and programs.policytype = 2 and programs.DCmax = 5 and ratedata.PCmax < 9 and programs.constructiontype != 3

union

SELECT distinct rate*100 [>5 miles from coast]
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'NJ' and programs.companyid = 4 and ratedata.coverageid <5 and Ratedata.territory = 'A'
and programs.policytype = 2 and programs.DCmax = 9999 and ratedata.PCmax < 9 and programs.constructiontype != 3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 14:08:52

Well you'll need to include that in your result set then.

SELECT 'Header 1', ...
FROM ...

UNION

SELECT 'Header 2', ...

...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-03-31 : 14:13:13

can you give me an example with the data I sent.......not exacly sure how to follow.Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 14:15:10

You'll need to add an extra column to each query, and that will be your identifier of which query it came from. I don't have time to write it for you. My example provides enough information for you to be able to do it.

If this is not what you want, then you can't use a view.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources