|
Home > Archive > SQL server exams > June 2002 > Inner Join
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| cocolocopolo 2002-06-24, 8:01 am |
| Dear ladies and gentlemen, here is the Inner Join statement.
When I enter the first way, it is ok (comes up 7 rows)
Use Pubs
Go
Select p.pub_id, p.pub_name, i.pr_info
From publishers AS p
Inner Join pub_info as i
on p.pub_id=i.pub_id
(8 row(s) affected)
But when I enter the code as following:
Use Pubs
Go
Select i.pub_id, i.pr_info, p.pub_name
From pub_info as i
Inner Join publishers as p
On pub_info.pub_id = publishers.pub_id
I got error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'pub_info' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'publishers' does not match with a table name or alias name used in the query.
What happens to the second statement???
Thanks for any advice. | |
| baruugh 2002-06-24, 5:05 pm |
| Hi,
You need to make sure that your aliases are used in the join - ie, if you alias a table, then you need to use that alias in the join statement.
The first statement proves this -
Select p.pub_id, p.pub_name, i.pr_info
From publishers AS p
Inner Join pub_info as i
on p.pub_id=i.pub_id
whereas the second statement uses the aliases in the select statement, but the table names in the join -
Select i.pub_id, i.pr_info, p.pub_name
From pub_info as i
Inner Join publishers as p
On pub_info.pub_id = publishers.pub_id
Hope this helps. | |
| limsam 2002-06-24, 10:07 pm |
| Hi
Yes barug is correct.
Edit the last line as
On i.pub_id = p.pub_id
And run! | |
| cocolocopolo 2002-06-25, 1:22 am |
| Thanks baruugh and limsam.
I got now. |
|
|
|
|