help with 'ambiguous' statement, request.

[CrazyMerlin]

Hey guys,

I am being returned an error saying that 'user_idx' below is ambiguous.

SELECT vg_msgb_topics.topic_idx
FROM vg_msgb_categories
LEFT JOIN vg_msgb_forums
USING ( cat_idx )
LEFT JOIN vg_msgb_topics
USING ( forum_idx )
LEFT JOIN vg_msgb_topics_read
USING ( topic_idx )
LEFT JOIN vg_user_data
USING ( user_idx )
WHERE vg_user_data.user_idx = '1'
AND vg_msgb_forums.view_access <= '10000'
AND vg_msgb_topics_read.read_idx IS NULL
AND vg_msgb_topics.topic_idx IS NOT NULL
ORDER BY vg_msgb_topics.post_date DESC
LIMIT 0 , 20

Can anyone see and obvious fault here, and what exactly does it mean by ambiguous??

Thank you for any and all help.

CM

[aussie girl]

It means that you have another column called user_idx probably as a foreign key somewhere, so put the table name in front of it... i.e tablename.user_idx

[CrazyMerlin]

Thx aussie_girl, but that just gives me another error saying there is an error i the SQL where I put the table name in

[chazzy]

can you show us the sql?

[mattyblah]

The problem is that you're using USING (column) instead of ON column = column. I'm guess that three tables or more share one or more of the columns in the USING statements. Can't reallyl help unless I know exactly what you're trying to do and all the columns in all the tables, or at least the ones that are being joined.

[CrazyMerlin]

thx guys!

problem was resolved by using a null right table and an ON clause...which returned all rows not matching the specified criteria.

it is for a forum, and needs to show all message not read by a user, in post date order, but only if the user has priveledge to read them.

thx again