Re: Postal code searching

Thanks for your very detailed response Mark. Most helpful. On Wed, Apr 25, 2012 at 10:14 AM, Mark Goodge wrote: > On 24/04/2012 17:24, Tompkins Neil wrote: > >> How about if I want to only return postal codes that are like W1U 8JE >> not W13 0SU. >> >> Because in

Re: Postal code searching

How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code On Tue, Apr 24, 2012 at 5:18 PM, Gary Smith wrote: > On 24/04/2012 17:16, Gary Smith wrote: > >> http://dev.mysql.com/doc/**r

Postal code searching

Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number of abbreviated postal codes like W1 WC1 WC2

JOIN giving duplicate records

Hi Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilityt

Design advice for hotel availability program

Hi, I'm in the process of starting to design a hotel booking system that will eventually consist of over 10,000 hotels all with different room types, rates and availability for different dates. My question is does anyone have any experience with regards the best way to store the daily rates. Am

Chinese characters not displaying in Workbench latest version

Does anyone know why Chinese characters are not displaying correctly in a replicated database on a slave machine ? I'm just getting square boxes. Thanks Neil

Re: Large insert query gives MySQL server gone away

But how do I calculate such size ? On Wed, Oct 12, 2011 at 2:38 PM, Reindl Harald wrote: > as big as you largest query > > Am 12.10.2011 15:30, schrieb Tompkins Neil: > > Do you have any recommendations as to what size this should be increased > to > > ? > > > &

Re: Fwd: Large insert query gives MySQL server gone away

Do you have any recommendations as to what size this should be increased to ? On Tue, Oct 11, 2011 at 12:23 AM, Angela liu wrote: > Yeah, I think adjusting max packet size may be helpful, remember change > that value in both server and client > > -- > *From:* Johnny W

Re: Index question

Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman wrote: > - Original Message - > > From: "Rik Wasmus" > > > > Depends on the data and usage, but

Re: MySQL Indexes

ery record >> in the that database (the test itself being expensive as infix finding is >> iterative). Perhaps you should consider this approach instead: >> <http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html> >> http://dev.mysql.com/doc/refman/5.5/en/ful

MySQL Indexes

Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For ex

Re: Slow query - please help

iPad > > On Oct 5, 2011, at 4:01 AM, Tompkins Neil > wrote: > > > Following my mail below, if anyone can help optimise the query further > that > > would be a great help. > > > > ------ Forwarded message -- > > From: Tompkins Neil > >

Fwd: Slow query - please help

Following my mail below, if anyone can help optimise the query further that would be a great help. -- Forwarded message -- From: Tompkins Neil Date: Wed, Oct 5, 2011 at 9:48 AM Subject: Re: Slow query - please help To: Johnny Withers Cc: "mysql@lists.mysql.com" I ju

Re: Slow query - please help

g where' '2', 'DEPENDENT SUBQUERY', 'hotels', 'ref', 'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code', 'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Usin

Re: Slow query - please help

onst', '267', '100.00', 'Using index condition; Using where' Thanks Neil On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers wrote: > Can you post the explain extended output of your query? > > Sent from my iPad > > On Oct 4, 2011, at 2:45 PM

Re: Slow query - please help

`), KEY `IDX_latitude` (`latitude`), KEY `IDX_longitude` (`longitude`), KEY `IDX_name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; On Fri, Sep 30, 2011 at 6:08 PM, mark carson wrote: > Hi > > You need version of mysql and table/key/index layout in o

Slow query - please help

Hi I've the following query : SELECT city_id, name, meta_title, meta_description, meta_keywords, country_code, link_text, folder_url, enabled, last_changed, nr_hotels, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.enable

Re: replication between two tables in same database

gt;> >> On 29 Sep 2011, at 18:16, Reindl Harald wrote: >> >> > so mysql is currently the wrong database for your project >> > sad but true, you can not have fulltext-search and innodb this time >> > >> > Am 29.09.2011 19:15, schrieb Tompkins Neil: >&g

MySQL 5.6

Hi Does anyone know when the production release of MySQL 5.6 will be out ? Thanks Neil

Re: replication between two tables in same database

: > you do not need any replication or myisam for > select * from table where field like '%input%'; > > for most workloads this is enough and you have not the problem > with stop-words, minimum input length and so on > > Am 29.09.2011 19:07, schrieb Tompkins N

Re: replication between two tables in same database

