Hi,
Attached is a bug and a question.
Regards,
Jonathan
user id: jberall4691
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
SELECT t.trans_id, t.trans_no, DATE_FORMAT(t.trans_date, '%e-%b-%x') as transDate,
a.name,
t.supplier_id , p.type, DATE_FORMAT(MAX(p.pod_time),'%e-%b-%x %T') as podTime
,t.void_customer ,
t.Type
FROM transaction t, pod p, address a
Where t.trans_id = p.trans_id AND t.void_customer=0 AND a.add_id=t.supplier_add_id
AND t.customer_id=3
GROUP BY t.trans_id HAVING MAX(p.type)>1
ORDER BY t.trans_date ASC
LIMIT 300
CREATE TABLE transaction(
trans_id INT PRIMARY KEY,
trans_no VARCHAR(20) NOT NULL,
trans_date DATE NOT NULL,
supplier_id INT,
supplier_add_id INT,
void_customer TINYINT DEFAULT 0,
type SMALLINT
);
CREATE TABLE pod
(co_id INT NOT NULL,
pod_time TIMESTAMP(14) NOT NULL,
type TINYINT NOT NULL,
trans_id INT NOT NULL,
user_id_pk INT,
reason_code VARCHAR(20),
notes VARCHAR(255),
PRIMARY KEY(co_id, pod_time, type, trans_id)
);
CREATE TABLE address
(add_id INT NOT NULL PRIMARY KEY ,
name VARCHAR(30),
dba VARCHAR(30),
add1 VARCHAR(30) NOT NULL,
add2 VARCHAR(30),
add3 VARCHAR(30),
city VARCHAR(25) NOT NULL,
state VARCHAR(2),
country VARCHAR(3) NOT NULL,
zip VARCHAR(11) NOT NULL,
phone VARCHAR(15),
fax VARCHAR(15),
active TINYINT UNSIGNED NOT NULL DEFAULT 0
);
The transaction table has a 1 to 1 relationship with the address table.
The transaction table has a 1 to many relationship with the POD table.
+++++ THE ISSUES ++++++
The MySQL is running on Windows 2000. I am accessing it with ODBC, my mid-tier is VB
6.0, and the front end is ASP.
1. I used to include a CASE MAX(p.type) WHEN 1 THEN 'In Progress' WHEN 2 THEN 'Sent'
ELSE 'Picked Up' END AS podType
but it won't return any results if I have a MAX(p.type) and a
DATE_FORMAT(MAX(p.pod_time),'%e-%b-%x %T') as podTime.
When I remove the MAX() from the MAX(p.type) I get back results but the p.type isn't
the MAX one.
I don't know if this is a bug or I'm not doing the SQL statement correctly. When I
type the code into MySQL direct it works beautifully but I can't get it to work in VB.
I tried dropping the Primary Key but this doesn't help. I tried making 2 tables for
the p.type but this doesn't work either. The problem occurs when two MAX() functions
were used on the same table.
MY WORK AROUND.
Create a mirror table as the POD. Each INSERT or UPDATE to pod I delete the record
from the mirror pod, then use the INSERT INTO mirror_pod SELECT statement ...,
MAX(pod_id) WHERE trans_id=(the values from the previous pod). Then when I do the
SELECT statements I just use the straight INNER JOIN with no group by functions.
2. Also I can't figure out how to get the last_insert_id()? I open a connection, then
update and I can't retrieve the Last-Insert_id(). can you direct me to the proper area.
3. Is it possible to use the count when using the limit x, y or are 2 db requests
required?
SELECT t.trans_id, t.trans_no, DATE_FORMAT(t.trans_date, '%e-%b-%x') as transDate,
a.name,
t.supplier_id , p.type, DATE_FORMAT(MAX(p.pod_time),'%e-%b-%x %T') as podTime
,t.void_customer ,
t.Type, COUNT(t.trans_id)
FROM transaction t, pod p, address a
Where t.trans_id = p.trans_id AND t.void_customer=0 AND a.add_id=t.supplier_add_id
AND t.customer_id=3
GROUP BY t.trans_id HAVING MAX(p.type)>1
ORDER BY t.trans_date ASC
LIMIT 50, 100;
If you need additional information please let me know.
Thanking you in advance,
Jonathan Berall
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
