Web Development

Ask something
Discussion Locked

SQL Question - Joins

0 helpful
3 replies

by jafa

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello all

I need to perform a query on two tables in SQL Server 2000.

I need to pull everything from table2 with the values from table one, if the type matches use that value, otherwise if the type dosen't match I need to use the value where the type is null.

Example:

Table1 Table2
Style Type Vaule Style Type
---------------- -----------
a 1 a 5
a 1 .9 a 1
a 2 .8 a 3
b 1.1 a 2
b 1 1 b 4
c 1.2 c 4
c 3
c 1

Desired Output
Style Type Value
-----------------
a 5 1
a 1 .9
a 3 1
a 2 .8
b 4 1.1

Any ideas?

Thanks

3 replies

by archimede

I don't have SQLServer here, but the following seems to work in Access:

SELECT T2.Style, T2.Type, (SELECT TOP 1 T1.Value FROM Table1 T1 WHERE
(T1.Type = T2.Type OR T1.Type Is Null) AND T1.Style = T2.Style ORDER BY T1.Type DESC;) AS MyValue
FROM Table2 T2;

HTH.

Alessandro

This discussion is closed to new replies.