T1                                     T2

F1    F2                               Field1      Field2
--    --                               ------      ------ 
A      A1                              A2           Fred             
A      A2                              B0           Judy
B      B0                              B1           Max
B      B2                              B3           Joe
B      B3

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT		d.F1,
		d.F2,
		p.Field1,
		p.Field2
FROM		(
			SELECT		t1.F1,
					t1.F2,
					ISNULL((SELECT MAX(y.Field1) FROM @T2 AS y WHERE y.Field1 LIKE t1.F1 + '%' AND y.Field1 < t1.F2), t2.Field1) AS Yak
			FROM		@T1 AS t1
			LEFT JOIN	@T2 AS t2 ON t2.Field1 = t1.F2
		) AS d
LEFT JOIN	@T2 AS p ON p.Field1 = d.Yak

quote:

---

Originally posted by nr

Have you had a go at it yourself.
You could do it using a correlated subquery

where field2 = (select max(t2.field2) from t2 where t2.field2 <= t1.f2)


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

F1   F2   Field1 Field2
---- ---- ------ ------
A    A1   NULL   NULL
A    A2   A2     Fred
B    B0   B0     Judy
B    B2   B1     Max
B    B3   B1     Max

B    B3   B3     Joe

quote:

---

Originally posted by Peso

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT		d.F1,
		d.F2,
		p.Field1,
		p.Field2
FROM		(
			SELECT		t1.F1,
					t1.F2,
					ISNULL((SELECT MAX(y.Field1) FROM @T2 AS y WHERE y.Field1 LIKE t1.F1 + '%' AND y.Field1 < t1.F2), t2.Field1) AS Yak
			FROM		@T1 AS t1
			LEFT JOIN	@T2 AS t2 ON t2.Field1 = t1.F2
		) AS d
LEFT JOIN	@T2 AS p ON p.Field1 = d.Yak

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by CanadaDBA

Hi Nigel,

SELECT A.F1, A.F2, B.Field1, B.Field2
FROM T1 A, T2 B
where B.field2 = (select max(B.field2) from t2 where B.field2 <= A.f2)

returnes the following error:
Msg 147, Level 16, State 2, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

quote:

---

Originally posted by nr

Have you had a go at it yourself.
You could do it using a correlated subquery

where field2 = (select max(t2.field2) from t2 where t2.field2 <= t1.f2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

Canada DBA

---

quote:

---

Originally posted by chandan_joshi80

SELECT * FROM T3 WHERE FIELD1 IN (SELECT F2 FROM T2 )

chandan Joshi

---

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT A.F1, A.F2, B.Field1, B.Field2 
FROM @T1 A, @T2 B
where B.field2 = (select max(t2.field2) from @t2 as t2 where t2.field2 <= A.f2)

(5 row(s) affected)

(4 row(s) affected)
F1   F2   Field1 Field2
---- ---- ------ ------

(0 row(s) affected)

quote:

---

Originally posted by CanadaDBA

Nigel,

I merged your script with Peter's table creation, and it didn't return any records.

DECLARE	@T1 TABLE (F1 VARCHAR(1), F2 VARCHAR(2))

INSERT	@T1
SELECT	'A', 'A1' UNION ALL
SELECT	'A', 'A2' UNION ALL
SELECT	'B', 'B0' UNION ALL
SELECT	'B', 'B2' UNION ALL
SELECT	'B', 'B3'

DECLARE	@T2 TABLE (Field1 VARCHAR(2), Field2 VARCHAR(4))

INSERT	@T2
SELECT	'A2', 'Fred' UNION ALL
SELECT	'B0', 'Judy' UNION ALL
SELECT	'B1', 'Max' UNION ALL
SELECT	'B3', 'Joe'

SELECT A.F1, A.F2, B.Field1, B.Field2 
FROM @T1 A, @T2 B
where B.field2 = (select max(t2.field2) from @t2 as t2 where t2.field2 <= A.f2)

Results:

(5 row(s) affected)

(4 row(s) affected)
F1   F2   Field1 Field2
---- ---- ------ ------

(0 row(s) affected)

Canada DBA

---

SELECT A.Storm, A.Obs, A. ObsLat, A.ObsLong, B.Model, B.Init, B.InitLat, B.InitLong 
FROM @T1 A, @T2 B
where B.Init = (select max(t2.Init) from @t2 as t2 where t2.Init <= A.Obs)
    AND ( ABS(B.InitLat-A.ObsLat) <= 5 ) 
    AND ( ABS(B.InitLong-A.ObsLong) <= 5 ) 
Order by A.Storm, A.Obs