Combine 2 queries into 1 - 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 2000 Forums

Transact-SQL (2000)

Combine 2 queries into 1

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-02 : 06:02:39

Is it possible to "merge" the two queries listed below into 1?

Views

CREATE VIEW dbo.vwCreditsAchieved
AS
SELECT     dbo.srs_sce.sce_stuc, SUM(dbo.ins_smr.smr_mcrd) AS cred_acvd, dbo.ins_smr.smr_rslt
FROM         dbo.ins_smr INNER JOIN
                      dbo.srs_sce ON dbo.ins_smr.spr_code = dbo.srs_sce.sce_scjc
GROUP BY dbo.srs_sce.sce_stuc, dbo.ins_smr.smr_rslt
HAVING      (dbo.ins_smr.smr_rslt = 'P')


CREATE VIEW dbo.vwCreditsBooked
AS
SELECT     dbo.srs_sce.sce_stuc, dbo.srs_sce.sce_stac, dbo.srs_sce.sce_pgsc, SUM(dbo.cam_smo.smo_mcrd) AS cred_bkd
FROM         dbo.cam_smo INNER JOIN
                      dbo.srs_sce ON dbo.cam_smo.spr_code = dbo.srs_sce.sce_scjc
GROUP BY dbo.srs_sce.sce_stuc, dbo.srs_sce.sce_stac, dbo.srs_sce.sce_pgsc

Create Table and Insert Sample Data Code

CREATE TABLE [dbo].[srs_sce] (
	[sce_scjc] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[sce_stuc] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[sce_stac] [char] (6) COLLATE Latin1_General_CI_AS NULL ,
	[sce_pgsc] [char] (3) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ins_smr] (
	[spr_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[smr_rslt] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[smr_mcrd] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cam_smo] (
	[spr_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[smo_mcrd] [int] NULL
) ON [PRIMARY]
GO


INSERT INTO [dbo].[srs_sce] ([sce_scjc], [sce_stuc], [sce_stac], [sce_pgsc]) VALUES ('0122803/1', '0122803', 'C', NULL)

INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 50)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 50)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)
INSERT INTO [dbo].[cam_smo] ([spr_code], [smo_mcrd]) VALUES ('0122803/1', 100)

INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 50)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 50)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)
INSERT INTO [dbo].[ins_smr] ([spr_code], [smr_rslt], [smr_mcrd]) VALUES ('0122803/1', 'P', 100)

My desired output would be something like;

sce_stuc sce_stac sce_pgsc cred_bkd cred_acvd
--------------------------------------------------
0122803 C <NULL> 1600 800

Thanks for taking the time to look (and reply

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 06:11:35

[code]
SELECT
b.sce_stuc,
b.sce_stac,
b.sce_pgsc,
SUM(a.smo_mcrd) AS cred_bkd,
c.cred_acvd
FROM
cam_smo as a
INNER JOIN srs_sce as b
ON a.spr_code = b.sce_scjc
inner join (SELECT
b.sce_stuc,
SUM(a.smr_mcrd) AS cred_acvd,
a.smr_rslt
FROM
ins_smr as a
INNER JOIN srs_sce as b
ON a.spr_code = b.sce_scjc
GROUP BY b.sce_stuc, a.smr_rslt
HAVING (a.smr_rslt = 'P')) as c
on b.sce_stuc = c.sce_stuc
GROUP BY b.sce_stuc, b.sce_stac, b.sce_pgsc, c.cred_acvd
[/code]

??!

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-02 : 09:17:21

Thanks very much, that worked a treat. I can never get this stuff figured out in my head

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-02 : 10:07:57

That's a GREAT Post!

And since the views already exists


CREATE VIEW myView99
AS
   SELECT sce_stuc, sce_stac, sce_pgsc, cred_bkd, Null AS smr_rslt
     FROM vwCreditsBooked
UNION ALL
   SELECT sce_stuc, Null AS sce_Stac, Null AS sce_pgsc, cred_acvd, smr_rslt
     FROM vwCreditsBooked
GO

Brett

8-)

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 10:47:07

quote:

Originally posted by X002548

That's a GREAT Post!

And since the views already exists


