query on two tables? - 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

General SQL Server Forums

New to SQL Server Programming

query on two tables?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-30 : 08:36:48

I have two tables to query:-

table1 contains :-

field1 - uniqueID
field2 - short_desc
field3 - customer
field4 - employee_doing_work

table2 contains :-

field1 - ID (not unique but many instances of field1 in other table)
field2 - date_run
field3 - date_required
field4 - work_done

As shown above I have a one to many link of field1 in table1 to field1 in table2.
I need to get all the instances of table1 (all fields) , but only the latest (run_date) instance of the related record in table2 (with all fields)

can anyone help a thickie with the sql for this please as I'm overworked and stressed out.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:42:24

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY t1.uniqueID ORDER BY t2.run_Date DESC) AS Seq,*
FROM table1 t1
INNER JOIN table2 t2
ON t1.uniqueID=t2.ID
)t
WHERE Seq=1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:46:03

if sql 2000

SELECT t1.*
FROM table1 t1
INNER JOIN (SELECT DISTINCT ID FROM table2) t2
ON t1.uniqueID=t2.ID

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-30 : 08:50:02

It's sql 2005, thanks, I'll check it out now.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-30 : 08:54:14

I'm getting the following error:-
The column 'IDNo' was specified multiple times for 't'.

(forgot to mention that field1 in both tables has the same name 'IDNo' but is only unique in table1), does this make the difference?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:54:19

quote:
Originally posted by Bill_C

It's sql 2005, thanks, I'll check it out now.

Cheers

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-30 : 09:06:57

still getting the error!
can't figure out why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 09:13:20

quote:
Originally posted by Bill_C

still getting the error!
can't figure out why?

replace * inside the query with actual column names

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY t1.uniqueID ORDER BY t2.run_Date DESC) AS Seq,uniqueID,
short_desc,
,customer
,employee_doing_work
,date_run
,date_required
,work_done

FROM table1 t1
INNER JOIN table2 t2
ON t1.uniqueID=t2.ID
)t
WHERE Seq=1

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-30 : 09:43:47

Thanks, that sorted it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 14:37:08

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources