Re: Strange results from a query

2004-10-21 Thread Stephen E. Bacher 
FYI, I ran the same scenario under MySQL 3.23.58 and it produced the
results you wanted/expected:

+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   4 |   0 |
|2 |   0 |   6 |
+--+-+-+

So could it be a bug in 4.0.18?

 - seb

---

David Griffiths [EMAIL PROTECTED] wrote:

One of our developers came to me yesterday with strange results from a 
query. I've created a simple version of the example. I've pasted the 
table definitions at the bottom if someone really needs to see them. 
This is on mysql 4.0.18.

insert into master (col1) values (1), (2);

insert into sub (col1, a, b, c) values
(1, 'a', null, '2'),
(1, 'a', null, '2'),
(2, null, 'b', '3'),
(2, null, 'b', '3');


mysql select m.col1,
- sum(s1.c) as 'A-count',
- sum(s2.c) as 'B-count'
- FROM master m
- left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null)
- left join sub s2 on (m.col1 = s2.col1 and s2.b is not null)
- group by m.col1;
+--+-+-+
| col1 | A-count | B-count |
+--+-+-+
|1 |   8 |NULL |
|2 |NULL |  12 |
+--+-+-+
2 rows in set (0.00 sec)

In case it's not obvious, the count for the column marked A should be 
4, not 8. And for B, it should be 6, not 12. The database seems to be 
iterating through the table twice.

If one of the outer-joins is removed, the results are correct. I would 
hazard a guess that if a third column existed in master/sub, and a third 
left-join was added, A would go to 12, and B would go to 16. Each 
outer join seems to spawn a new iteration through the data.

My question is why, and what would be the strategy to avoid this?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: need help for a query

2004-08-24 Thread Stephen E. Bacher 
select name 
from mytable a 
where changedate  
 (select  changedate 
 from mytable b 
 where a.name=b.name 
 and a.changedate != b.changedate);

or:

select name
from mytable a
where exists 
  (select * from mytable b
   where a.name=b.name
   and a.changedate  b.changedate);

Or, especially if your MySQL is earlier than 4.1 and you
don't have subqueries:

(also untested)

select t1.name
  from mytable t1, mytable t2
 where t1.name = t2.name
   and t1.date = 'd1' and t2.date = 'd2' 
   and t1.changeDate  t2.changeDate

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Figuring out the ranking position of an item on a table given its partnumber

2004-08-24 Thread Stephen E. Bacher 
C.F. Scheidecker Antunes [EMAIL PROTECTED] wrote:

I have a table that has a PartNumber and the Quantity of Items sold for 
each partNumber, that is:

PartNumber
Qty

I need to get its sales raking given its PartNumber, that is. So if I 
order the table by Qyt in descending order the first
record will be the partNumber that sold the most. If I want to know what 
is 123 raking position according to that.

Is there any easy way to do it?

I think you may be looking for something like this:

set @a = 0;
select @a := @a+1 as rank, PartNumber from my_table order by Qty desc;

Unfortunately, selecting only the record with rank 123 doesn't work very
well with this query, so you may want to load a temporary table and then
select from that table:

set @a = 0;
create temporary table foo
 select @a := @a+1 as rank, PartNumber from my_table order by Qty desc;
select * from foo where rank = 123;

(I haven't addressed the question of how you deal with records where
the quantity is the same.  Left as an exercise for the reader. :-)

 - seb


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL question, SELECT DISTINCT

2004-08-17 Thread Stephen E. Bacher 
I had a similar problem, but my criteria for selecting the
value of f1 was different; it's a date field and I wanted
only the rows with the most recent date value in that field,
so only the latest of otherwise identical entries got inserted.

I ended up doing something like this:

create temporary table temp_table (
 t_f1 date,
 t_f2 varchar(100) unique,
 t_f3 varchar(100) unique
);

insert ignore into temp_table
 select f1,f2,f3 from new_table
 order by f1 desc;

insert into original_table
 select * from temp_table;

If there is a better way to do this, I would like to
know about it.

 - seb


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: In Statement Help

2004-08-03 Thread Stephen E. Bacher 
Andrew Dixon [EMAIL PROTECTED] wrote:

Hi Everyone.

