CODEWITHSUNDEEP
sqlsqlite
BKS
BKS

Reputation: 2333

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

Upvotes: 2

Views: 43

Answers (4)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

I have tried your query and it works properly:

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)

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

drop table #AuthorsCompatibility,#Authors

Upvotes: 0

Mickey Patel
Mickey Patel

Reputation: 501

SELECT ac.AuthorID1, a.Name, ac.AuthorID2, b.Name, ac.Compatibility
FROM AuthorsCompatibility ac
INNER JOIN AUTHORS a ON ac.AuthorID1 = a.AuthorID
INNER JOIN AUTHORS b ON ac.AuthorID2 = b.AuthorID

Upvotes: 4

Rich Benner
Rich Benner

Reputation: 8113

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

Upvotes: 2

Razzka
Razzka

Reputation: 641

You can rewrite it in JOIN-form like this

select ac.AuthorID1, A.Name, ac.AuthorID2, B.Name, ac.Compatibility
  from AuthorsCompatibility ac
  join Authors as A
    on ac.AuthorID1 = A.AuthorID
  join Authors as B
    on ac.AuthorID2 = B.AuthorID

Upvotes: 2

Related Questions