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

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

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

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

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

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.

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.

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.

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.

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?

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources