CODEWITHSUNDEEP
sql-server
Inam
Inam

Reputation: 51

The multi-part identifier could not be bound in SQL Server

I'm new to SQL Server and I have created three tables students, phones and stud_phone.

The relation between student and phone is many-many the stud_phone table contains the two foreign keys from student and phone tables.

What I want to achieve is that I want to retrieve the record of student of Id1 and their respective phones which he/she has but I'm unable to do it.

I get this error:

multipart identifier could not be bound

Here is my schema:

enter image description here

Here is my query : 

select * 
from student, phone
join stud_phone on student.stId = 1
                and student.stId = stud_phone.stId
                and phone.phId = stud_phone.phId

Upvotes: 0

Views: 2371

Answers (4)

user1443098
user1443098

Reputation: 7675

You probably mean:

select * 
from student, phone
join stud_phone on student.stId = 1
                and student.stId = stud_phone.stId
                and phone.phId = stud_phone.phId

Replace the '_' in student_Id with a '.': student.stId

Upvotes: 1

Vinit
Vinit

Reputation: 2607

the below query should return students with their phone numbers. I have used tables aliases and ANSI-92 join syntax

select s.*, p.* 
from student as s
join stud_phone as sp on s.stId = sp.stId
inner join phone as p on p.phId = sp.phId
where s.stId = 1

Update : if the relation between student and stud_phone is zero to many, then you should use LEFT JOIN between student and stud_phone tables.

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23837

select * 
from student s
left join stud_phone sp on s.stId=sp.stId
inner join phone p on p.phId = sp.phId
where s.stId = 1;

Here s, sp and p are "local aliases" and just used for the purpose of less typing (it has useful benefits in other places). Note that using old style joins is asking for trouble, it implicitly creates an inner join (but a student might have 0 phones).

Upvotes: 3

ccarpenter32
ccarpenter32

Reputation: 1077

Perhaps this is what you meant:

SELECT s.*, sp.*
FROM student s
INNER JOIN stud_phone sp ON sp.stId = s.stId
INNER JOIN phone p ON p.phId = s.phId

NOTE: You really should think about swapping into a more reasonable style of joins here. ANSI-92 is now old enough to drink. ;)

Upvotes: 2

Related Questions