CREATE VIEW myView99
AS
   SELECT sce_stuc, sce_stac, sce_pgsc, cred_bkd, Null AS smr_rslt
     FROM vwCreditsBooked
UNION ALL
   SELECT sce_stuc, Null AS sce_Stac, Null AS sce_pgsc, cred_acvd, smr_rslt
     FROM vwCreditsBooked
GO

Brett

8-)

UNION will not produce the output he desires.

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-02 : 10:56:19

Yeah, I'm trying to get away from using too many views and merging it all into one SP. Here's my next challenge, same as last one. How do you learn how to do this? I had a look at your answer to my first request but couldn't get my head around it

Views

CREATE VIEW dbo.vwMCA
AS
SELECT     mod_code, SUM(smr_mcrd) AS sum_smr_mcrd
FROM         dbo.ins_smr
GROUP BY mod_code

CREATE VIEW dbo.vwMCB
AS
SELECT     mod_code, SUM(smo_mcrd) AS sum_smo_mcrd
FROM         dbo.cam_smo
GROUP BY mod_code

Create Table and Insert Sample Data

CREATE TABLE [dbo].[ins_smr] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[smr_mcrd] [int] NULL

) ON [PRIMARY]
GO


CREATE TABLE [dbo].[cam_smo] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[smo_mcrd] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[cam_smo] ([mod_code], [smo_mcrd]) VALUES ('D01011', 100)