I require some help with an IN statement I'm trying to get working. I have
inherited a database from someone else in which there is a table with
project information and a field containing which geographic regions the
projects relate. As a single project can relate to multiple geographic
regions the person who created the database create this field as a varchar
field and populated it with a pipe (|) delimited list of ID's of the
geographic regions (ID from another table). An example of the data in this
field is:

1|5|9|10|12

Or 

1

Or

9|5|7

I have been asked to write a query to return all the projects for a single
geographic region. This is what I have come up with:

SELECT project_title 
FROM   projects
WHERE  9 IN (REPLACE(geo_region,'|',','))
ORDER BY   project

[...]

I would suggest something like

WHERE CONCAT('|',geo_region,'|') LIKE '%|9|%'

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Question

2004-08-03 Thread Stephen E. Bacher 
[EMAIL PROTECTED] wrote:

My connection to the mysql is correct, that is host, username, password,
however when I click the link of form so that it will pull out data from 
mysql database, I see the php code of the form, any guidance, please.

This is a Web server configuration issue.

Assuming you are running Apache, your httpd.conf file should include
lines similar to the following:

---

AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps
LoadModule php4_modulelibexec/libphp4.so

---

Make sure they do, and don't forget to stop and restart the httpd daemon
to make them take effect.

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: deleting duplicates from table

2004-07-19 Thread Stephen E. Bacher 
Wesley Furgiuele [EMAIL PROTECTED] wrote:

I think one way to solve it would be to move your data to a new copy of 
your table.  Assuming record_ref and keyword are separate fields...

CREATE TABLE newtable SELECT * FROM oldtable GROUP BY CONCAT( 
record_ref, keyword );

Or, more simply and reliably:

CREATE TABLE newtable SELECT DISTINCT * FROM oldtable;

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query to see if db exists...........mysql_query( myQuery)

2004-05-28 Thread Stephen E. Bacher 
On Thu, May 27, 2004 at 03:59:46PM -0400, Bono, Saroj AA R62 wrote:
 I am going to use mysql_query() and want to find out if a certain
 database  exists. If  mysql_real_connect() fails there are many errors
 that could account for this. The database may exist , and I cant take
 the error returned from the failed  query to mean the db isnt there and
 should be created. So what sort of query can I use to see whether the db
 exists? I thought of USE myDb but once again a failed mysql_query cant
 rule out other factors that can cause the failure. Any suggestions? 

Are you using PHP?  You can use the mysql_list_dbs() function:

http://www.php.net/manual/en/function.mysql-list-dbs.php

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: building 4.0.20 on Solaris2.7-x86 - make errors after configure

2004-05-28 Thread Stephen E. Bacher 
Not sure if this will help, but we recently ran into a problem
building MySQL on a Solaris 9 system - the build failed at the
point of trying to make readline because of a missing file
Makefile.in.  It turned out that automake wasn't available,
and automake is required to do the make.  When we installed
automake and tried the MySQL configure again, all was fine.

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select on Multiple columns

2004-05-19 Thread Stephen E. Bacher 
Rocco Castino [EMAIL PROTECTED] writes:

 I would like, for example, to get the records starting from row number 6
 (without, of course, working with the primary key, where the numbers could
 not necessarily be sorted as here):
 +++---+---+
 | id_example  | x_uno  | x_due | x_desc  |
 +++---+---+
 |  6 | 2004-01-02 | 3 | a |
 |  7 | 2004-01-02 | 4 |   |
 |  8 | 2004-01-03 | 2 | 0 |
 |  9 | 2004-01-03 | 5 |   |
 +++---+---+
 In fact, a select with the following syntax:
 SELECT * FROM `example` where x_uno=20040102 and x_due = 3 ORDER BY x_uno
 ASC, x_due ASC
 does not return me row number 8 (as it is 23 on column x_due).

Another solution is a concatenation of the x_uno and x_due columns,
something like:

 SELECT * FROM example where concat(x_uno,x_due) = '200401023'

If x_due is an integer that can be more than 1 digit in length, then you may
need to be more creative in your concatenation.  You might even do
something like

 SELECT * FROM example where (100*x_uno + x_due) = 2004010203

which will work only for values of x_due less than 100.

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Importing Text Files in phpMyAdmin vs the Infamous