Yes, unless I can set-up some sort of replication between the two tables. On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald wrote: > please do NOT post off-list! > > so your only workaround is like '%whatever%' currently > > Am 29.09.2011 19:04, schrieb Tompkins Neil: >

replication between two tables in same database

Hi I've a Innodb and MyISAM table in the SAME database that I wish to replicate the data between the two because I need to use FULLTEXT searching on the MyISAM table. Is this possible ? If so how do I do it. Thanks Neil

Re: Query help

Thanks for the response. This is what I was after. Although, I am looking to find out the email addresses used to login from the same IP ? On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz wrote: > > >-Original Message- > >From: Tompkins Neil [mailto:neil.tompk...@googlema

Query help

Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil

Re: Unknown column 'users.users_id' in 'where clause'

Thanks, but how can I pass the current users value from the other query ? On Thu, Feb 3, 2011 at 4:22 PM, Simcha Younger wrote: > On Thu, 3 Feb 2011 13:55:36 + > Tompkins Neil wrote: > > > SELECT DISTINCT(away_teams_id) AS teams_id > > FROM fixtu

Re: SELECT Help

ect away_team_id as team_id, away_user_id as user_id, last_changed > from data) s1 > where s1.user_id = 3 > group by team_id, user_id; > > -Travis > > -Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Thursday, February 03, 20

Unknown column 'users.users_id' in 'where clause'

Hi, I've the following SELECT statement SELECT users.gamer_tag, UNIX_TIMESTAMP(users.created_on) AS time_registered, (SELECT fixtures_results.last_changed FROM fixtures_results WHERE (home_users_id = users.users_id AND home_teams_id = users_teams.teams_id) OR (away_users_id = users.users_id AND a

SELECT Help

Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18

Error Unknown column in 'where clause'

Hi I've the following query : SELECT players_bids.players_bids_id, players_bids.players_id, players_bids.bid_date, players_bids.bid_type, players_bids.bid_value, (SELECT SUM(IF(home_users_id = players_bids.users_id_from,home_manager_points,away_manager_points)) FROM fixtures_results WHERE (home_u

Fwd: If within If ?

itions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' Cheers Neil -- Forwarded message -- From: Tompkins Neil Date: Tue, Oct 19, 2010 at 2:01 PM Subject: If within If ? To: "[MySQL]" Hi, I've the following query : SELECT IFNUL

If within If ?

Hi, I've the following query : SELECT IFNULL(SUM(gate_receipts),0) AS gate_receipts, competitions.caption FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' However, I

COUNT question

Hi, I've the following query SELECT COUNT(players_id) AS players_count FROM players WHERE teams_id > 0 GROUP BY teams_id ORDER BY players_count DESC However, I've another field called original_teams_id and want to include the COUNT with players_count, when original_teams_id = teams_id Cheers Ne

Fwd: Backing up MySQL using PHPMyAdmin problem with UTF-8

MySQL Administrator 1.2.15 is fails with the same problem. Do anyone have any other suggestions ? -- Forwarded message -- From: Tompkins Neil Date: Fri, Oct 15, 2010 at 11:21 AM Subject: Re: Backing up MySQL using PHPMyAdmin problem with UTF-8 To: Michael Dykman Cc: "[

Re: Backing up MySQL using PHPMyAdmin problem with UTF-8

o your dump at the console. PHP does > not deal with UTF very well. > > - michael dykman > > > On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil > wrote: > > Hi, > > > > I'm using PHP MyAdmin to backup my MySQL database. The database is of > > ty

Fwd: Primary key not unique on InnoDB table

Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard Cc: "[MySQL]" Hi Travis, Thanks for you

Re: Backing up MySQL using PHPMyAdmin problem with UTF-8

d do your dump at the console. PHP does > not deal with UTF very well. > > - michael dykman > > > On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil > wrote: > > Hi, > > > > I'm using PHP MyAdmin to backup my MySQL database. The database is of > > type Inn

Backing up MySQL using PHPMyAdmin problem with UTF-8

Hi, I'm using PHP MyAdmin to backup my MySQL database. The database is of type InnoDB and encoding used is utf8_unicode_ci. The variables are set as follows : *MySQL connection collation: **utf8_unicode_ci* *MySQL charset: **UTF-8 Unicode (utf8)* *character set client: utf8* *character set conn

Re: Primary key not unique on InnoDB table

requirements. > > -Travis > > -Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, October 13, 2010 8:37 AM > To: [MySQL] > Subject: Primary key not unique on InnoDB table > > I've the following table. But why i

