Merging Two Views - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Merging Two Views

Author

Topic

rbh123456789
Starting Member

15 Posts

Posted - 2010-03-25 : 10:42:44

Hey guys,

I am pretty new to SQL, as i have only been using it for a few months.

I am currently generating some reports using Crystal, and i have access to the PROD db (SQL 2005 Server)

Here is my issue:

I have two views inside my report, which are linked in the db expert. My goal is to combine the two, which i think might speed up the report.

Is it possible to use a UNION for the two views? Do you think it is possible to combine the two?

Thanks,

---------------------------------------------
--View number 1
---------------------------------------------

SELECT mtg.ID, pro.LANG1, mtg.STARTDATE, mtg.ENDDATE, SUITCASE.STAFF.ID AS initiatedByID, mtg.CASEPROCESSID, loc.LANG1 AS location,
mtg.LOCATION AS LocationID, pro.ID AS processid, cr.CTRLVALUE, cm.CASEID AS CaseNum, cm.NAME
FROM SUITCASE.MEETINGS AS mtg INNER JOIN
SUITCASE.CASEPROCESS AS cp ON mtg.CASEPROCESSID = cp.ID INNER JOIN
SUITCASE.PROCESSES AS pro ON cp.PROCESSID = pro.ID INNER JOIN
SUITCASE.LOCATIONS AS loc ON mtg.LOCATION = loc.ID INNER JOIN
SUITCASE.STAFF ON mtg.CHAIR = SUITCASE.STAFF.ID INNER JOIN
SUITCASE.CASEMASTER AS cm ON cm.ID = cp.CASEID LEFT OUTER JOIN
SUITCASE.CUSTOMRECORD AS cr ON mtg.ID = cr.KEY1 AND cr.SUBJECTID = 507 AND cr.CTRLID = 27672
WHERE (mtg.STATUS = 1) AND (pro.ID IN (4, 5, 6))

---------------------------------------------
--View number 2
---------------------------------------------

SELECT DISTINCT mtg2.ID, SUITCASE.STAFF.ID AS ViceChairID
FROM SUITCASE.MEETINGS AS mtg2 INNER JOIN
SUITCASE.MEETINGSTAFF AS mtgst ON mtg2.ID = mtgst.MEETINGID INNER JOIN
SUITCASE.STAFF ON mtgst.PROCESSSTAFFID = SUITCASE.STAFF.ID INNER JOIN
SUITCASE.STAFFROLE ON SUITCASE.STAFF.ID = SUITCASE.STAFFROLE.STAFFID INNER JOIN
SUITCASE.PROCESSROLE AS pr ON SUITCASE.STAFFROLE.ROLEID = pr.ID
WHERE (pr.ROLEID = 2)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 10:54:51

quote:
Originally posted by rbh123456789

Hey guys,

I am pretty new to SQL, as i have only been using it for a few months.

I am currently generating some reports using Crystal, and i have access to the PROD db (SQL 2005 Server)

No offense, but that's a recipe for disaster. If you're new to SQL, they you should ask for a copy of the PROD DB to be placed in a development environment. That way, if you unknowingly run a massive
query that hammers the b-jesus out of the server (and that's easy to do when you're new to SQL), the prod database won't come to a grinding halt.

quote:

Is it possible to use a UNION for the two views? Do you think it is possible to combine the two?

You can't UNION 2 queries that have different output columns.

A UNION combines the output rows of query 1 with those of query 2. If query 1 returns:

A B C
-----
1 2 3

And query 2 returns:

A B C
-----
4 5 6

Then query 1 UNION query 2 returns.

A B C
-----
1 2 3
4 5 6

I don't think that's what you're trying to do. You appear to be trying to add the columns of the 2 queries together, correct?

There are 10 types of people in the world, those that understand binary, and those that don't.

rbh123456789
Starting Member

15 Posts

Posted - 2010-03-25 : 11:07:32

Thanks. We do have a UAT db here, which i have access to.

I guess what your saying makes sense. I just want to have one view instead of having to link 2 of them.

quote:

There are 10 types of people in the world, those that understand binary, and those that don't.

What about the other 8?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 11:12:39

If they both return a different number of columns (which they do) and a different set of rows (which they probably do, because the WHERE clause is different for each query), then it looks like your stuck with accepting 2 queries. There's nothing wrong with that.

And stick to using the UAT database, it may save you a lot of explaining to the production team. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

rbh123456789
Starting Member

15 Posts

Posted - 2010-03-25 : 11:22:44

Thanks for your help man, thats all i wanted to know.
btw - i just realized that your line at the end was a joke.... dude, IT is nerdy enough without these types of bad puns in circulation, please stop spreading the stigma :)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 11:35:07

quote:
Originally posted by rbh123456789

Thanks for your help man, thats all i wanted to know.
btw - i just realized that your line at the end was a joke.... dude, IT is nerdy enough without these types of bad puns in circulation, please stop spreading the stigma :)

LOL. I was thinking of changing it soon anyway. :)

Glad I could help.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources