[PHP-DB] MySQL Query Help
Hello, I'm unsure how to write the follow query, Please can someone assist. I am writing in short hand the basis of the two queries I would like to combine. The object is to select all the products that have not meet the min sale requirements so I can send myself a reminder email. The first table products contains the product information and how ofter the stores must purchase and at what period ie, Weekly or Monthly by product. First Query gives me all the products that must check to see if they have meet the min sales. Select store_id,product_id, min_level, reoirder_period from products db where monthly_order_required 0 Second Query is then run to test to see if they have meet the min sales. Php: If reorder period = weekly then backdate = 7 days elseif period = monthly then backdate = 30 Select sum(sale_product_qty) as sale_period_total from sales db where store = store_id and product = product_id and date = now and date backdate and sale_period_total min_level Result: sum of sales in the last week or month period for that product from that store. If no result then sale level ok. If result then the difference from min_level is what is require for the store to make asap. Php: if sale_period_total min_level then email low order email. Any assistance to combine to the queries instead of hundreds of individual would be grateful. Regards Mark Dyer NZ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query help please!
Hi all, I have 2 tables that I need to extract data from, say PORTS and STATS. PORTS contains a list of Sea Ports in PORT i.e. : - PORTS.PORT UK FRANCE GERMANY NETHERLANDS SPAIN ITALY STATS contains a few thousand records that with among other things has the elements TO_PORT and AMOUNT. the entries in TO_PORT match the ports in PORT. Now one of the things I want to do is produce a summary of the totals of AMOUNT against the ports in PORTS, including (and this is the important bit that I'm stuck on) the ports in PORTS that do not have a match in STATS.TO_PORT. so that I get a result such as : - UK 300 FRANCE 100 GERMANY 400 NETHERLANDS 0 SPAIN 0 ITALY 300 and NOT :- UK 300 FRANCE 100 GERMANY 400 ITALY 300 Which is all I can get at the moment. the query I am using is : - SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT Basicaly what I want to do is pull out a list of all entries in PORTS.PORT and put a total figure against it from STATS.AMOUNT where the STATS.TO_PORT matches PORTS.PORT and just a zero if theres no entry in STATS. Anyone got any ideas? Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query help please!
I just set up a test db using your info, and the query you have worked ok. It provided a zero. Here's what I have (slightly modified from yours, but not appreciably). select ports.port, sum(stats.amount) as Total from ports left join stats on ports.port=stats.to_port group by ports.port order by Total desc Data: Ports - UK France Germany Italy Stats - France 1000 Italy 3000 UK 2000 France 1000 UK 5000 Result: UK 7000 Italy 3000 France 2000 Germany 0 --- Griffiths, Daniel [EMAIL PROTECTED] wrote: Hi all, I have 2 tables that I need to extract data from, say PORTS and STATS. PORTS contains a list of Sea Ports in PORT i.e. : - PORTS.PORT UK FRANCE GERMANY NETHERLANDS SPAIN ITALY STATS contains a few thousand records that with among other things has the elements TO_PORT and AMOUNT. the entries in TO_PORT match the ports in PORT. Now one of the things I want to do is produce a summary of the totals of AMOUNT against the ports in PORTS, including (and this is the important bit that I'm stuck on) the ports in PORTS that do not have a match in STATS.TO_PORT. so that I get a result such as : - UK300 FRANCE100 GERMANY 400 NETHERLANDS 0 SPAIN 0 ITALY 300 and NOT :- UK300 FRANCE100 GERMANY 400 ITALY 300 Which is all I can get at the moment. the query I am using is : - SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT Basicaly what I want to do is pull out a list of all entries in PORTS.PORT and put a total figure against it from STATS.AMOUNT where the STATS.TO_PORT matches PORTS.PORT and just a zero if theres no entry in STATS. Anyone got any ideas? Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Query help please!
urm, so it does.., thank you for your polite reply, I had included a month element in my query but left it out cos i didnt think it made a dufference, of course i was using a where month = 1 clause after the ON bit which was limiting the link to those records, duh! cheers Dan -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 15:36 To: Griffiths, Daniel Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL Query help please! I just set up a test db using your info, and the query you have worked ok. It provided a zero. Here's what I have (slightly modified from yours, but not appreciably). select ports.port, sum(stats.amount) as Total from ports left join stats on ports.port=stats.to_port group by ports.port order by Total desc Data: Ports - UK France Germany Italy Stats - France 1000 Italy 3000 UK 2000 France 1000 UK 5000 Result: UK 7000 Italy 3000 France 2000 Germany 0 --- Griffiths, Daniel [EMAIL PROTECTED] wrote: Hi all, I have 2 tables that I need to extract data from, say PORTS and STATS. PORTS contains a list of Sea Ports in PORT i.e. : - PORTS.PORT UK FRANCE GERMANY NETHERLANDS SPAIN ITALY STATS contains a few thousand records that with among other things has the elements TO_PORT and AMOUNT. the entries in TO_PORT match the ports in PORT. Now one of the things I want to do is produce a summary of the totals of AMOUNT against the ports in PORTS, including (and this is the important bit that I'm stuck on) the ports in PORTS that do not have a match in STATS.TO_PORT. so that I get a result such as : - UK300 FRANCE100 GERMANY 400 NETHERLANDS 0 SPAIN 0 ITALY 300 and NOT :- UK300 FRANCE100 GERMANY 400 ITALY 300 Which is all I can get at the moment. the query I am using is : - SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT Basicaly what I want to do is pull out a list of all entries in PORTS.PORT and put a total figure against it from STATS.AMOUNT where the STATS.TO_PORT matches PORTS.PORT and just a zero if theres no entry in STATS. Anyone got any ideas? Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query help - limit and group by
Hi there. I would like to return the 2 most recent events from my table, for each author (Author_ID). Here's my table structure (simplified). tblEvents ID Title Create_Date Author_ID Is there a way to do this with a query instead of getting every record, sorted by Author_ID, displaying the first two events, and skipping through until the Author_ID changes, then displaying the first 2 events for that Author_ID, etc. I know I could use a GROUP BY clause, but can I limit the number of records returned in each group, sorted by date? -- Faye -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MYSQL query help
It's not the IN, it's the sub-query you cannot use. MySQL doesn't support them and it's just about the main reason I don't like it. Alternatives? You could execute your subquery and return the results to an array. Loop through the array, using the index and the indexed element to drive a series of queries, accumulating your results as you go. If the number of items return by the subquery is not large, you could build an in set, for lack of a better term, so you would end up with ... and IN (first, second, ...nth) . (Check syntax!!) But that's likely to be slower than cold molasses and run like a pig. If you're not too far into the project, and you will have a lot of subqueries, switch to PostgreSQL or a database that supports them. I don't really have an answer. I hope someone comes up with a more elegant solution. Regards - Miles Thompson At 09:34 AM 12/14/2001 -0500, Harpreet wrote: I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative that would work? select * from lib_asset_tbl where material_id '' and asset_id in (select asset_id from lib_copy_tbl) Help is appreciated. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] MYSQL query help
I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative that would work? select * from lib_asset_tbl where material_id '' and asset_id in (select asset_id from lib_copy_tbl) Help is appreciated. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
