BKS BKS - 3 months ago 10
SQL Question

Query not producing what its supposed to

I have the following two tables:

Table Authors:

AuthorID Name
A John
B Alex
C Carl


Table AuthorsCompatibility:

AuthorID1 AuthorID2 Compatibility
A B 5.0
A C 4.0
B C 4.5


I want to produce the following:

AuthorID1 Name1 AuthorID2 Name2 Compatibility
A John B Alex 5.0
A John C Carl 4.0
B Alex C Carl 4.5


This is what I tried, but I know it's not working:

select AuthorID1, A.Name, AuthorID2, B.Name, Compatibility
from AuthorsCompatibility, Authors as A, Authors as B
where AuthorID1=A.AuthorID and AuthorID2=B.AuthorID

Answer

You're not joining correctly (you're using the very old style joins). Try joins like this below;

SELECT
    ac.AuthorID1
    ,a1.Name AS Name1
    ,ac.AuthorID2
    ,a2.Name AS Name2
    ,ac.Compatibility
FROM AuthorsCompatibility ac
INNER JOIN Authors a1
    ON ac.AuthorID1 = a1.AuthorID
INNER JOIN Authors a2
    ON ac.AuthorID2 = a2.AuthorID

Using this sample data;

CREATE TABLE Authors (AuthorID nvarchar(1), Name nvarchar(10))
INSERT INTO Authors
VALUES
('A', 'John')
,('B', 'Alex')
,('C', 'Carl')

CREATE TABLE AuthorsCompatibility (AuthorID1 nvarchar(1), AuthorID2 nvarchar(1), Compatibility money)
INSERT INTO AuthorsCompatibility (AuthorID1, AuthorID2, Compatibility)
VALUES
('A', 'B', 5.0)
,('A', 'C', 4.0)
,('B', 'C', 4.5)

Gives this result;

AuthorID1   Name1   AuthorID2   Name2   Compatibility
A           John    B           Alex    5.00
A           John    C           Carl    4.00
B           Alex    C           Carl    4.50