How to join 3 table in asp.net?

Question

Possible Duplicate:
Ambiguous column name error

I want to join 3 tables: post_job, apply_job, and employer_detail.

table1: post_job

job_id, emp_id, job_title
table 2: apply_job

applied_id, job_id, js_id, emp_id
table 3: jobskker_personal

js_id, full_name

I want it to look like this:

job_id, job_title, full_name

I have written a query like this:

"SELECT job_id,job_title,post_date ,full_name " + "FROM post_job,applied_jobs,jobseeker_personal WHERE emp_id='"+emp_id+"' ";

It shows an error:

Ambiguous column name 'emp_id'.
Ambiguous column name 'job_id'.
Ambiguous column name 'job_title'.

Can anyone please help me?.

http://stackoverflow.com/a/318089/284240 – Tim Schmelter May 16 '12 at 20:01 — Tim Schmelter, May 16 '12 at 20:01
Please consider marking this question as answered. – Leon Cullens May 26 '12 at 20:48 — Leon Cullens, May 26 '12 at 20:48

score 1 · Answer 1 · answered May 16 '12 at 20:05

1

Try specifying the table name in front of the column name. That's probably why your code doesn't know where to look.

answered May 16 '12 at 20:05

Leon Cullens

12,276
10
51
85

score 0 · Answer 2 · answered May 16 '12 at 20:06

The "ambiguous column" means that the column exists in more than one of the tables. Specify the full column name using [table name].[column name], like so:

"SELECT post_job.job_id, post_job.job_title, post_date, full_name  " + "FROM
    post_job, applied_jobs, jobseeker_personal WHERE post_job.emp_id='" + emp_id + "' ";

score 0 · Answer 3 · answered May 16 '12 at 20:09

Prefixing is the answer...you can alias your table names, and reference them throughout the query, like the following:

SELECT 
    pj.job_id, pj.job_title, pj.post_date, jp.full_name 
FROM 
    post_job pj, applied_jobs aj, jobseeker_personal jp 
WHERE
    emp_id='"+emp_id+"' "

score 0 · Answer 4 · answered May 16 '12 at 20:10

You need to (a) write join conditions, and (b) add table identifiers to field names that occur in more than one table.

In this case, I'd guess what you want to say is something like:

select p.job_id, p.job_title, s.full_name
FROM post_job p
join applied_jobs a on a.job_id=p.job_id
join jobseeker_personal s on s.js_id=a.js_id
WHERE a.emp_id=?

Side note: Your question has nothing to do with ASP.NET. It's a SQL question.

How to join 3 table in asp.net?

4 Answers4