Keeping duplicates using Count and Max with RIGHT OUTER JOIN 
Author Message
 Keeping duplicates using Count and Max with RIGHT OUTER JOIN

How can all of a table's entries be included in an output set when using
COUNT and MAX?

Given the following Query:
SELECT a.pid AS p, COUNT(c.eid) AS m, MAX(c.eid) AS t
INTO holder
FROM tableC c RIGHT OUTER JOIN
 TableA a ON c.pid = a.pid
GROUP BY a.pid

-- How can this include duplicate entries in a.pid?

IF Table A has field a of int
1
2
3
4
1
2

And Table B has

1 1
1 2
1 3
1 8
2 2
2 4
2 6
4 8

And get a result set of
1 3 3
2 3 6
3 0 NULL
4 1 8
1 3 3
2 3 6



Mon, 26 Sep 2005 15:34:48 GMT
 Keeping duplicates using Count and Max with RIGHT OUTER JOIN

Kory,

In the future, post table schemas as CREATE TABLEs and sample data as INSERTs.

The following, I think, is the technique that you are looking for, but frankly I have not been able to figure out how you get your output given your input, so if your output is indeed what you're after then you either have to somehow modify the query or better explain how the output relates to the input.

create table A
(
 a int NOT NULL
)

create table B
(
 c1 int NOT NULL,
 c2 int NOT NULL
)
go

insert into A values (1)
insert into A values (2)
insert into A values (3)
insert into A values (4)
insert into A values (1)
insert into A values (2)

insert into B values (1, 1)
insert into B values (1, 2)
insert into B values (1, 3)
insert into B values (1, 8)
insert into B values (2, 2)
insert into B values (2, 4)
insert into B values (2, 6)
insert into B values (4, 8)
go

alter table A add PK int NOT NULL IDENTITY (1, 1)
go

select A.a, cnt, max_c2
from A
join (select A.a, count(*) as cnt , max(b.c2) as max_c2
       from A
       left outer join B
        on (A.a = B.c1)
       group by A.a) as x
  on (A.a = x.a)
order by A.PK
go

alter table A drop column PK
go

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:

> How can all of a table's entries be included in an output set when using
> COUNT and MAX?

> Given the following Query:
> SELECT a.pid AS p, COUNT(c.eid) AS m, MAX(c.eid) AS t
> INTO holder
> FROM tableC c RIGHT OUTER JOIN
>  TableA a ON c.pid = a.pid
> GROUP BY a.pid

> -- How can this include duplicate entries in a.pid?

> IF Table A has field a of int
> 1
> 2
> 3
> 4
> 1
> 2

> And Table B has

> 1 1
> 1 2
> 1 3
> 1 8
> 2 2
> 2 4
> 2 6
> 4 8

> And get a result set of
> 1 3 3
> 2 3 6
> 3 0 NULL
> 4 1 8
> 1 3 3
> 2 3 6



Tue, 27 Sep 2005 03:06:01 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Left-Outer join and Right-Outer join

2. Left outer join works, Right outer join just goes on for ever, processor at 100%

3. LEFT OUTER JOIN vs RIGHT OUTER JION

4. INNER JOIN as well as RIGHT OUTER JOIN?????

5. Outer Join + Outer Join

6. SQL-92 outer join vs T-SQL outer join (6.5 or 7.0) - test script included

7. Outer Joins, difference between *= and OUTER JOIN

8. Outer Join + Outer Join

9. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

10. Outer joins in Oracle vs outer joins in Sybase

11. MS SQL Outer Joins VS Sybase Outer Joins

12. Right Outer join with more than two tables


 
Powered by phpBB® Forum Software