Help with SQL [INNER JOIN]

[Anon]

ello,

For the same problem that I posted which dated 2000-08-15
"Help with SQL [PHP and MySQL]".
Does anyone, has a high knowledge of INNER JOIN.
As I stated in my post that my SQL is not to good.

select m.material_id
from materials m
INNER JOIN (materialsKeywords mk (INNER JOIN keywords k
on (k.keyword_id = mk.keyword_id)))
where word = "central sulawesi" and word = "culture"

I have attempted the above SQL many times, just getting no where!
Any help would be most appreciated.

Annalise
[e-mail]

[Anon]

You don't have any join between the table materials and the other tables materialsKeywords and keywords

Also, where is word coming from? and how can it be equal to two different values at once. I don't think I can decipher what you are trying to do. Could you explain?

[Anon]

Unless this is a syntax I'm not real familiar with, you might want to try standard join syntax as follows:

Table names: A, and B
Common filed: id

select * from A, B where A.id=B.id AND (other various parts of where clause...) order by something;

The A.id=B.id is the join part of the where clause.

[Anon]

OK, I'm guessing you're making a travel webpage, searching for material on 'culture' (materialsKeyword) relating to 'Central Sulawesi' (keywords). I can't precisely infer the relationship between [materials] and [materialsKeywords], however, since the material keywords are separated from the materials table, I'll assume you can have the same material under different keywords. Here's my guess at your table structure.

materials

material_id
(stuff)

materialsKeywords

keyword_id
material_id
word

keywords

keyword_id
word

SELECT materials., materialsKeywords., keywords.* from materials
INNER JOIN materialsKeywords
ON materials.material_id = materialsKeywords.material_id
INNER JOIN keywords
ON materialsKeywords.keyword_id = keywords.keyword_id
WHERE materialsKey.word = 'culture' AND keywords.word = 'Central Sulawesi'

Not the most efficient table structure or retrieval method, but should work.

Multiple joins are processed in order, so the second join is applied to the table created by the first join, and the criteria is applied to the table created by the second join. Good luck.