INSERT INTO [dbo].[ins_smr] ([mod_code], [smr_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[ins_smr] ([mod_code], [smr_mcrd]) VALUES ('D01011', 100)
INSERT INTO [dbo].[ins_smr] ([mod_code], [smr_mcrd]) VALUES ('D01011', 100)

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 11:08:23

Hi mparter,

If you get in to the habit of thinking of every SELECT statement as a potential derived table then you're half way there. I didn't really have to do much to be honest. I took out the SELECT part from each view and ran them separately to see the results they produced. I then decided that one of the select should act as my derived table (random choice on my part) and joined it with the other table on the sce_stuc field.

With practice you will get better and better!

By the way, I think you should use aliases in your queries as it enhances readability and takes up less space. Great post however thanks to the time you spent preparing the DDL for us.

Good luck!

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-02 : 11:21:55

I'm trying

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-02 : 11:31:21

by the way -- i say do not move away from too many views. As long as they are generic and can be reused in other stored procedures (and/or ad-hoc SQL), views are very valuable and will make all of your code much shorter and more readable.

Plus, the major benefit of views -- if the base table needs to change, you can often just leave the view as is so that existing code still works. i.e., if a table gets partitioned into two tables, if you always had accessed the table with VIEWS, then none of your code needs to change after the partitioning occurs. just redefine the VIEW to include the two parts of the table.

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 11:37:39

Views are invaluable when doing data auditing. Saves so much time.

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-02 : 11:52:07

Well, I'm not moving away from views, I'm converting a bunch of Access queries and some of them are deeply nested, so I'm trying rationalise them into 1 or 2 views to do the same job as opposed to 10 queries!

Thanks for the advice though.

Mark

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-02 : 12:41:48

quote:
Originally posted by mparter

Well, I'm not moving away from views, I'm converting a bunch of Access queries and some of them are deeply nested, so I'm trying rationalise them into 1 or 2 views to do the same job as opposed to 10 queries!

Thanks for the advice though.

Mark

Been there done that....HAd one once that was 13 levels deep with three branches...

Just plain ugly...lot of times you'll find some very bad code...

GROUP BY's with no scalar function....ect

You need to start with the lowest level query and convert that...

Then build up....use the derived tables as mentioned...

Brett

8-)

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-06-02 : 15:09:15

I need the results from 2 queries returned as a single row. How can I achieve this. The result from the first query will ALWAYS return 1 row and the second query will ALWAYS return 2 rows.

I want a single row returned showing data from both queries.

As a sample of what I am trying to achieve I show 2 queries below using northwind.

use northwind
select productname, categoryid from products where productid=1

select productid from [order details] where orderid=10249

Required result :

productname categoryid p_id_1 p_id_2

any help appreciated.

Declan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 04:11:41

quote:
Originally posted by liffey

I need the results from 2 queries returned as a single row. How can I achieve this. The result from the first query will ALWAYS return 1 row and the second query will ALWAYS return 2 rows.

I want a single row returned showing data from both queries.

As a sample of what I am trying to achieve I show 2 queries below using northwind.

use northwind
select productname, categoryid from products where productid=1

select productid from [order details] where orderid=10249

Required result :

productname categoryid p_id_1 p_id_2

any help appreciated.

Declan

Start a new topic...

But how would you know how to join the two resultsets??

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-03 : 07:11:06

Really sorry about this guys but I still can't get my head around this. Are there any good articles on doing this? I think I'll get a book and read it over the weekend. In the meantime, could you help me out with this one? Last one, I promise

Merge the two views below into 1...

Views

CREATE VIEW dbo.vwModEnrolled
AS
SELECT     COUNT(*) AS Enrolled
FROM         dbo.cam_smo INNER JOIN
                      dbo.cam_eve ON dbo.cam_smo.mod_code = dbo.cam_eve.mod_code AND dbo.cam_smo.mav_occur = dbo.cam_eve.mav_occur AND 
                      dbo.cam_smo.ayr_code = dbo.cam_eve.ayr_code AND dbo.cam_smo.psl_code = dbo.cam_eve.psl_code
GROUP BY dbo.cam_smo.mod_code, RTRIM(dbo.cam_eve.eve_crsc) + '/' + RTRIM(dbo.cam_eve.eve_blok) + '/' + RTRIM(dbo.cam_eve.eve_occl) 
                      + '/' + dbo.cam_eve.ayr_code
HAVING      (dbo.cam_smo.mod_code = 'D00811') AND (RTRIM(dbo.cam_eve.eve_crsc) + '/' + RTRIM(dbo.cam_eve.eve_blok) + '/' + RTRIM(dbo.cam_eve.eve_occl) 
                      + '/' + dbo.cam_eve.ayr_code = 'NQADMINT2/F1/A/2003/4')

CREATE VIEW dbo.vwModPassed
AS
SELECT     COUNT(*) AS Passed
FROM         dbo.ins_smr INNER JOIN
                      dbo.cam_eve ON dbo.ins_smr.mod_code = dbo.cam_eve.mod_code AND dbo.ins_smr.mav_occur = dbo.cam_eve.mav_occur AND 
                      dbo.ins_smr.ayr_code = dbo.cam_eve.ayr_code AND dbo.ins_smr.psl_code = dbo.cam_eve.psl_code
WHERE     (dbo.ins_smr.mod_code = 'D00811') AND (dbo.ins_smr.smr_actg = 'P') AND (RTRIM(dbo.cam_eve.eve_crsc) + '/' + RTRIM(dbo.cam_eve.eve_blok) 
                      + '/' + RTRIM(dbo.cam_eve.eve_occl) + '/' + dbo.cam_eve.ayr_code = 'NQADMINT2/F1/A/2003/4')

Table and data

CREATE TABLE [dbo].[ins_smr] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[smr_actg] [char] (2) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]
GO


CREATE TABLE [dbo].[cam_smo] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[cam_eve] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NULL ,
	[eve_crsc] [char] (12) COLLATE Latin1_General_CI_AS NULL ,
	[mav_occur] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ayr_code] [char] (6) COLLATE Latin1_General_CI_AS NULL ,
	[psl_code] [char] (6) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]
GO


INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')


INSERT INTO [dbo].[ins_smr] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[ins_smr] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[ins_smr] ([mod_code]) VALUES ('D00811')

INSERT INTO [dbo].[cam_eve] ([mod_code], [eve_crsc],[mav_occur], [ayr_code], [psl_code]) VALUES ('D00811', 'NQADMINT2', 'F1', 'A', '2003/4')

I had to rush through this so hopefully I've got the data right, etc.

Thanks for your time,

Mark

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-03 : 15:29:46

Help me please, one last time

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-03 : 16:13:34

Anybody home?

OK, to prove I've been trying, here's what I've come up with so far;


