MYSQL question

[fulster]

supyall,

here's the simple pb ... I'm looking for a simple solution, but 100% SQL (I use php for every day website .. but this time ... I had to implement a Delphi GUI 🙂

2 tables let's call them

customer [name,id_customer]
bill [ammount,date,id_bill,id_customer]

a customer can have 1 or more bills ....

I want to get a result with all customers aned their LAST bill only.

is that possible in SQL only ?

thanks a lot !

etienne

PS : I tried to GROUP but the ORDER clause is used after grouping ...

[[deleted]]

You cannot get the entire bill, but you can get a list of latest dates for the bills.

SELECT name, MAX(date)
FROM customer,bill
WHERE customer.customerid = bill.customerid
GROUP BY name

If your bill_id is an auto_increment field, then you can replace MAX(date) with MAX(bill_id) to get the highest bill_id for each customer, which translates to the latest bill.

[fulster]

yep of course ... but my interest is to get a bill ID back so I can act upon it .... I need a whole set of information not simply a date ...

but thanks anyway

[aspiers]

Below is one solution but it would need tweaking to work in MySQL as MySQL does not support subselects.

You would need to setup a loop driven from the following SQL :

select id_customer
from customer

which then passes id_customer into the following :

select name, amount, date, id_bill
from customer c,
bill b
where c.id_customer = b.id_customer
and c.date = (select max(date)
from customer
where id_customer)

but as MySQL doesn't support subselects you would have to break the subselect out into another query which then passes max(date) into the other query.

Clear ?!?!

Regards,

Andrew

[[deleted]]

I think you may want to rethink that query a little. well... maybe a lot :-)

The short answer Etienne: yes you can do it all in SQL only, but that would require stored procedures, which MySQL does not have (as with most other usefull features of relational databases)

[aspiers]

Looks alright to me. Whats wrong with it 😉

[[deleted]]

"and c.date = (select max(date)
from customer
where id_customer)"

'where id_customer' is true for every value of id_cusomer' except zero

but the main thing is:

"select id_customer
from customer

which then passes id_customer into the following :"

which you cannot do without a stored procedure or PHP/perl/whatever, and the question was 'can it be done inside SQL', and MySQL does not support stored procedures.

Otherwise, it's fine :-)

[aspiers]

It should work, I think my explaination was a bit lacking (rushed !!)

The sql query SELECT id_customer FROM customer would return a value (e.g. $custid) for each customer which using a loop could be passed into the other query :

select name, amount, date, id_bill
from customer c,
bill b
where c.id_customer = b.id_customer
and c.date = (select max(date)
from customer
where id_customer = '$custid')

et voila.

Theres no reason that that shouldn't work is there (apart from the fact that it uses a subselect).

[[deleted]]

"The sql query SELECT id_customer FROM customer would return a value (e.g. $custid) for each customer which using a loop could be passed into the other query :"

that's the problem, you can't build that loop INSIDE MySQL. You'd have to use external scripting/programming, and that was exactly what Etienne was trying to avoid.

FYI: you can do it without the subselect like this:

SELECT name, amount, date, id_bill
FROM customer c,
bill b
WHERE c.id_customer = b.id_customer
ORDER BY date DESC
LIMIT 1

[fulster]

Hey pals !!! many thanks for all these answers and this valuable discussion !

yes you're right ... the limit is a good solution except that I want this in 1 query and for many customers since I want to associate the result in a grid ...

I really don't think there's a one pass solution ....

maybe I would better wait for MySQL 4 🙂

many thanks to all again !

etienne

[[deleted]]

You can do it in one pass, but only with stored procedures, and with MySQL's current track-record for implementing usefull features, I doubt they will ever implement that.

It took them way to long to implement transactions, they took scanadously long to do rowlevel locking, and they still don't have functional indexes.

Look at postgreSQL, that does have stored procedures and a whole lot more (and it's free)

[fulster]

I read about PostGreSQL / MySQL ... I thought there was a real performances difference ...
most of all ... MySQL got cool packages for Win32 installations.
But that's beyond the scope of this topic I think ...
anyway any good discussion about MySQL / PostGreSQL is welcome 🙂))

[[deleted]]

MySQL usually gives better preformance, but that is only because it lacks all the essentials. No referencial integrety checks, no transactions, no functional indexes, no cascading delete, no stored procedures, no database fragmentation, no UNION, no OUTER JOIN, no INTERSECT, the list is long.

The lack of all those functions makes MySQL very fast. (that is why the creators left them out).

But because MySQL lacks so many usefull functions it is also very difficult to do complex queries. Consequently, many MySQL applications are quite simple, and simple things are nearly always fast.

If ever something complex needs to be done with MySQL, people cheat by doing the complex things in PHP. (we have all seen the posts about how to store an array in MySQL)

Once you have tasted the power of a real Rdbms, you'll find that there are a million more things you can do with PostgreSQL than with MySQL.

Unfortunately there are many people who only look at one criterium: speed.
They forget about data consistency, scalability, reliability etc, and just go for the fastest solution. (which will backfire sooner or later)

[fulster]

hi 🙂

you near to be right but your query missed something ...

what about 2 transactions per day ?

your query would return only a max(date) so ... how will it choose between the 2 ?

thanks anyway 🙂

etienne