Joining Multiple Tables in SQL Server 2008 causes error

Question

I am writing a query to join 3 tables, I have the following tables:

Apn [ID, QualHolderID, RecvDate, Barcode]
QualHolder [ID, Surname, FullName, DOB, ResCountryID, Gender]
Country [ID, Name]
Gender [ID, Name]

I wrote the following SQL statement to retrieve the data:

SELECT 
    a.QualHolderID, b.FullName, b.Surname, d.Name, b.DOB, b.ResCountryID, a.RecvDate
FROM
    dbo.Apn a 
INNER JOIN 
    dbo.QualHolder b ON a.QualHolderID = b.ID 
JOIN
    dbo.QualHolder c 
INNER JOIN 
    dbo.Gender d ON c.Gender = d.ID 
WHERE
    b.ResCountryID = 48

But now I get the following error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'ID'.

I would recommend to use **more meaningful alias** for your tables! Just using `a, b, c` is confusing and not helpful. I would use `a` for `Apn`, `qh` for `QualHolder`, `g` for `Gender` etc. - those aliases are much clearer and more obvious to anyone reading and studying your query — marc_s, Feb 18 '14 at 09:11

score 1 · Answer 1 · answered Feb 18 '14 at 08:51

I think that the correct query is:

 SELECT a.QualHolderID, b.FullName, b.Surname, d.Name, b.DOB, b.ResCountryID, a.RecvDate
 FROM dbo.Apn a 
 INNER JOIN dbo.QualHolder b ON a.QualHolderID = b.ID 
 INNER JOIN dbo.Gender c ON b.Gender = c.ID 
 WHERE b.ResCountryID = 48

score 0 · Accepted Answer · edited Feb 18 '14 at 10:24

You don't need to join dbo.QualHolder table two times. Try it this way:

SELECT a.qualholderid, 
       b.fullname, 
       b.surname, 
       d.name, 
       b.dob, 
       b.rescountryid, 
       a.recvdate 
FROM   dbo.apn a 
       INNER JOIN dbo.qualholder b 
               ON a.qualholderid = b.id 
       INNER JOIN dbo.gender d 
               ON b.gender = d.id 
WHERE  b.rescountryid = 48

Shell · Answer 3 · 2014-02-18T10:53:42.157

0

Your problem is here = b.ID JOIN use following query and execute.

SELECT a.QualHolderID, b.FullName, b.Surname, d.Name, b.DOB, b.ResCountryID, a.RecvDate
FROM (dbo.Apn a INNER JOIN dbo.QualHolder b ON a.QualHolderID = b.ID) 
INNER JOIN dbo.Gender d ON b.Gender = d.ID 
WHERE b.ResCountryID = 48

edited Feb 18 '14 at 10:53

answered Feb 18 '14 at 08:57

Shell

6,818
11
39
70

you don't have the alias `c` defined. – NickyvV Feb 18 '14 at 10:22

Joining Multiple Tables in SQL Server 2008 causes error

3 Answers3