SELECT     COUNT(*) AS Enrolled, c.Passed
FROM         dbo.cam_smo a INNER JOIN
                      dbo.cam_eve b ON a.mod_code = b.mod_code AND a.mav_occur = b.mav_occur AND 
                      a.ayr_code = b.ayr_code AND a.psl_code = b.psl_code INNER JOIN
                          (SELECT COUNT(a.mod_code) AS Passed
                            FROM   dbo.ins_smr AS a INNER JOIN dbo.cam_eve AS b ON b.mod_code = a.mod_code AND b.mav_occur = a.mav_occur AND 
                                                   b.ayr_code = a.ayr_code AND b.psl_code = a.psl_code
GROUP BY a.mod_code, RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code
HAVING      (a.mod_code = 'D00811') AND (RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code = 'NQADMINT2/F1/A/2003/4')) c
ON b.mod_code = c.mod_code AND b.mav_occur = c.mav_occur AND 
                                                   b.ayr_code = c.ayr_code AND b.psl_code = c.psl_code

GROUP BY a.mod_code, RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code
HAVING      (a.mod_code = 'D00811') AND (RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code = 'NQADMINT2/F1/A/2003/4')

And, it doesn't work!

Query Analyzer goves me the following errors;


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'mod_code'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mav_occur'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ayr_code'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'psl_code'.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-03 : 16:25:38

In the first bold section you return a single column in an inner select. you then try to join that inner select using columns you did not include. Thus your errors in SQA.

quote:

Originally posted by mparter

Anybody home?

OK, to prove I've been trying, here's what I've come up with so far;


SELECT     COUNT(*) AS Enrolled, c.Passed
FROM         dbo.cam_smo a INNER JOIN
                      dbo.cam_eve b ON a.mod_code = b.mod_code AND a.mav_occur = b.mav_occur AND 
                      a.ayr_code = b.ayr_code AND a.psl_code = b.psl_code INNER JOIN
                          (SELECT COUNT(a.mod_code) AS Passed
                            FROM   dbo.ins_smr AS a INNER JOIN dbo.cam_eve AS b ON b.mod_code = a.mod_code AND b.mav_occur = a.mav_occur AND 
                                                   b.ayr_code = a.ayr_code AND b.psl_code = a.psl_code
GROUP BY a.mod_code, RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code
HAVING      (a.mod_code = 'D00811') AND (RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code = 'NQADMINT2/F1/A/2003/4')) c
ON b.mod_code = c.mod_code AND b.mav_occur = c.mav_occur AND 
                                                   b.ayr_code = c.ayr_code AND b.psl_code = c.psl_code

GROUP BY a.mod_code, RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code
HAVING      (a.mod_code = 'D00811') AND (RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code = 'NQADMINT2/F1/A/2003/4')

And, it doesn't work!

Query Analyzer goves me the following errors;


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'mod_code'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mav_occur'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ayr_code'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'psl_code'.

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-06-03 : 16:47:00

OK, I've changed it like so and now I get another error


SELECT     COUNT(*) AS Enrolled, c.Passed
FROM         dbo.cam_smo a INNER JOIN
                      dbo.cam_eve b ON a.mod_code = b.mod_code AND a.mav_occur = b.mav_occur AND a.ayr_code = b.ayr_code AND 
                      a.psl_code = b.psl_code INNER JOIN
(SELECT     COUNT(*) AS Passed, b.mod_code, b.mav_occur, b.ayr_code, b.psl_code
FROM         dbo.ins_smr a INNER JOIN
                      dbo.cam_eve b ON a.mod_code = b.mod_code AND a.mav_occur = b.mav_occur AND a.ayr_code = b.ayr_code AND a.psl_code = b.psl_code
WHERE     (a.mod_code = 'D00811') AND (a.smr_actg = 'P') AND (RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) 
                      + '/' + b.ayr_code = 'NQADMINT2/F1/A/2003/4')) AS c ON
b.mod_code = c.mod_code AND b.mav_occur = c.mav_occur AND b.ayr_code = c.ayr_code AND b.psl_code = c.psl_code
GROUP BY a.mod_code, RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) + '/' + b.ayr_code
HAVING      (a.mod_code = 'D00811') AND (RTRIM(b.eve_crsc) + '/' + RTRIM(b.eve_blok) + '/' + RTRIM(b.eve_occl) + '/' + b.ayr_code = 'NQADMINT2/F1/A/2003/4')

