BKS BKS - 8 months ago 38
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


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

    ,a1.Name AS Name1
    ,a2.Name AS Name2
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))
('A', 'John')
,('B', 'Alex')
,('C', 'Carl')

CREATE TABLE AuthorsCompatibility (AuthorID1 nvarchar(1), AuthorID2 nvarchar(1), Compatibility money)
INSERT INTO AuthorsCompatibility (AuthorID1, AuthorID2, Compatibility)
('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