List:General Discussion« Previous MessageNext Message »
From:Rory McKinley Date:September 19 2005 7:03pm
Subject:Re: 1064 error
View as plain text  
Schalk Neethling wrote:
> Greetings
> 
> What might be causing the 1064 error in the following query?
> 
> SELECT mem_number, first_name, last_name, area_represented,
> joining_points + E-Model Challenge + SA Pro Model + Star Model Challenge
> + Eastern Cape Classic + SA Model Super Star + KZN Model GP + Mpumalanga
> GP + Glam Slam Model Challenge + Model Man Woman + SA Look of the Year +
> SA Face of the Year + KZN Classic + Eastern Cape GP + Western Cape
> Classic + Free State Classic + North West GP + Northern Province Classic
> + SA Model Open + Cover Search + Champion of Champions + Northern Cape
> Classic + Goldfields Model GP + Limpopo Classic + SA Model Portfolio +
> Top Model Challenge + Gauteng Model Classic + Year of the Model AS
> total_points
> FROM modelcup.ab_leader_board
> 
> All of the rows does exist in the table and all row names are correct.
> TYIA!
> 
Wow, a select statement like that is always going to cause you problems
- I know if I had to do it I would be sitting with typos from now until
Christmas. If I may suggest an alternative design, which will be a bot
more normalised and perhaps easier to work with ... making a suppostion
based on the above I think a table structure such as the following may
make lfe easier...


Table: models
___________________
|mem_number
|first_name
|last_name
|area_respresented
|
|

Table: events
___________________
|event_id
|event_name

Table: events_points
|event_id
|mem_number
|points

Would result in a query that looks like this:

SELECT a.mem_number, a.first_name, a.last_name, a.area_represented,
IFNULL(SUM(b.points), 0) AS total_points
FROM models LEFT JOIN events_points ON a.mem_number = b.mem_number
GROUP BY  a.mem_number, a.first_name, a.last_name, a.area_represented

Makes for a design that is easier to maintain (you could also treat your
joining points as another event). New events just get added as an entry
in the events table and are then referenced in the events_points table
rather than having to add a whole stack of columns ;).

Sorry, I know, answering an unasked question but I hope it helps....

Rory
Thread
1064 errorSchalk Neethling19 Sep
  • Re: 1064 errorRoger Baklund19 Sep
    • Re: 1064 errorSchalk19 Sep
  • Re: 1064 errorRory McKinley19 Sep