and the error is...


Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.Passed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'b.mod_code' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'b.mav_occur' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'b.ayr_code' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'b.psl_code' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-03 : 16:50:17

Why do you need to return this in one query? Why can't the front end handle this for you? If you must do it in the back end, you could put each COUNT into a temp table that has two columns. Wrap the whole thing into a stored procedure and just return the temp table.

Tara

stephe40
Posting Yak Master

218 Posts

Posted - 2004-06-03 : 17:19:55

quote:

Originally posted by mparter

Really sorry about this guys but I still can't get my head around this. Are there any good articles on doing this? I think I'll get a book and read it over the weekend. In the meantime, could you help me out with this one? Last one, I promise

Merge the two views below into 1...

Views

CREATE VIEW dbo.vwModEnrolled
AS
SELECT     COUNT(*) AS Enrolled
FROM         dbo.cam_smo INNER JOIN
                      dbo.cam_eve ON dbo.cam_smo.mod_code = dbo.cam_eve.mod_code AND dbo.cam_smo.mav_occur = dbo.cam_eve.mav_occur AND 
                      dbo.cam_smo.ayr_code = dbo.cam_eve.ayr_code AND dbo.cam_smo.psl_code = dbo.cam_eve.psl_code
GROUP BY dbo.cam_smo.mod_code, RTRIM(dbo.cam_eve.eve_crsc) + '/' + RTRIM(dbo.cam_eve.eve_blok) + '/' + RTRIM(dbo.cam_eve.eve_occl) 
                      + '/' + dbo.cam_eve.ayr_code
HAVING      (dbo.cam_smo.mod_code = 'D00811') AND (RTRIM(dbo.cam_eve.eve_crsc) + '/' + RTRIM(dbo.cam_eve.eve_blok) + '/' + RTRIM(dbo.cam_eve.eve_occl) 
                      + '/' + dbo.cam_eve.ayr_code = 'NQADMINT2/F1/A/2003/4')

CREATE VIEW dbo.vwModPassed
AS
SELECT     COUNT(*) AS Passed
FROM         dbo.ins_smr INNER JOIN
                      dbo.cam_eve ON dbo.ins_smr.mod_code = dbo.cam_eve.mod_code AND dbo.ins_smr.mav_occur = dbo.cam_eve.mav_occur AND 
                      dbo.ins_smr.ayr_code = dbo.cam_eve.ayr_code AND dbo.ins_smr.psl_code = dbo.cam_eve.psl_code
WHERE     (dbo.ins_smr.mod_code = 'D00811') AND (dbo.ins_smr.smr_actg = 'P') AND (RTRIM(dbo.cam_eve.eve_crsc) + '/' + RTRIM(dbo.cam_eve.eve_blok) 
                      + '/' + RTRIM(dbo.cam_eve.eve_occl) + '/' + dbo.cam_eve.ayr_code = 'NQADMINT2/F1/A/2003/4')

Table and data

CREATE TABLE [dbo].[ins_smr] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL ,
	[smr_actg] [char] (2) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]
GO


CREATE TABLE [dbo].[cam_smo] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[cam_eve] (
	[mod_code] [char] (12) COLLATE Latin1_General_CI_AS NULL ,
	[eve_crsc] [char] (12) COLLATE Latin1_General_CI_AS NULL ,
	[mav_occur] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
	[ayr_code] [char] (6) COLLATE Latin1_General_CI_AS NULL ,
	[psl_code] [char] (6) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]
GO


INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[cam_smo] ([mod_code]) VALUES ('D00811')


INSERT INTO [dbo].[ins_smr] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[ins_smr] ([mod_code]) VALUES ('D00811')
INSERT INTO [dbo].[ins_smr] ([mod_code]) VALUES ('D00811')

INSERT INTO [dbo].[cam_eve] ([mod_code], [eve_crsc],[mav_occur], [ayr_code], [psl_code]) VALUES ('D00811', 'NQADMINT2', 'F1', 'A', '2003/4')

I had to rush through this so hopefully I've got the data right, etc.

Thanks for your time,

Mark

In your DML, cam_smo does not have all the required columns.

- Eric

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources