500 Error, ambiguous column

[arcadeicons]

I'm getting a 500 error when I try and view list data in the back end, same if I create a new list from this table. The table is shared with another list that has a few joins, however that list doesn't display any errors when the data is viewed in the back end. I checked the primary key and AI settings in the list that is having the issue.

Fabrik has generated an incorrect query for the list Tournament Scoreboard: <br /><br /><pre>Column 'name' in field list is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `submit_score`.`collectiontype` AS `submit_score___collectiontype_raw`, CONCAT_WS('', #__jem_venues.venue, " , ", city, " , ", state, " , ", country) AS `submit_score___collectiontype`, `submit_score`.`eventID` AS `submit_score___eventID_raw`, `#__jem_events`.`title` AS `submit_score___eventID`, `submit_score`.`gameID` AS `submit_score___gameID_raw`, CONCAT_WS('', (SELECT name FROM #__djc2_items WHERE #__djc2_items.id = `select_game`.gameID)) AS `submit_score___gameID`, `submit_score`.`date_time` AS `submit_score___date_time`, `submit_score`.`date_time` AS `submit_score___date_time_raw`, `submit_score`.`id` AS `submit_score___id`, `submit_score`.`id` AS `submit_score___id_raw`, `submit_score`.`gamescore` AS `submit_score___gamescore`, `submit_score`.`gamescore` AS `submit_score___gamescore_raw`, `submit_score`.`playerID` AS `submit_score___playerID_raw`, `submit_score_0`.`player` AS `submit_score___playerID`, `submit_score`.`player` AS `submit_score___player_raw`, CONCAT_WS('', name, ' (', username, ')') AS `submit_score___player`, `submit_score`.`updated_by` AS `submit_score___updated_by_raw`, `#__users_0`.`name` AS `submit_score___updated_by`, `submit_score`.`created_by` AS `submit_score___created_by_raw`, `#__users_1`.`name` AS `submit_score___created_by`, `submit_score`.`create_time` AS `submit_score___create_time`, `submit_score`.`create_time` AS `submit_score___create_time_raw`, `submit_score`.`update_time` AS `submit_score___update_time`, `submit_score`.`update_time` AS `submit_score___update_time_raw`, `submit_score`.`tournID` AS `submit_score___tournID`, `submit_score`.`tournID` AS `submit_score___tournID_raw`, `submit_score`.`id` AS slug , `submit_score`.`id` AS `__pk_val` FROM `submit_score` LEFT JOIN `#__jem_venues` AS `#__jem_venues` ON `#__jem_venues`.`id` = `submit_score`.`collectiontype` LEFT JOIN `#__jem_events` AS `#__jem_events` ON `#__jem_events`.`id` = `submit_score`.`eventID` LEFT JOIN `select_game` AS `select_game` ON `select_game`.`id` = `submit_score`.`gameID` LEFT JOIN `submit_score` AS `submit_score_0` ON `submit_score_0`.`id` = `submit_score`.`playerID` LEFT JOIN `#__users` AS `#__users` ON `#__users`.`id` = `submit_score`.`player` LEFT JOIN `#__users` AS `#__users_0` ON `#__users_0`.`id` = `submit_score`.`updated_by` LEFT JOIN `#__users` AS `#__users_1` ON `#__users_1`.`id` = `submit_score`.`created_by` LIMIT 0, 10</pre>

 

[cheesegrits]

Looks like you are using a "CONCAT" label in several places on join elements. You need to be more specific with your field names, and use {thistable}.name. That is, {thistable} literally. Which we will replace with the alias of the table you are joining.

-- hugh

 

[arcadeicons]

ok, here is my concat, how can I write this in a better way...

(SELECT name FROM nfojm_djc2_items
WHERE nfojm_djc2_items.id = {thistable}.gameID)

I have three lists set up like this...

List: Submit Score <--- throws error ONLY when trying to view list data in back end
Table: submit_score

List: Global Scoreboard <--- works no issues
Table: submit_score
Left Joins:
#_users (has a column called 'name')
#_jem_venues
#_djc2_items (has a column called 'name')
add_tournament

List: Tournament Scoreboard <--- throws error ONLY when trying to view list data in back end
Table: submit_score

I dont have a choice of naming for the column as I am referencing a field from another application's table. I can certainly see two columns in two different tables named 'name' neither of which I have control over for naming convention.

 

[cheesegrits]

Just fully name all your fields, so use nfojm_djc2_items.name, the same way you did for nfojm_djc2_items.id.

-- hugh

 

[arcadeicons]

THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!!

fixed

 

[cheesegrits]

Do feel free to express your gratitude in the form of a paid support sub for a month or two. I appreciate the THANK YOUs, but they don't pay the bills.

-- hugh

 

[arcadeicons]

Yep I bought a month, but the way I am going I think I'm going to need a little bit more than that lol

 

[cheesegrits]

Hmmm, I only see "Supporter" by your name, not "Standard" or "Pro".

-- hugh

 

[arcadeicons]

I thought that's what that meant. Anyway, yep I'm going to sign up today for a sub, you've been beyond helpful and I need MOWAH help already!

 

[cheesegrits]

The "supporter" thing doesn't actually get you access to the paid forums. It's more of a "tip".

I see you got a Standard sub. Thanks.

I also see a post which you then seem to have removed. I think the answer to that one may be creating a MySQL view, it's not really a "pivot".

-- hugh