LEFT JOINing three tables together...

[Farel321]

I'm using MySQL Version 5.0.51

I'm trying to combine three tables into one, however it requires multiple "ON" conditions.

I'll start by stating the tables.

Table: keywords
project_id
keyword
bucket_hcc_id
bucket_custom_id
monthly_count

Table: buckets_hcc
bucket_hcc_id
bucket_hcc_title

Table: buckets_custom
buckets_custom_id
project_id
buckets_custom_title

Now i'm trying to combine all three of these tables into 1. So i'm combining keywords with buckets_hcc based on the bucket_hcc_id then i'm combining keywords with buckets_custom based on project_id AND buckets_custom_id. The last condition is that keywords project_id is equal to whatever project_id I specify.

Therefore i come up with the following query:
SELECT * FROM keywords
LEFT JOIN buckets_hcc USING (bucket_hcc_id)
LEFT JOIN buckets_custom ON keywords.bucket_custom_id=buckets_custom.bucket_custom_id AND keywords.project_id=buckets_custom.project_id
WHERE project_id='$my_project_id'
ORDER BY monthly_count DESC

I apologize for the little bit of php ($my_project_id).

Only problem is I get the following error: Column 'project_id' in where clause is ambiguous. I believe that this is because my buckets_hcc does not have a project_id so if that's the case how do I go about combining these tables based on my conditions?

[skywalker2208]

The error means you have more then one table with the same field name and it doesn't know which want it goes to. You need to specify which table it goes to. Either by using the table name with a . like keywords.project_id or using table aliases. When state the table add the table alias afterwards like "keywords k". Then in your query you just have to do k.project_id.

[Farel321]

Thank you very much, that solves my problem completely!

You're a life savor =]