SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL query filter help needed

    I'm having this tables for products, products orders and products accesses (downloads) and I want to build a filter query. Everything works fine for the products table but when the query gets a little more complicated and, for example, I want to select only the products that have more than 1 orders or more than 1 accesses nothing happens....the filter doesn't work. I'll just paste the query here...hope there is someone to light this up a little for me.

    Code MySQL:
    SELECT 
    	COUNT( products_accesses.ID ) AS accesses, 
    	COUNT( products_orders.ID ) AS orders, 
    	products . *
    FROM 
    	`products`
    LEFT JOIN 
    	products_accesses ON ( products.ID = products_accesses.prod_id )
    LEFT JOIN 
    	products_orders ON ( products.ID = products_orders.prod_id )
    WHERE 
    	( 
    		SELECT 
    			COUNT( products_accesses.ID )
    		FROM 
    			products_accesses
    		LEFT JOIN 
    			products ON ( products.ID = products_accesses.prod_id )
    	) > '1'
    AND 
    	(
    		SELECT 
    			COUNT( products_orders.ID )
    		FROM 
    			products_orders
    		LEFT JOIN 
    			products ON ( products.ID = products_orders.prod_id )
    	) > '1'
    GROUP BY 
    	products.ID

    There's no error or anything like that...just an invalid filter.

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2006
    Location
    Sweden
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You say you want "I want to select only the products that have more than 1 orders or more than 1 accesses", but in your query, you state that you want everything with both more than one access and more than one order. Perhaps you should change "AND" to "OR"?

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's not the problem. we can simply remove that part with accesses and still, the same result. The query won't select only the products that have more than 1 order.
    Code MySQL:
    SELECT
        COUNT( products_accesses.ID ) AS accesses,
        COUNT( products_orders.ID ) AS orders,
        products . *
    FROM
        `products`
    LEFT JOIN
        products_accesses ON ( products.ID = products_accesses.prod_id )
    LEFT JOIN
        products_orders ON ( products.ID = products_orders.prod_id )
    WHERE
        (
            SELECT
                COUNT( products_orders.ID )
            FROM
                products_orders
            LEFT JOIN
                products ON ( products.ID = products_orders.prod_id )
        ) > '1'
    GROUP BY
        products.ID

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    delete your entire WHERE clause and add this HAVING clause to the end of your query:
    Code:
    HAVING accesses > 1 or orders > 1
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so simple....damn...thanks man.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •