Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Exclamation how to combine these queries into one using a join?

    Hi there. I apologize but I'm a novice at sql...
    I have to combine these two queries into one using a join:

    SELECT categories.category_id, categories.title, categories.description,categories.parent_id,catego ries.display_weight,
    categories.image_thumb, categories.deleted, categories.image_banner,
    FROM categories
    WHERE
    AND parent_id=18
    AND category_id <> 18

    And here's the second one:


    SELECT `product.product_id`, `product.name`, `product.image_thumb`, `product.deleted`
    FROM (`product`)
    WHERE `product_id` IN (select product_id from product_category where category_id=18)
    ORDER BY `display_weight` DESC

    A few points:

    It's possible to have a scenario where the first query will return 0 rows, but the second does contain records.

    Also, you'll notice that the second query has a subquery baked in. The relationship between the product table and category table is defined in the product_category table.
    The product table on its own doesn't know which categories the products belong to.

    Any help would be appreciated.

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    87
    A standard (INNER) JOIN will return 0 rows if one of the tables is empty. Check out about OUTER JOIN's instead. (I.e. LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN.)

    How come query may return 0 rows, but the second does contain records?

    How do the two queries relate to each other? (Join condition...)

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,470
    Another option might be UNION(or UNION ALL) of those two queries by adding some dummy columns for each query, to make same select column list for two queries.

Posting Permissions

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