SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to perform such in single query??

    Hello forums!!

    How to query in following case:
    Suppose we have two tables
    Code:
    --------------
    table1
    --------------
    id
    title
    field
    --------------
    
    
    --------------
    table2
    --------------
    id
    title
    table1_id (FK to table1)
    field (Same field as in table1)
    --------------
    Question
    How to Select all the rows from table2 based on the following:
    If table1's 'field' has some value i.e. > 0 then
    relate with table2's 'field'
    else
    relate table1's id & table2's table1_id, and additionally filter by table1.table1_id = some value.

    How to perform such in single query?

    Thanks in advance for the help.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT ...
    FROM table1
    INNER JOIN table2
    ON
      (table1.field IS NOT NULL AND table1.field = table2.field)
      OR 
      (table1.field IS NULL AND table1.id = table2.table1_id AND table1.table1_id = ?)

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply: Dan Grossman.
    Can this be done this way?
    Code MySQL:
    SELECT ...
    FROM table1
    INNER JOIN table2
    ON
    IF(table1.field > 0, table1.field = table2.field, table1.id = table2.table1_id AND table1.table1_id = ?)

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have you tried it?

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just guessed. What do you think? Does this work?

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why don't you try it and find out?

  7. #7
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  8. #8
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    both ways works:
    for the unique data we have to use:
    GROUP BY table2.id

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Now add some indexes on the columns you use in the joins, and use EXPLAIN to find out whether both ways make use of the indexes. I have a hunch that using IF() as your ON condition might not, and therefore won't scale when you have many rows.

  10. #10
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more alternative:
    SELECT ...
    FROM table1
    INNER JOIN table2
    ON (table1.field = table2.field ) OR (table1.id = table2.table1_id)
    WHERE
    1=1
    AND (CASE
    WHEN table1.field > 0 THEN X ELSE table1.id END
    ) = X
    GROUP BY
    table2.id

    I would like to know whether this one is good or not compared to:
    SELECT ...
    FROM table1
    INNER JOIN table2
    ON
    (table1.field IS NOT NULL AND table1.field = table2.field)
    OR
    (table1.field IS NULL AND table1.id = table2.table1_id AND table1.table1_id = ?)

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't believe that query is equivalent.

  12. #12
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just ignore the condition: IS NOT NULL

  13. #13
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My real tables are:
    Code:
    shops
    ---------------------------
    id | admin_id | shop_title
    ---------------------------
    1  | 10       | shop1     |
    2  | 0	      | shop2     |
    Code:
    products
    -----------------------------------
    id | shop_id | admin_id | title
    -----------------------------------
    1  | 1       | 0        | product1
    2  | 1       | 10       | product2
    3  | 2       | 10 	| product3
    4  | 2       | 0 	| product4
    Case:
    Select all the products from 'products' on the following basis:
    if 'shops' has some value for `admin_id` ie `admin_id` > 0 then select based on this field
    else
    select based on the `shop_id` field

    In this case following will be the results:
    Code:
    2  | 1       | 10       | product2
    3  | 2       | 10 	| product3
    1  | 2       | 0        | product1

    How to perform in the single query?

    Thanks in advance.

  14. #14
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    We already answered this, just exchange the table and field names with the real ones.

    Could've saved you some work if you started out with the real table instead of making up fictional ones

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PHPycho View Post
    In this case following will be the results:
    Code:
    2  | 1       | 10       | product2
    3  | 2       | 10 	| product3
    1  | 2       | 0        | product1
    where are those values coming from? which one is the shop id, which one is the product id?

    your sample data is more confusing than ever
    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
  •