CODEWITHSUNDEEP
mysql
Ivan
Ivan

Reputation: 15912

Avoid ambiguous condition in MySQL

I'm working on a very limited MySQL environment (all MySQL calls are passed as array parameters). The problem is I don't know how to resolve an ambiguos condition.

In this environment all MySQL calls take this form:

SELECT value1,value2,...,valueN 
FROM table1,table2,...,tableN 
WHERE cond1 OP cond2 OP ... OP condN

That's not a problem until you have the same column names in table1 and table2. Imagine table1 has columns {a,b,z} and table2 has column names {c,d,f,z}. I can do this:

SELECT * 
FROM table1, table2 
WHERE a='3' AND table1.z='5'

Perfect but, one of the limitations is that I can't use table1.z format, just z='5' that produces a collision.

So, the question is: is there any way to prevent this ambiguosity without the use of table name prefix in the condition part? (for example, it will be great if there is a way to use only part of table2 or say that table1 has priority in case of ambiguity).

Upvotes: 0

Views: 1024

Answers (1)

Matthew
Matthew

Reputation: 10444

There is no way to avoid a collision if you can't qualify which table "myColumn" comes from.

Your constraints seem kind of silly... You're basically asking for valid result when you assert that you cannot write valid SQL....

Perhaps instead of SELECTing from table1, you would SELECT from a nested subquery in which you aliased the column names...

For example:

if table1 and table2 both have column "myColumn"
Rather than:

 SELECT 
    * 
 FROM 
    table1,table2 
 WHERE myColumn = @value -- produces collision!

Could you say:

SELECT 
    * 
FROM 
    (SELECT myColumn AS foo FROM table1) newTable1, 
    (SELECT myColumn AS bar FROM table2) newTable2
WHERE 
    foo = @value

This way you're not fully qualifying the tables in the outer WHERE clause but you are re-aliasing the columns inside the subqueries (thus making them into different names for the outer query)

This seems like a roundabout exercise though

EDIT: http://thedailywtf.com/Articles/SQL-Sentences.aspx

"MySQL sentences?"

Upvotes: 3

Related Questions