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
 SQL QUERY (SQL 2000)

Author  Topic 

SQLegal
Starting Member

3 Posts

Posted - 2014-11-17 : 10:20:53
I need to join 2 tables; the 2 tables have primary - foreign key

Table one is my main table with primary key, the table 2 has the foreign key

A unique record on table one is associated with many records on table 2, records on table two are organized by date

I need to link each unique record from table one to the record on table two that has the greater date


Can anyone help me with the formulation of this query?


sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-11-17 : 10:35:50

select t.pk, s.fk from table1 t
join table2 s
on t.pk = s.fk
where (t.date1 < s.date2)

Hema Sunder
Go to Top of Page

SQLegal
Starting Member

3 Posts

Posted - 2014-11-17 : 11:18:14
I don't have date on table one

for example, I have a unique record ID on table one, Id is linked to many records on table 2, each record with different date.
I need the last record on from table shorter by date. the record with the greater date

Thanks
Go to Top of Page

SQLegal
Starting Member

3 Posts

Posted - 2014-11-17 : 15:24:54
TABLE 1
ID LASTNAME FIRSTNAME
001 TEST1 AB
002 TEST2 BC

TABLE 2
ID LASTNAME FIRSTNAME DATE
001 TEST1 AB 20141031
002 TEST2 BC 20141031

001 TEST1 AB 20141117
002 TEST2 BC 20141117

001 TEST1 AB 20141130
002 TEST2 BC 20141130
Go to Top of Page

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 08:37:19
I think the answer is to use the the max function.


max(date) and group by foreign key on table 2
Go to Top of Page

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 08:45:00
I think you might have to use a CTE
WITH CTE_name (Date,ForeignKey)
AS
(
Select
Max(date),
ForeignKey
FROM
Table2
GROUP BY
ForeignKey
)

then do the join on CTE_name on table1.PrimaryKey = CTE_name.Foreign Key.


If you don't want to use a Common Table Expression then create a view of the maximum dates and just join Table1 to that view.
Go to Top of Page

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 09:04:35
Tested this,

I don't think you can do a join on a CTE so you will just have to do the first part as a view so you have a table with latest dates and then just do a join between table1 and the view.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 10:13:28
You can certainly join on a CTE. After all, a CTE is just a convenient way to lay out subqueries. That is, these are equivalent:


WITH

CTE1 AS (
SELECT 1 AS A, 2 AS B
),

CTE2 AS (
SELECT 1 AS A, 3 AS B
)

SELECT *
FROM CTE1
JOIN CTE2 on CTE1.A = CTE2.A


and



SELECT *
FROM (
SELECT 1 AS A, 2 AS B
) SUB1

JOIN (
SELECT 1 AS A, 3 AS B
) SUB2

ON SUB1.A = SUB2.A


However, the real issue is that the OP stated SQL SERVER 2000, which does not support CTEs at all.
Go to Top of Page

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 11:57:04
Ah, I didn't know that about 2000.

guess he/she will just have to create a view with the max(date) function then.
Go to Top of Page

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 11:58:30
I actually meant can you join a CTE to a table? Or is it just a CTE to a CTE?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 12:11:43
Think of a CTE as a subquery. So yes, you can join it to table, just like a subquery
Go to Top of Page

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 12:16:15
Thanks,

Only been working with SQL a couple of weeks so thanks for the info.
Go to Top of Page
   

- Advertisement -