[solved]problematic query urgent :)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

[solved]problematic query urgent :)

Post by jmut »

process -> PK process_id
orders -> PK orders_id

Code: Select all

SELECT DAYNAME( o.date_end ) AS pd, count( * ) AS amount
            FROM process op
            LEFT JOIN orders o
            USING ( orders_id )
                WHERE op.field_name = 'product_id'
                AND op.field_value = 50037
                
                AND o.date_end IS NOT NULL
                AND YEAR( o.date_end ) =2005
                AND WEEK( o.date_end, 1 ) =45
                GROUP BY pd
The problem is I can have more than one values with same product_id for a single 'orders_id'
I want to count only one of them not both.


I tried adding

Code: Select all

AND op.process_id = (SELECT MAX(process_id) FROM process WHERE  `field_name` = 'product_id' AND `field_value` = 50037 )
but now success of course....I am missing something

Using mysql 4.1...

I am not even sure I can achive this in one query.
Last edited by jmut on Thu Nov 17, 2005 9:06 am, edited 1 time in total.
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

I think I made it work

Code: Select all

SELECT DAYNAME(o.date_end) as pd,count(*) as amount
                            FROM (
                                    SELECT DISTINCT(orders_id),field_name,field_value FROM process WHERE field_name = 'product_id'
                                 ) op LEFT JOIN orders o USING (orders_id)
                            WHERE
                                op.field_name = 'product_id'
                                AND op.field_value = 50037
                                AND o.date_end  IS NOT NULL
                                AND YEAR(o.date_end) = 2005
                                AND WEEK(o.date_end,1) = 45
                            GROUP BY pd
enjoy
Post Reply