CODEWITHSUNDEEP
sqlsql-servert-sqljoin
ury
ury

Reputation: 1160

SQL Join with table including from/to values

I'm working with a 3rd-party SQL Server database, containing the two tables below (simplified), which I'd like to join into a single view.

The Document table contains the following columns: ID primary key
"other columns".

The DocumentTag table contains the following columns: FromDocumentID ToDocumentID Tag.

How do I create a view of the form Document.ID, Tag, "other columns"?
Join each document its tag (yes, with repetitions) instead of looking for the right ID range in the Document Tag table.

[EDIT] an example:

Document table

ID    | Description | Owner ....
------+-------------+---------
1     | blah        | me 
2     | blah 2      | me
3     | blah 3      | Alice
4     | blah 4      | Bob
5     | blah blah   | me 
6     | blah blah 2 | Bob
7     | blah blah 3 | Alice
8     | blah blah 4 | Alice

Document Tag table

FromDocumentID | ToDocumentID | Tag         | ...
---------------+--------------+-------------+----
1              | 3            | Bananas
4              | 4            | Crocodiles
5              | 5            | Bananas
6              | 8            | Donuts

What I would like to have is 

ID    | Description | Owner  | Tag      | ....
------+-------------+--------+----------+-----
1     | blah        | me     | Bananas
2     | blah 2      | me     | Bananas
3     | blah 3      | Alice  | Bananas
4     | blah 4      | Bob    | Crocodiles
5     | blah blah   | me     | Bananas
6     | blah blah 2 | Bob    | Donuts
7     | blah blah 3 | Alice  | Donuts
8     | blah blah 4 | Alice  | Donuts

Upvotes: 1

Views: 106

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

If the tag applies to many documents by range

select D.ID, T.Tag, D.Other1, D.Other2
from Document D
left join DocumentTag T on D.ID between T.FromDocumentID and T.ToDocumentID

If the tag applies to 2 specific documents by ID. This also shows the creation of a view

create View DocumentWithTag
as
select D.ID, T.Tag, D.Other1, D.Other2
from Document D
left join DocumentTag T on D.ID in (T.FromDocumentID, T.ToDocumentID)

Upvotes: 1

Related Questions