CODEWITHSUNDEEP
sqljoinviewinsert-into
Ashton Sheets
Ashton Sheets

Reputation: 513

How can I join multiple views into a single view

I have a query that has multiple select statements to generate several different sets of results. It's basically a set of 4 separate queries. What I need to do, though, is to have these separate results be joinable into a the 4th query.

From what I can tell, I will need to make the separate queries into individual views so that they're all existing within a single query but I'm not sure how to do this.

I'm using Microsoft SQL Server Management Studio 2010

Query 1 creates a result with the following columns:

 AgencyID | Agency | Address | City

Query 2 creates a result with these columns:

 AgentID | AgencyID | Name | Address

Query 3 creates a result with these columns:

 InsuredID | PolicyID | Name | Company

Query 4 creates a result with these columns:

 PolicyID | AgencyID | AgentID | InsuredID

Eeach query has a full set of SELECT, FROM, WHERE and ORDER BY/GROUP BY statements. I need to join the results of the 1st three queries (views?) into the view created by the 4th query based on the columns in there.

I know how to set the joins up once I have the views able to be referenced and joined. What I don't know how to do, however is get them into a format where that can happen.

I want to put all of that together into a single table view so that every row shows the policy info along with the corresponding agent data, insured data, and agency data. Is this an efficient or even possible way to do it?

INSERT INTO @Agency (AgencyID, Agency, Address, City)
SELECT
FROM
WHERE

INSERT INTO @Agent (AgentID, AgencyID, Name, Address)
SELECT
FROM
WHERE

INSERT INTO @Insured (InsuredID, PolicyID, Name, Company)
SELECT
FROM
WHERE

INSERT INTO @Policy (PolicyID, AgencyID, AgentID, InsuredID)
SELECT
FROM
WHERE

SELECT *
FROM @Policy p
JOIN @Agency ay ON ay.AgencyID = p.AgencyID
JOIN @Agent at ON at.AgentID = p.AgentID
JOIN @Insured i ON i.InsuredID = p.InsuredID

Is there a better way to do it or does this seem like a viable route to take? I left out the details of the select, where, and other stuff because they're not relevant to this specific question.

Upvotes: 1

Views: 3761

Answers (1)

mwigdahl
mwigdahl

Reputation: 16578

There are several different ways you could do this.

  • You can do it the way you've laid out.
  • You could use Common Table Expressions to pull all four precursor queries into your final query.
  • You could encapsulate the precursor queries in inline table-valued functions and then join them.
  • You could encapsulate the precursor queries into views and then join them.

There may be minor differences in query efficiency based on the method you choose; this will depend somewhat on the specifics of what you're doing in the precursor queries.

Upvotes: 2

Related Questions