Combining three queries [Resolved]
Ref:
1. http://www.vbforums.com/showthread.php?t=328403 for table descriptions
2. http://www.vbforums.com/showthread.php?t=328505 for some of the query stuff I was doing last night
3. http://www.vbforums.com/showthread.php?t=240883 for details of someone who wanted something similar
I'm trying to combine three queries to produce a report for each member, passing the parameter intMemID into a query to return:
1. The member's name
2. The number of sessions they have been to
3. The total number of sessions that have occurred
Using the following, I can get all of the information:
(SELECT Forenames & Chr(32) & Surname FROM Members WHERE MemID = intMemID)
UNION ALL
(SELECT Count(MemID) AS SessionsAttended FROM RegMem WHERE MemID = intMemID)
UNION ALL
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate))
But my question is, how do I modify the query so that instead of having the data displayed in 3 separate rows, it is displayed as 3 fields in one row?
I've been messing around with INNER JOIN for about an hour now to get what I want, and it's just not coming.
Re: Combining three queries
Still not got anywhere with this...
Re: Combining three queries
In order to really help, I would need to see a but bit more of what the data really looks like. I can whip you up a query very easily, but in order to not do it 6 times, I need to see real data.
Something like:
Code:
Member
MemberId ForeName SurName
0000001 Tom Smith
and the other tables also - they are more important...
Re: Combining three queries
If you have a look at the first link I gave, I explained my table design there.
There's one table like you've shown, and another (RegMem) with:
SDate - MemID
01/02/2005 - 8
01/02/2005 - 9
01/02/2005 - 10
01/02/2005 - 15
08/02/2005 - 8
08/02/2005 - 10
Re: Combining three queries
I did this in QUERY ANALYZER...
Code:
begin tran
create table member (intmemberid int,forename varchar(10), surname varchar(10))
insert into member values (8,'TOM','SMITH')
insert into member values (9,'JOHN','JONES')
insert into member values (10,'JACK','ADAMS')
insert into member values (15,'ANN','BLACK')
create table regmem (sdate datetime,memid int)
insert into regmem values ('2005-01-02',8)
insert into regmem values ('2005-01-02',9)
insert into regmem values ('2005-01-02',10)
insert into regmem values ('2005-01-02',15)
insert into regmem values ('2005-08-02',8)
insert into regmem values ('2005-08-02',10)
insert into regmem values ('2005-08-02',10)
select * from member
select * from regmem
select intmemberid,forename,surname
,sum(1) "Visits for member"
,count(distinct sdate) "Distinct dates"
from regmem
left join member on intmemberid=memid
group by intmemberid,forename,surname
rollback
and got these results
Code:
intmemberid forename surname
----------- ---------- ----------
8 TOM SMITH
9 JOHN JONES
10 JACK ADAMS
15 ANN BLACK
sdate memid
------------------------------------------------------ -----------
2005-01-02 00:00:00.000 8
2005-01-02 00:00:00.000 9
2005-01-02 00:00:00.000 10
2005-01-02 00:00:00.000 15
2005-08-02 00:00:00.000 8
2005-08-02 00:00:00.000 10
2005-08-02 00:00:00.000 10
intmemberid forename surname Visits for member Distinct dates
----------- ---------- ---------- ----------------- --------------
8 TOM SMITH 2 2
9 JOHN JONES 1 1
10 JACK ADAMS 3 2
15 ANN BLACK 1 1
I do not understand what you really want for the two last columns. It would appear to me from your example that you would get the same values in both columns - number of visits for the member. What do you really want in the last column. I made it show "distinct" visit dates - member #10 had only two distinct dates - in 3 visits...
Re: Combining three queries
Yes, I think you misunderstood. The last column will always be the same - it is simply a count of the number of distinct dates in the table, not distinct dates that the member has visited on, as that will be the same as the number of sessions they have been to.
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) returns this count.
I'm not trying to create a table, though - it's discrete data I'm extracting about each member, so that I can create a report, eg:
MemID - 8
Surname - Smith
Forenames - Tom
Sessions Attended - 2 out of 5
Re: Combining three queries
Quote:
Originally Posted by olamm2k
Yes, I think you misunderstood. The last column will always be the same - it is simply a count of the number of distinct dates in the table, not distinct dates that the member has visited on, as that will be the same as the number of sessions they have been to.
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) returns this count.
I'm not trying to create a table, though - it's discrete data I'm extracting about each member, so that I can create a report, eg:
MemID - 8
Surname - Smith
Forenames - Tom
Sessions Attended - 2 out of 5
I wasn't trying to show you how to create a table - just needed to do that myself so I could have some test data in order to help you with your problem.
Now this query does what you want (the query in bold that is):
Code:
begin tran
set nocount on
create table member (intmemberid int,forename varchar(10), surname varchar(10))
insert into member values (8,'TOM','SMITH')
insert into member values (9,'JOHN','JONES')
insert into member values (10,'JACK','ADAMS')
insert into member values (15,'ANN','BLACK')
create table regmem (sdate datetime,memid int)
insert into regmem values ('2005-01-02',8)
insert into regmem values ('2005-01-02',9)
insert into regmem values ('2005-01-02',10)
insert into regmem values ('2005-01-02',15)
insert into regmem values ('2005-08-02',8)
insert into regmem values ('2005-08-02',10)
insert into regmem values ('2005-08-02',10)
select * from member
select * from regmem
select intmemberid,forename,surname
,sum(1) "Visits for member"
,(select count(distinct sdate) from regmem) "Distinct dates in table"
from regmem
left join member on intmemberid=memid
group by intmemberid,forename,surname
rollback
Giving these results:
Code:
intmemberid forename surname
----------- ---------- ----------
8 TOM SMITH
9 JOHN JONES
10 JACK ADAMS
15 ANN BLACK
sdate memid
------------------------------------------------------ -----------
2005-01-02 00:00:00.000 8
2005-01-02 00:00:00.000 9
2005-01-02 00:00:00.000 10
2005-01-02 00:00:00.000 15
2005-08-02 00:00:00.000 8
2005-08-02 00:00:00.000 10
2005-08-02 00:00:00.000 10
intmemberid forename surname Visits for member Distinct dates in table
----------- ---------- ---------- ----------------- -----------------------
8 TOM SMITH 2 2
9 JOHN JONES 1 2
10 JACK ADAMS 3 2
15 ANN BLACK 1 2
Re: Combining three queries
That doesn't work. I'm using Access, so I can't use COUNT(DISTINCT SDate) for one thing. It also doesn't like sum(1) "Visits for member" - it has to be sum(1) AS "Visits for member". Even after changing that, I still get 'Join Expression not supported'.
Re: Combining three queries
Quote:
Originally Posted by olamm2k
That doesn't work. I'm using Access, so I can't use COUNT(DISTINCT SDate) for one thing. It also doesn't like sum(1) "Visits for member" - it has to be sum(1) AS "Visits for member". Even after changing that, I still get 'Join Expression not supported'.
I don't do ACCESS - but try this:
Code:
select intmemberid,forename,surname
,count(*) as SessionsAttendance
from regmem
left join member on intmemberid=memid
group by intmemberid,forename,surname
If it doesn't work, copy/paste your query so I can make sure you don't have some other syntax error.
Not sure what you can do about the lack of COUNT(DISTINCT...) in ACCESS - sorry.
Re: Combining three queries
This search had some stuff that might help - others have asked this question before...
Search for ACCESS COUNT DISTINCT
Re: Combining three queries
I have no problems with the count distinct - I'm using (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)), as I explained before.
I'm still getting a 'join expression not supported' error. This seems to be because the ON clause includes a variable.
Re: Combining three queries
Quote:
Originally Posted by olamm2k
I have no problems with the count distinct - I'm using (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)), as I explained before.
I'm still getting a 'join expression not supported' error. This seems to be because the ON clause includes a variable.
I was wondering why intMemberId looked like a variable...
Replace it with the actual column name.
SQL Server doesn't allow you to embed VB variables in query lines like that.
Re: Combining three queries
It works like this:
select regmem.memid, forenames, surname, count(*) as SessionsAttendance
from (select memid from regmem where memid=intmemid) regmem
left join members
on regmem.memid = members.memid
group by regmem.memid, forenames, surname;
But I still also need to include the count distinct part (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) as the last field.
Re: Combining three queries
Looks like this should work:
PARAMETERS [intMemID] Integer;
SELECT TempTable.MemID, Forenames, Surname, COUNT(TempTable.MemID) AS SessionsAttendance, (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM (SELECT MemID FROM RegMem WHERE MemID=[intMemID]) AS TempTable
INNER JOIN Members
ON TempTable.MemID = Members.MemID
GROUP BY TempTable.MemID, Forenames, Surname;
Re: Combining three queries
Quote:
Originally Posted by olamm2k
Looks like this should work:
PARAMETERS [intMemID] Integer;
SELECT TempTable.MemID, Forenames, Surname, COUNT(TempTable.MemID) AS SessionsAttendance, (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM (SELECT MemID FROM RegMem WHERE MemID=[intMemID]) AS TempTable
INNER JOIN Members
ON TempTable.MemID = Members.MemID
GROUP BY TempTable.MemID, Forenames, Surname;
Looks good to me - hope I helped :)
Re: Combining three queries
You did, thanks. I didn't realise you could put subqueries as a field like that before.
Re: Combining three queries [Resolved]
Just realised - using my original query, I simply needed to rewrite it to:
SELECT Forenames & Chr(32) & Surname AS FullName,
(SELECT Count(MemID) FROM RegMem WHERE MemID = intMemID) AS SessionsAttended,
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM Members WHERE MemID = intMemID
From:
(SELECT Forenames & Chr(32) & Surname AS FullName FROM Members WHERE MemID = intMemID)
UNION ALL
(SELECT Count(MemID) AS SessionsAttended FROM RegMem WHERE MemID = intMemID)
UNION ALL
(SELECT COUNT (SDate) AS TotalDates FROM (SELECT SDate FROM RegMem GROUP BY SDate))
Re: Combining three queries [Resolved]
Quote:
Originally Posted by olamm2k
Just realised - using my original query, I simply needed to rewrite it to:
SELECT Forenames & Chr(32) & Surname AS FullName,
(SELECT Count(MemID) FROM RegMem WHERE MemID = intMemID) AS SessionsAttended,
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM Members WHERE MemID = intMemID
From:
(SELECT Forenames & Chr(32) & Surname AS FullName FROM Members WHERE MemID = intMemID)
UNION ALL
(SELECT Count(MemID) AS SessionsAttended FROM RegMem WHERE MemID = intMemID)
UNION ALL
(SELECT COUNT (SDate) AS TotalDates FROM (SELECT SDate FROM RegMem GROUP BY SDate))
I agree that it can be done the way you suggest, but in general your goal should be to reduce sub-queries use as much as possible.
The method I suggested reads through the REGMEM table just once - and uses the GROUP BY/AGGREGATE logic to do it's job.
In MS SQL Server - with COUNT(DISTINCT...) it would be possible to have no sub-queries at all - just one scan of data from REGMEM and the aggregate functions do there jobs.
With sub-queries, the data engine must evaluate each "sub-query" for each row presented - in most cases.
