Can you have more than one id in a select statement

[gokeeffe]

How come the below select statement will work but when I add a second id to the select statement
I keep getting an ambiguous error for that id.
Is it not possible to select more than 2 id's in a select statement.

Regards
Graham


---------------------------------- Works -------------------------------

SELECT
id_grind,
interest_name,
subject_name,
level_name,
county_name,
town_name,
contact_name,
contact_number,
qualifications,
experience,
additional_locations,
availability,
rate_per_hour,
additional_number,
email_address,
additional_information,
username
FROM
grind_record,
interests,
subjects,
levels,
county,
town,
users,
premium
WHERE
county.id_county = users.id_county
AND
town.id_town = users.id_town
AND
interests.id_interest = grind_record.id_interest
AND
subjects.id_subject = grind_record.id_subject
AND
levels.id_level = grind_record.id_level
AND
users.user_id = grind_record.user_id
AND
premium.id_premium = users.id_premium
AND
interests.id_interest = 2


---------------------------------- does not work Work -------------------------------

SELECT
id_grind,
user_id,
interest_name,
subject_name,
level_name,
county_name,
town_name,
contact_name,
contact_number,
qualifications,
experience,
additional_locations,
availability,
rate_per_hour,
additional_number,
email_address,
additional_information,
username
FROM
grind_record,
interests,
subjects,
levels,
county,
town,
users,
premium
WHERE
county.id_county = users.id_county
AND
town.id_town = users.id_town
AND
interests.id_interest = grind_record.id_interest
AND
subjects.id_subject = grind_record.id_subject
AND
levels.id_level = grind_record.id_level
AND
users.user_id = grind_record.user_id
AND
premium.id_premium = users.id_premium
AND
interests.id_interest = 2

 

[hoinz]

Imho the problem is not a second id;
the problem is that user_id is ambiguous, because there are two tables with a column like this, and which one shall be selected, users.user_id or grind_record.user_id ?
Well, you may argue that this shouldn't matter, as you stated 'users.user_id = grind_record.user_id' later on, but your database will not be impressed by this sophistry.
Better you give way and say which one you want:

SELECT
id_grind,
users.user_id,
interest_name,
...

hope this helps

 

[gokeeffe]

That was the problem Tks it worked