Re: Primary key not unique on InnoDB table

Shawn it is fine. I thought my primary key was just 1 field. On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL) < shawn.l.gr...@oracle.com> wrote: > On 10/13/2010 11:37 AM, Tompkins Neil wrote: > >> Shawn, sorry my error, I didn't realise I had two fields as the primar

Re: Primary key not unique on InnoDB table

s_id,default_teams_id). > > _Krishna > > On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> I've the following table. But why isn't the primary key unique, e.g. >> preventing duplicates if entered ? >> &

Re: Primary key not unique on InnoDB table

ent. Am I completely > wrong? > > -- > João Cândido de Souza Neto > > "Tompkins Neil" escreveu na mensagem > news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com... > Sorry Joao, I thought that was pretty standard to have a primary key with > auto_increme

Re: Primary key not unique on InnoDB table

w, if you have an auto_increment field it must be your > > single primary key. Am I wrong? > > > > -- > > João Cândido de Souza Neto > > > > "Tompkins Neil" escreveu na mensagem > > news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@m

Primary key not unique on InnoDB table

I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `se

Re: Backing up the InnoDB tables

The problem is I don't have any command line access, just direct MySQL access to the database tables. On Wed, Oct 13, 2010 at 1:19 PM, Suresh Kuna wrote: > use xtra backup > > On Wed, Oct 13, 2010 at 5:37 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: >

Fwd: Backing up the InnoDB tables

Would really appreciate some help or suggestions on this please, if anyone can assist ? Regards Neil -- Forwarded message -- From: Tompkins Neil Date: Tue, Oct 12, 2010 at 5:45 PM Subject: Backing up the InnoDB tables To: "[MySQL]" Hi On a shared MySQL server w

Backing up the InnoDB tables

Hi On a shared MySQL server with access just to my own database, what is the recommend backup methods and strategies for the InnoDB tables ? Cheers Neil

Re: innodb backup

I'm interested in InnoDB backups. Does anyone use PHPMyAdmin ? I've a MySQL server on a shared hosting server. Cheers Neil On Sat, Oct 9, 2010 at 3:21 AM, wrote: > Hello, > > Is there any good document for backup of InnoDB? > includes the increment backup and full backup. > > Thanks. > > > --

Fwd: Design advice

Wonder if anyone can help me ? -- Forwarded message -- From: Tompkins Neil Date: Tue, Oct 5, 2010 at 10:07 AM Subject: Design advice To: "[MySQL]" Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id pl

Query working on 5.1.43 and not 5.0.77

Hi, My hosting company are only running MySQL version 5.0.77 and at this current time are not planning on upgrading to at least my required version of 5.1.43. My query in which I have some problems is shown below : SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams

Design advice

Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed.

Re: Indexing question

few number of queries to be checked over. Will send them > tommorrow > > > On 4 Oct 2010, at 18:27, Gavin Towey wrote: > > Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE >> table \G output. Someone should be able to offer suggestions. >> >

MySQL DB Version

Account Number : uk600724 Dear Sir/Madam, The MySQL database version which you have supplied to us is version 5.0.77. However, it would appear that we require version to be at least 5.1.43. How can we get our MySQL db upgraded to this version or greater ? Regards Neil Tompkins

Re: Indexing question

Jörg Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you to advise if any changes need to be made ? Regards Neil On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe wrote: > Hi! > > > Neil Tompkins wrote: > > Thanks for your reply. So should we create individual indexes on each

Re: Indexing question

So if you have individual indexes for example field_1, field_2 and field_3 etc and then perform a search like WHERE field_1 = 10 AND field_3 = 'abc' This wouldn't improve the search ? You have to create a index for all possible combined field searches ? On Fri, Oct 1, 2010 at 9:35 PM, Gavin T

Re: SUM Top 10 records

Christoph, this SUMs all values ? On Tue, Sep 28, 2010 at 11:25 AM, Christoph Boget wrote: > > I've a basic table like and want to SUM the top 5 values. For example if > I > > have > > > > Any suggestions on how to achieve this ? > > > > SELECT SUM( rating ) as total_rating FROM my-table ORDER

SUM Top 10 records

Hi I've a basic table like and want to SUM the top 5 values. For example if I have id, rating 0, 10 1, 25 2, 5 3, 10 4, 50 5, 1 6, 15 7, 20 8, 9 I want my query to sum the values 4,50 1,25 7,20 6.15 0,10 Suming a value of 120 Any suggestions on how to achieve this ? Cheers Neil

Re: Advanced query help

Hi, I did try defining it before the IF statement, but still the same ? Cheers Neil On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman wrote: > At a guess, because you use @team in an if statement before you actually > define it. > > > On Sun, Sep 26, 2010 at 12:35 AM

Advanced query help

Hi, I've the following query SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,IF(@team <> teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM player

Fwd: Matrix Stats Login

Sorry, this message was sent in error, please ignore. -- Forwarded message -- From: Tompkins Neil Date: Fri, Sep 24, 2010 at 5:41 PM Subject: Matrix Stats Login To: "[MySQL]" User Pass

Matrix Stats Login

User fifaleagues Pass DrUch929eHek9Huwa7HAcH6wRUk5QaTr

Fwd: Query help please

I wondered if anyone can help me ? Do you need any further information ? Cheers Neil -- Forwarded message -- From: Tompkins Neil Date: Thu, Sep 23, 2010 at 9:49 AM Subject: Query help please To: "[MySQL]" Hi all, I've the following

Query help please

Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams

Re: SELECT WHERE IN help

hole point of an RDBMS is to *avoid* duplicate data :-) > > > On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of >> 3 only exists once in the table my_

Re: SELECT WHERE IN help

Meersman wrote: > If there are two, you will return two. > > > On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Hi >> >> With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I >> return t

SELECT WHERE IN help

Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil

Re: Encryption with MYSQL

e: > hex() and unhex() should do the trick in mysql (not base64, but same idea). > Those will transform every byte into a pair of hexadecimal digits - thus > limited to 0-9 and A-F - and reverse the process. > > > On Fri, Sep 17, 2010 at 2:13 PM, Tompkins Neil < > neil.tompk...@

SHA1 returns binary value

Hi Why when I run the command (MySQL 5.1) SELECT SHA1('abc'); is it returned as a binary value and not a string value ? Cheers Neil

Encryption with MYSQL

Hi, I need to encrypt a string like 'hello world', using a passkey. But I also need to be able to decrypt the encrypted phrase using the same passkey. I noticed in MySQL there are functions like AES_ENCRYPT()

Improve query

Hi, Regarding my query below, is there anyway in which this query can be improved ? SELECT t1.players_brought, t2.players_sold, t3.team_balance, t4.home_team_wages, t5.away_team_wages, (t3.team_balance+t2.players_sold)-(t1.players_brought+t4.home_team_wages+t5.away_team_wages) AS team_balance, t6

Table design help

Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the finan

Re: Query SUM help

1:27 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Any help would be really appreciated ? >> >> >> >> ------ Forwarded message -- >> From: Tompkins Neil >> Date: Wed, Sep 8, 2010 at 5:30 PM >> Subject

Fwd: Query SUM help

Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: "[MySQL]" Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS m

Query SUM help

Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_ca

Re: Calculating table standings

ience designing > these kinds of applications, so maybe some others might have better advice > for you. If you find your reporting is too slow or it is too awkward to > query this table twice and union the results, then you might want to add a > summary table. > > -Travis >

Re: Dup Key Error Messages

Hi You have a PRIMARY KEY of just the id field like PRIMARY KEY ( `id` ). You need both ip and type to be the primary key Cheers Neil On Mon, Sep 6, 2010 at 2:02 PM, Thorsten Heymann wrote: > Hi, > > First, I'm new to this mailing list, hopefully I'll do my post the *right* > way. > > I've a p

Re: Query help

, Ananda Kumar wrote: > Also, can u please lets u know the value's in this table. > Just one row, an example would do. > > regards > anandkl > > > > On Mon, Sep 6, 2010 at 5:35 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >>

Re: Query help

These two fields home_goals and away_goals Cheers Neil On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar wrote: > Tompkins, > Which field stores the result of matches. > > regards > anandkl > > On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil < > neil.tompk...@goo

Query help

Hi, I've the following fields within a table : fixtures_results_id home_teams_id away_teams_id home_goals away_goals home_users_id away_users_id >From this I want to extract the following type of information if the home_users_id or away_users_id = 1 : total number of games games number of games

Re: Best method to keep totals

Just another quick question - following my initial question regarding the best method to keep totals, I wondered if I should adopt a table to record team league standings like based on each result like team_id home_win home_draw home_loss home_goals home_conceded away_win away_draw away_loss away_

Re: Best method to keep totals

; Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > E-mail: je...@gii.co.jp > Web site: www.the-infoshop.com > > >-Original Message- > >From: Tompkins Neil [mailto:neil.tompk...@goo

Best method to keep totals

Hi, I'm developing a system whereby a manager gets assigned points based a certain number of factors which are saved alongside a football result. However, I also want to keep a total for each manager. My question is it best to just have a query that uses SUM to total the managers points gained f

Re: Database design help

I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep

Fwd: Database design help

Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/res

Re: MySQL SUM on two columns

Hi, I also wondered, if there is any way to LIMIT the products.rating for each language to say the top 25 ? Cheers Neil On Tue, Aug 31, 2010 at 12:11 PM, Tompkins Neil < neil.tompk...@googlemail.com> wrote: > Perfect. Exactly what I was looking for. > > Cheers John. > > R

Re: MySQL SUM on two columns

.products_id = products.products_id > WHERE products.enabled = 1 > AND products_sales.language IN ('EN','ES') > GROUP BY products_sales.language > > > Will give the sum for each language. > > On 31 August 2010 11:42, Tompkins Neil wrote: > >> Hi, >> >> I&#x

Re: MySQL SUM on two columns

ROLLUP grouping > http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html > > see second example on that page. > > regards, > m > > -----Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Tuesday, August 31, 2010 11:43 AM >

MySQL SUM on two columns

Hi, I've the following basic SUM for our products based on a rating. SELECT SUM(products.rating) AS products_rating FROM products_sales INNER JOIN products ON products_sales.products_id = products.products_id WHERE products.enabled = 1 AND products_sales.language = 'EN' This works fine, however

Re: Calculating table standings

t;,1 as away >,0 as won_home >,0 as draw_home >,0 as lost_home >,0 as scored_home >,0 as conceded_home >,if(away_goals > home_goals, 1, 0) as won_away >,if(away_goals = home_goals, 1, 0) as draw_away >,if(away_goals < home_goals, 1, 0) a

Table design question

Hi, I'm creating a application which hosts football matches and I want to record the player appearances, goals etc. I was thinking of having a record for each player as follows : appearance_id season_id player_id team_id competition_id appearance goals yellow_card red_card date_played Is this t

Re: Retrieve three columns in sub query

l Message----- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, August 25, 2010 1:23 PM > To: [MySQL] > Subject: Retrieve three columns in sub query > > Hi > > Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query

Re: Retrieve three columns in sub query

¢ndido de Souza Neto > > "Tompkins Neil" escreveu na mensagem > news:aanlkti=djkujcsg=kf29sjsp0yllhmhb02mqzdvzd...@mail.gmail.com... > > Hi > > > > Is it possible in MySQL 5.1 to retrieve three columns in a select > > sub-query > > like below : > >

Retrieve three columns in sub query

Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age > 10 ORDER BY RAND() LIMIT 1 I've looked and tried eve

Re: Calculating table standings

y >,if(away_goals < home_goals, 1, 0) as lost_away >,away_goals as scored_away >,home_goals as conceded_away > from matches) s1 > group by seasons_id, team_id; > > -Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: T

Re: SUM value like 10,23,15,10

-) > > You could maybe build a stored procedure, or do it in the app; but it's > gonna be code either way. > > > On Tue, Aug 24, 2010 at 3:58 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Yeah these values are held with a varchar field. &g

Re: SUM value like 10,23,15,10

values, you can do a group by that field and select the count() of it. > > > On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Hi >> >> In MySQL is it possible to SUM a field which contains like 10,23,15,10. >> Th

SUM value like 10,23,15,10

Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil

Calculating table standings

Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based

Re: Fixture List generation using MySQL

a condition to prevent those > rows from showing up: > > > select * from teams t1 JOIN teams t2 ON t1.id!=t2.id; > ++----+ > | id | id | > +++ > | 2 | 1 | > | 3 | 1 | > | 4 | 1 | > | 1 | 2 | > | 3 | 2 | > | 4 | 2 | > | 1 | 3 | &g

Re: Fixture List generation using MySQL

t; select a.id,b.id from tbl a join tbl b on a.id> union >> select a.id,b.id from tbl a join tbl b on a.id>b.id; >> >> PB >> >> - >> >> >> On 8/19/2010 9:12 AM, Tompkins Neil wrote: >> >>> Hi, >>> >>> I'm tasked with

Fixture List generation using MySQL

Hi, I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Thanks for any input. Regards Neil

  1   2   >