help with linking table - 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)

help with linking table

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-15 : 00:32:24

Good morning SQL gurus.

I have two tables with a one to many relationship.

I want to do a left join so that I always pull out every row from table1 whether it has a row in table 2 or not.
But also if table2 has many rows for the table1 row I only want the top row from table2 (i.e. the most recent) this is recorded in a field called recid. So I will need to somehow use Max(recid) .
I'm almost there but can't quite get it right in my head.

Thanks a lot

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-15 : 02:23:18

send us the query and sample data.... if you want rows from both tables, then i think you must go for a FULL OUTER JOIN

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-15 : 03:02:06

this is the query that I have. the problem with it is that it if there is more than one row in the second table I get more than one row for each table entry.

I don't think i need a right join because i will never need a row for the second table on its own.

select O.Userid, O.Company, O.Contact, O.stage, O.Name, O.Forprob, O.Foramt, O.Startdate, Contact1.key5,ch.lastdate, ch.lasttime from opmgr O left outer join contact1 on O.ACCOUNTNO = Contact1.ACCOUNTNO
left outer join conthist ch
on O.opid = ch.loprecid
where O.rectype = 'O' order by ch.lastdate desc, ch.lasttime desc

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-15 : 03:05:04

can we make select distinct? it just a guess.... i will tell you the correct join in a while.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:13:42

quote:
Originally posted by icw

Good morning SQL gurus.

I have two tables with a one to many relationship.

I want to do a left join so that I always pull out every row from table1 whether it has a row in table 2 or not.
But also if table2 has many rows for the table1 row I only want the top row from table2 (i.e. the most recent) this is recorded in a field called recid. So I will need to somehow use Max(recid) .
I'm almost there but can't quite get it right in my head.

Thanks a lot

may be this


SELECT O.Userid, O.Company, O.Contact, O.stage, O.Name, O.Forprob, O.Foramt, O.Startdate, Contact1.key5,ch2.lastdate, ch2.lasttime
FROM opmgr O 
left outer join  contact1 
on O.ACCOUNTNO = Contact1.ACCOUNTNO 
left outer join(
select ch.*
from conthist ch
join (select loprecid, MAX(recid) as latest
      from conthist 
      GROUP BY loprecid) ch1
ON ch.loprecid = ch1.loprecid
And ch.recid = ch1.latest
)ch2
on O.opid = ch2.loprecid
where O.rectype = 'O' 
order by ch.lastdate desc, ch.lasttime desc