Table Join and Objects

[joft]

Hi all,
(beforehand: I'm a nuBuilder beginner + not an SQL expert)

I've got one table which is joined with a second table on a certain identifier (primary key "id" in second table and a normal INT field within the first table). So the first table just contains a "reference" to an id in the second table - I guess that's a regular usage pattern?
I created a Form with an appropriate SQL-Select-Join-Query in the General tab. The first table also has a primary key "id" - which has nothing to do with the one from the second table. So I entered firsttable.id in the Primary Key field on the General tab, otherwise the identifier "id" would be ambiguous of course. So far everything works fine - the Form is shown correctly.

Now the problem is: Launching the Objects page I created for this Form fails, due to an unfortunate SQL syntax generated by nuBuilder:

SELECT * FROM `firsttable` WHERE `firsttable.id` = '296'
1054: Unknown column 'firsttable.id' in 'where clause'

After I tried this statement manually via command line, it was clear that the quotes around firsttable.id make MySQL (5.1 in my case) stop interpreting the usual table-dot-field syntax.

Now I'm a little bit unsure about what to do? How to fix the problem? Or did I do something wrong?

[massiws]

The right syntax in:

Code: Select all

SELECT * FROM `tableName` WHERE `tableName`.`fieldName` = `param`

In you case, you can use one of these two solutions:

Code: Select all

SELECT * FROM `firsttable` WHERE `firsttable`.`id` = `296` 
SELECT * FROM firsttable WHERE firsttable.id = '296'

If you use only one table in your query you can omit the name of the table:

Code: Select all

SELECT * FROM firsttable WHERE id = 296

I hope this helps.