2004-05-11 Thread Stephen E. Bacher 
 I have a php website connecting to mysql database.
 How can i have a user export his database through
 the webpage to csv format?
 I would like the user to have a button he can press
 that would pop up a
 save as screen so he can save his database.

The PHP code to do this would look something like this:

 // -- Do not send any HTML before this point -- //

 header(Content-type: application/vnd.ms-excel);
 header('Content-Disposition: attachment; filename=myfile.xls');
 header(Content-Transfer-Encoding: binary);

 $r1 = // -- do query here -- //;
 $nf = mysql_num_fields($r1);
 for ($i=0; $i$nf; $i++) {
  $x = mysql_field_name($r1,$i);
  echo '' . str_replace('','',htmlspecialchars($x)) . '\t';
 }
 echo \n;
 while ($a1 = mysql_fetch_array($r1)) {
  for ($i=0; $i$nf; $i++) {
   $x = $a1[$i];
   echo '' . str_replace('','',htmlspecialchars($x)) . '\t';
  }
  echo \n;
 }

Hope this helps.

 - seb


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Use of AVG in where clause

2004-05-05 Thread Stephen E. Bacher 
Julien Martin [EMAIL PROTECTED] wrote:

I have a sql query as follows:

**
SELECT 
   DB_ESTABLISHMENT_NAME,
   AVG(DB_GRADE)

FROM   
   ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON
ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID

WHERE 
   AVG(DB_GRADE)  2

GROUP BY 
   ES.DB_ESTABLISHMENT_ID
;
**
and I get the following error:

ERROR : Invalid use of group function

Can anyone tell me how to use a function in the where clause?

You need to use HAVING instead of WHERE:

 SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE)
 FROM   ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR
 ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID
 GROUP BY ES.DB_ESTABLISHMENT_ID
 HAVING AVG(DB_GRADE)  2
 ;
 
 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [PHP-DB] lamer noob with repeat question

2004-05-04 Thread Stephen E. Bacher 
$result_insert = @mysql_query ($query_insert) or die(you suck! $mysql_error);

Try mysql_error() instead of $mysql_error.

 - seb

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Another Trailing Spaces Issue

2004-04-30 Thread Stephen E. Bacher 
If the string is theString + space and another row has an entry with
theString (+ no space), the query returns BOTH rows.

Is there a way to get around returning the latter entry?

You could do something like:

select stuff from mytable where stuff = 'foo ' and length(stuff) = 4;

-- 

Steve Bacher
Draper Laboratory
Cambridge, MA, US

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reports

2004-04-30 Thread Stephen E. Bacher 
Is there a way to do report writing from Mysql databases? I want to 
transfer all the records from Mysql to a file. i used the Into OUTFILE but it 
doesnt display properly.I want to diplay it properly like records or reports. 

I wrote a basic Perl script to prettify the output of a MySQL query that
gets outputted in tab delimited format.  So you would run MySQL with the
-B and -r flags and pipe the output through this script:

--- cut here ---
#!/usr/bin/perl

# input: a tab delimited file, as might come from a MySQL query

# output: the same data, arranged so that each column accommodates
# the maximum length of the data in that column

$i = 0;
while () {
 chomp;
 # s//\\/g;
 @data = split(\t);
 @sizes = map(length,@data);
 $i++;
 @sizevec[$i] = join(\t,@sizes);
 @datavec[$i] = $_;
 for ($j=0;$j[EMAIL PROTECTED];$j++) {
  $maxsize[$j] = $sizes[$j] if $maxsize[$j]  $sizes[$j];
 }
}
for ($i=0; $i@datavec;$i++) {
 @sizes = split(\t,@sizevec[$i]);
 @data  = split(\t,@datavec[$i]);
 for ($j=0;$j[EMAIL PROTECTED];$j++) {
  $_ = $data[$j];
  $l = length;
  $m = $maxsize[$j];
  $padding =   x ($m - $l);
  if (/^[0-9. -]*$/) { print $padding . $_ ; }
  else   { print $_ . $padding ; }
  print  ;
 }
 print \n;
}

--- ereh tuc ---

-- 

Steve Bacher
Draper Laboratory
Cambridge, MA, US

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]