Join two tables together with the use of a third table that contains composite keys from both?

Question

I'm trying to combine two tables together with a third that is comprised of a single column that is a combination of the two tables primary key column. I've considered using substring to separate the column into parts that can be compared to either table's key column but I might be making it more difficult.

Answer 1

The problem here is your design; you need to fix it. You are storing a delimited value in your column, combinedcode, in your junction table.

What you should be doing is storing the 2 values in separate columns, and then creating your foreign keys on those values. This would look something like this:

CREATE TABLE dbo.Table1 (SomeID varchar(10) NOT NULL,
                         SomeValue varchar(20));
ALTER TABLE dbo.Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY (SomeID);
GO
CREATE TABLE dbo.Table2 (OtherID varchar(10) NOT NULL,
                         OtherValue varchar(20));
ALTER TABLE dbo.Table2 ADD CONSTRAINT PK_Table2 PRIMARY KEY (OtherID);
GO
CREATE TABLE dbo.JunctionTable (SomeID varchar(10) NOT NULL,
                                OtherID varchar(10) NOT NULL);
ALTER TABLE dbo.JunctionTable ADD CONSTRAINT FK_JunctionTable1 FOREIGN KEY (SomeID) REFERENCES dbo.Table1(SomeID);
ALTER TABLE dbo.JunctionTable ADD CONSTRAINT FK_JunctionTable2 FOREIGN KEY (OtherID) REFERENCES dbo.Table2(OtherID);

Depending on your design, you may want to make it so that the value in the junction table are are unique:

ALTER TABLE dbo.JunctionTable ADD CONSTRAINT PK_JunctionTable PRIMARY KEY (SomeID,OtherID);

Then, to do your JOINs it would be as simple as:

SELECT {Your Columns}
FROM dbo.Table1 T1
     JOIN dbo.JunctionTable JT ON T1.SomeID = JT.SomeID
     JOIN dbo.Table2 T2 ON JT.OtherID = T2.OtherID;