CODEWITHSUNDEEP
sqlsql-servert-sql
Peter Jackson
Peter Jackson

Reputation: 23

SQL Join on tables with from to entries

I have two tables that I want to join. However the information is written as from to in the first table.

My first table looks like this:

No. | Date       | From entry | To Entry |
+---+------------+------------+----------+
1   | 21.12.2013 | 3          | 10

My second table looks like this:

| Entry  |  Code |
+--------+-------+
| 3      | 1     |
| 4      | 0     |
| 5      | 2     |
| 6      | 3     |
| 7      | 1     |
| 8      | 0     |
| 9      | 6     |
| 10     | 1     |

I want to join both based on the from to information. The result should look like this:

| Entry  | Code  | Date       |
+--------+-------+------------+
| 3      | 1     | 21.12.2013 |
| 4      | 0     | 21.12.2013 |
| 5      | 2     | 21.12.2013 |
| 6      | 3     | 21.12.2013 |
| 7      | 1     | 21.12.2013 |
| 8      | 0     | 21.12.2013 |
| 9      | 6     | 21.12.2013 |
| 10     | 1     | 21.12.2013 |

I have no idea how to achieve this with t-sql.

Upvotes: 2

Views: 51

Answers (5)

Hiren gardhariya
Hiren gardhariya

Reputation: 1257

try this

Select table2.*,
table1.date
from table2
left join table1 on 
table2.entry between table1.fromentry and table1.toentry

SQLFIDDLE

Upvotes: 1

Dimt
Dimt

Reputation: 2328

You just need to specify correct condition for the INNER JOIN

SELECT          *
FROM            table1
INNER JOIN      table2 ON  table2.Entry >= table1.[From entry] 
                       AND table2.Entry <= table1.[To entry]

Or you may consider using LEFT JOIN if you want to return all records from table1 no matter if they have related records in table2.

Upvotes: 2

Jesuraja
Jesuraja

Reputation: 3844

Try this: (INNER JOIN and BETWEEN AND)

SELECT 
       Entry, Code, Date
FROM   
       Table1 T1 INNER JOIN
       Table2 T2
              ON T2.Entry BETWEEN T1.[From entry] AND T1.[To entry]

Upvotes: 1

qxg
qxg

Reputation: 7036

SELECT Entry, Code, (SELECT T1.Date 
                    FROM FirstTable T1 
                    WHERE T1.Entry BETWEEN T2.[From Entry] 
                        AND T2.[To Entry]) AS Date
FROM SecondTable T2

Upvotes: 0

Jithin Shaji
Jithin Shaji

Reputation: 6073

This should work

SELECT  B.Entry,B.Code,A.Date
FROM    Table1 A CROSSJOIN Table2 B
WHERE   B.Entry >= A.[From Entry] AND B.Entry <= A.[To Entry]

Upvotes: 0

Related Questions