SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting Multiple ID's (Rows)

    I am writing a query for a MySQL table that is sort of a cross-reference table for products and categories with many-to-many relationships. I want to select multiple ID's (rows) but for some reason I can't seem to get it to go. I always get an empty set when there should be results. A stripped down version of my query goes something like this:

    SELECT DISTINCT productid from products_categories WHERE category_id=3 and category_id=19;

    Any ideas?

  2. #2
    SitePoint Zealot pionar's Avatar
    Join Date
    Nov 2003
    Location
    Indianapolis
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, without seeing the actual tables, it appears that you're asking for a condition that can't exist. A field can't have two values at the same time. I think you either want OR, or you need to redo your tables.

  3. #3
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My table would look something like this (some columns are removed for brevity):

    category_id productid

    3 717
    3 766
    3 402
    3 187
    3 793
    3 66
    3 59
    19 766
    35 766
    35 793
    19 793

    What I want to do is have rows returned only when a productid falls into 2 or 3 categories(category_id). For example if I passed the category_id's 3,19,35 in my query only productid's 766 and 793 should be returned.

  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)

  5. #5
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I looked at that thread, but I'm not sure it's the same thing. In that thread the user had multiple tables. I am dealing with just one table. Anyone have any other suggestions?

  6. #6
    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)
    the approach is the same, you just have to remove the join:

    Code:
    select productid 
        from tableA
      where category_id in ( 3, 19 )
      group
      	by productid
      having count(*) >= 2

  7. #7
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that worked, but yikes, that's the only way to do it? I would have thought there would be some sort of method using nested queries or some other way.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by frankiehots
    Ok, that worked, but yikes, that's the only way to do it?
    no, it isn't, but it's the simplest, fastest, and easiest to understand

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    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)
    you could try this other method that not quite as good:

    Code:
    select t.productid
     from tablea t
     join jablea t1 on
       t1.productid = t.productid
       and t1.category_id = 3
     join jablea t2 on
       t2.productid = t.productid
       and t2.category_id = 19
    you simply keep adding joins for each category you want to search on.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck
    you simply keep adding joins for each category you want to search on.
    okay, how do you handle this then --

    list all products that fall into at least 5 of these 7 categories

    not so easy with joins, is it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    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)
    you can't. that's why the other query is better. and the OP didn't specifically ask for that.

  12. #12
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I have used the method that longneck suggested and it works fine until I try to use it as a subquery. The query longneck suggested was like this:

    select productid
    from tableA
    where category_id in ( 3, 19 )
    group by productid
    having count(*) >= 2
    When I try to use a query like that as a subquery, I am running into a problem that seems to be affected by whether or not I use "ORDER BY" clause and which table it is coming. Here is my larger query with a sub-query:

    SELECT p.productid, p.productcode, p.descr, p.fulldescr, GROUP_CONCAT( DISTINCT c.price ), GROUP_CONCAT( DISTINCT x.value ), p.list_price
    FROM xcart_products AS p, xcart_pricing AS c, xcart_extra_field_values AS x, md_products_categories AS m
    WHERE p.productid = c.productid
    AND p.productid = x.productid
    AND p.productid = m.productid
    AND p.productid
    IN (

    SELECT productid
    FROM md_products_categories
    WHERE category_id
    IN ( 3, 19 )
    GROUP BY productid
    HAVING COUNT( * ) >=2
    )
    GROUP BY p.productcode
    ORDER BY category_id;
    If I don't use an ORDER BY clause or if it is based on a column that does not come from the md_products_categories table, some of the columns in my results from the query are blank when they shouldn't be.
    If I use an order ORDER BY clause based on a column that comes from the md_products_categories table the results are complete.

    Any idea why my query seems to be running into problems based on the use of ORDER BY?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, try making your SELECT fields match your GROUP BY --
    Code:
    SELECT p.productid
         , p.productcode
         , p.descr
         , p.fulldescr
         , p.list_price
         , GROUP_CONCAT( DISTINCT c.price )
         , GROUP_CONCAT( DISTINCT x.value )
      FROM ...
    GROUP
        BY p.productid
         , p.productcode
         , p.descr
         , p.fulldescr
         , p.list_price
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    first of all, try making your SELECT fields match your GROUP BY
    Thank you...that worked, with one problem still hanging on.
    I can now do ORDER BY any column and I will get complete results as I expect, but if I do not use any ORDER BY then 2 of the columns (descr and fulldescr) are still coming back empty. Any ideas?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try changing your joins to LEFT OUTER JOINs

    i can't imagine every p row always having an x row

    also, in the outer query, i'm pretty sure you don't need the m table at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried changing the inner join to an outer join, but I couldn't get it to work that way. Maybe I am doing something wrong. I haven't really used outer joins much before; symbolic inner joins have covered most of what I have previously tried to do. Here is what I tried:

    Code:
    SELECT xcart_products.productid, xcart_products.productcode, xcart_products.descr, xcart_products.fulldescr, xcart_products.list_price GROUP_CONCAT( DISTINCT xcart_pricing.price ), GROUP_CONCAT( DISTINCT xcart_extra_field_values.value ) 
    FROM 
    LEFT JOIN xcart_products ON xcart_products.productid = xcart_pricing.productid 
    LEFT JOIN xcart_pricing ON xcart_pricing.productid = xcart_extra_field_values.productid 
    LEFT JOIN xcart_extra_field_values ON xcart_extra_field_values.productid = md_products_categories.productid
    
    WHERE md_products_categories.productid
    IN (
    
    SELECT productid
    FROM md_products_categories
    WHERE category_id
    IN ( 3, 19 ) 
    GROUP BY productid
    HAVING COUNT( * ) >=2
    )
    GROUP BY xcart_products.productid, xcart_products.productcode, xcart_products.descr, xcart_products.fulldescr, xcart_products.list_price
    ORDER BY orderby;
    Also, I may be wrong, but I think I do need the m table(md_products_categories) in the outer query the event that I want to do an "ORDER BY" basedd on any of the columns in that table.

    P.S. Looking forward to taking your class r937

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you will not be able to do an ORDER BY on any columns that aren't in your GROUP BY

    let's get the basic query running before adding back the md table
    Code:
    SELECT xcart_products.productid
         , xcart_products.productcode
         , xcart_products.descr
         , xcart_products.fulldescr
         , xcart_products.list_price 
         , GROUP_CONCAT( DISTINCT xcart_pricing.price )
         , GROUP_CONCAT( DISTINCT xcart_extra_field_values.value ) 
      FROM xcart_products 
    LEFT outer
      JOIN xcart_pricing 
        ON xcart_products.productid 
         = xcart_pricing.productid 
    LEFT outer
      JOIN xcart_extra_field_values 
        ON xcart_pricing.productid 
         = xcart_extra_field_values.productid 
     WHERE xcart_products.productid
        IN (
           SELECT productid
             FROM md_products_categories
            WHERE category_id
               IN ( 3, 19 ) 
           GROUP BY productid
           HAVING COUNT( * ) >=2
           )
    GROUP 
        BY xcart_products.productid
         , xcart_products.productcode
         , xcart_products.descr
         , xcart_products.fulldescr
         , xcart_products.list_price
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    let's get the basic query running before adding back the md table
    Ok, that worked well and fixed the strange problem where I had to use an "ORDER BY" in order to avoid gettings some empty columns. I guess with joins, you can use the various types to accomplish similar things.

    Now my last question is if I do want to do an "ORDER BY" and base it on a column from the "md_products_categories" table that we took out of the outer query, what is the proper way to do it?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, the answer to that is, you gots to add it back in, eh

    don't forget, when you add it back in, the column that you want to ORDER BY has to be in the SELECT as well as the GROUP BY

    that's very important
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I added the "md_products_categories" back into the JOIN in the outer query and then added "md_products_categories.orderby" to the SELECT and GROUP BY. Once I did that there was a new problem - I started getting double results. I expected to get 3 rows returned, instead I got 6 rows returned where half were duplicates.

    Then I tried removing the SELECT and GROUP BY while leaving the "md_products_categories" LEFT JOIN to still maintain a link between tables. That actually returned the 3 rows as I expected. As a matter of fact I could do an "ORDER BY" any column in the "md_products_categories" table and it would work. So it seems like I'm doing it the wrong way, but getting the right results.

    Here is my code with "md_products_categories.orderby" set in the SELECT and GROUP:
    Code:
    SELECT xcart_products.productid
         , xcart_products.productcode
         , xcart_products.descr
         , xcart_products.fulldescr
         , xcart_products.list_price
         , md_products_categories.orderby 
         , GROUP_CONCAT( DISTINCT xcart_pricing.price )
         , GROUP_CONCAT( DISTINCT xcart_extra_field_values.value ) 
      FROM xcart_products 
    LEFT outer
      JOIN xcart_pricing 
        ON xcart_products.productid 
         = xcart_pricing.productid 
    LEFT outer
      JOIN xcart_extra_field_values 
        ON xcart_pricing.productid 
         = xcart_extra_field_values.productid
    LEFT outer
      JOIN md_products_categories 
        ON xcart_extra_field_values.productid 
         = md_products_categories.productid 
    WHERE xcart_products.productid
        IN (
           SELECT productid
             FROM md_products_categories
            WHERE category_id
               IN ( 3, 19 ) 
           GROUP BY productid
           HAVING COUNT( * ) >=2
           )
    GROUP 
        BY xcart_products.productid
         , xcart_products.productcode
         , xcart_products.descr
         , xcart_products.fulldescr
         , xcart_products.list_price
         , md_products_categories.orderby
    ORDER BY orderby
    and without:
    Code:
    SELECT xcart_products.productid
         , xcart_products.productcode
         , xcart_products.descr
         , xcart_products.fulldescr
         , xcart_products.list_price
    	 , GROUP_CONCAT( DISTINCT xcart_pricing.price )
         , GROUP_CONCAT( DISTINCT xcart_extra_field_values.value ) 
      FROM xcart_products 
    LEFT outer
      JOIN xcart_pricing 
        ON xcart_products.productid 
         = xcart_pricing.productid 
    LEFT outer
      JOIN xcart_extra_field_values 
        ON xcart_pricing.productid 
         = xcart_extra_field_values.productid
    LEFT outer
      JOIN md_products_categories 
        ON xcart_extra_field_values.productid 
         = md_products_categories.productid 
    WHERE xcart_products.productid
        IN (
           SELECT productid
             FROM md_products_categories
            WHERE category_id
               IN ( 3, 19 ) 
           GROUP BY productid
           HAVING COUNT( * ) >=2
           )
    GROUP 
        BY xcart_products.productid
         , xcart_products.productcode
         , xcart_products.descr
         , xcart_products.fulldescr
         , xcart_products.list_price
    ORDER BY orderby
    Any ideas?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ideas? sure

    what you're seeing in your first query is that since a product might be in more than one category, so it will join to more than one md row, consequently ...

    if you want to order the query by some md column, then if that column is exactly the same for each product, you can safely do an aggregate function on it, such as MIN(md.column) -- this allows you to have MIN(md.column) in the SELECT but not in the GROUP BY, hence you're back to one result row for each product and you can then ORDER BY MIN(md.column)

    however, your second query is on really shakey ground -- it should not work (and probably wouldn't in any other database but mysql) so i recommend you find a way (hint: see above) to do what you need correctly

    admittedly, this is difficult to do when the database you're working with is as sloppy as mysql -- how's the average person supposed to know what's right if mysql executes the wrong ones too?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict frankiehots's Avatar
    Join Date
    Jan 2002
    Location
    U.S.A. *Blue State*
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm back to this query after other things pulled me away for a while. I have changed the query almost as you recommeded(although I didn't change the subquery because it caused more problems when I did), but I am having a hell of a time getting the ORDER BY to work. Probably the reason I am running into problems is the "orderby" column is NOT the same for each product. Maybe I am approaching my ORDER BY method all wrong. In my cross reference table with many-to-many relationships that I am working off of in the subquery, I will sometimes be passing 1,2, 3 or even more category ID's. I was hoping that if I only passed one category ID, that my results would be based only on the orderby in the rows with that category ID. It is not working like that though.

    For example:

    productid 717 is in 2 rows. When it is tied to category_id 3 it has an orderby value of 100. When it is tied to category_id 19 it has an orderby value of 1100. If I was querying only for category_id 3, I thought it would have an orderby value of 100, but that is not always the case.

    Here is the query as I currently have it:

    Code:
    SELECT p.productid
         , p.productcode
         , p.descr
         , p.fulldescr
         , p.list_price
         , MIN(m.orderby) 
         , GROUP_CONCAT( DISTINCT c.price )
         , GROUP_CONCAT( DISTINCT x.value )
      FROM xcart_products as p
    LEFT outer
      JOIN xcart_pricing as c 
        ON p.productid 
         = c.productid 
    LEFT outer
      JOIN xcart_extra_field_values as x
        ON c.productid 
         = x.productid
    LEFT outer
      JOIN md_products_categories as m
        ON x.productid 
         = m.productid 
    WHERE p.productid
        IN (
           SELECT productid
             FROM md_products_categories
            WHERE category_id
               IN (3,19) 
           GROUP BY productid
           HAVING COUNT( * ) >=2
           )
    GROUP 
        BY p.productid
    	 , p.productcode
         , p.descr
         , p.fulldescr
         , p.list_price
    ORDER BY MIN(m.orderby)
    What is the right way to set up ORDER BY's for a cross reference table such as mine?

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by frankiehots
    What is the right way to set up ORDER BY's for a cross reference table such as mine?
    um, i dunno

    since the "orderby column is NOT the same for each product" then if the product belongs to more than one category, you need some way to make that call, and i wouldn't know what that might be
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •