Select max - 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

SQL Server Development (2000)

Select max

Author

Topic

a4mrmar1n3
Starting Member

3 Posts

Posted - 2006-09-14 : 12:59:12

I have two tables that I want to perform a join on. Table A contains a field with a 'one to many' relationship with a field in Table B. I want to retrieve a number of columns from table A and the max rowid (primary key) and a 3 other columns from table B. I want the fields from Table A to appear whether or not Table B contains a corresponding record.

tableA
========
fldA1 fldA2 fldA3 fldA4
--------------------------------
1 xxxx xxxx xxxx
2 yyyy yyyy yyyy
3 zzzz zzzz zzzz

tableB
=======
fldB1 fldB2 fldB3 fldB4
--------------------------------
1 aaaa aaaa 2
2 bbbb bbbb 1
3 cccc cccc 2
4 dddd dddd 2
5 eeee eeee 1

My Attempt:

select A.fldA1,A.fldA2,A.fldA3,A.fldA4,B.fldB1,B.fldB2,B.fldB3 from tableA A inner join tableB on B.fldB4=A.fldA1 where B.fldB1=(select max(B1.fldB1) from tableB B1 where B1.fldB4=@pVar1)
and A.fldA1=@pVar1

Desired results:

@pVar1 = 2

fldA1 fldA2 fldA3 fldA4 fldB1 fldB2 fldB3
----------------------------------------------------------------
2 yyyy yyyy yyyy 4 dddd dddd

@pVar1 = 3

fldA1 fldA2 fldA3 fldA4 fldB1 fldB2 fldB3
----------------------------------------------------------------
2 yyyy yyyy yyyy Null Null Null

Hopefully someone can help me out.

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-14 : 13:00:27

LEFT JOIN perhaps?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

a4mrmar1n3
Starting Member

3 Posts

Posted - 2006-09-14 : 13:12:32

Thanks for your reply. I tried that and it returns a blank record.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 13:55:09

You need to allow for the NULL in your WHERE when you use OUTER JOIN, like this

select A.fldA1,A.fldA2,A.fldA3,A.fldA4,B.fldB1,B.fldB2,B.fldB3
from tableA A
left outer join tableB on B.fldB4 = A.fldA1
where (B.fldB1 = (select max(B1.fldB1) from tableB B1 where B1.fldB4 = @pVar1) OR B.fldB1 IS NULL) and A.fldA1 = @pVar1

a4mrmar1n3
Starting Member

3 Posts

Posted - 2006-09-14 : 14:18:20

Thanks for all suggestions. I finally got it to work. Here is my query:

select a.flda1,a.flda2,a.flda3,a.flda4,b1.fldb1,b1.fldb2,b1.fldb3
from tablea a left join
(select b.fldb1,b.fldb2, max(b.fldb4) as fldb4 from tableb b
where b1.fldb4=@pvar1 group by b.fldb1,b.fldb2) as b1
on b.fldb4=a.flda1
where a.flda1=@pvar1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources