strictly speaking, is this "field name ambiguous"?

searain

tb1 has fields categories_id, categoies_code, date_added
tb2 has fields categoires_id, categories_name

$sql="select tb1.categoires_id as categories_id, tb2.* from tb1, tb2 where tb1.categories_id=tb2.categories_id"

I make the alias categories_id as tb1.categoies_id. But at the same time, I also use a wild card character to select all fields from tb2, and the tb2 also has field called categories_id.

Is this sql statment ambiguous in the field name "categories_id"? I tested it, and it is fine on my database. But I would like to know if it is legtimate if following the strict SQL standard.

I know in this case, this sql is the same as ="select tb2.* from tb1, tb2 where tb1.categories_id=tb2.categories_id".

just try to find out if "select tb1.categoires_id as categories_id, tb2.* from tb1, tb2 where tb1.categories_id=tb2.categories_id" is a legitimate sql statement. is it?

Thanks!

bastien

wouldn't it have taken less time to test it rather than post it here?

searain

I did test it before I posted it.

But like I run into before, some sql statments I used in InterBase which let me pass but when I transfer the web site to the server which use FireBird instead InterBase, it gives me sql errors.

Some friends pointed out that FireBird did follow the standards more stictly than others.

(So even if it passes my tests doesn't mean it is legitimate STRICTLY SPEAKING.)

And I have a SQL complete reference book, but it is for newbie I think. Just basic info there.

leatherback

Hi,

In my opinion, SQL is interpreted differently in different database systems. So what may work in one, doesn't in an other, even though the language is correct. You would be better off trying to match the system on the dev. system and the live system, imho.

Other then that, I think you are asking for troublke if you use the same fieldnames in two different tables. Particularly when it comes to ID colums. In two tables you should not have the same ID colum. From an efficient database design that would mean that the two tables should be put together.
Unless they are not both identifier colums, and one nof them is therefor not an ID colum.

RossC0

I agree SQL is quite unstandardised! Especially, when it comes to more advanced SQL statements.

I was told that it isn't best practise to use:

SELECT *
FROM tblName

You should directly reference the field names and only return those that you need. If you need all of them returned - it is quicker in processing time to reference each field by name.

This also solves your ambiguity problem.

searain

Thanks!

I will follow the advices.

I am updating my codes and thought it was the good time to use the clear sql statements if I can.

Specially, after I run into the problems when I updated my database from InterBase to FireBird. (FireBird is the open source udpate for InterBase, so I think it wouldn't create problem, but it did.)