SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    join unique row in one table with a non unique row in another

    Is it possible to do this:

    In a single query, join a row within a table based on a column with unique stock number, with a targeted row within within a group of rows with the same stock number in another table?

    If so how do you target the non unique row?

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Do you mean performing a JOIN?

    Code:
    SELECT columns_from_first_table, columns_from_second_table
      FROM first_table AS t
      LEFT JOIN second_table AS t2 ON t.stock_number = t2.stock_number

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you want a specific row from the group of rows with the same stock number in the other table, you'll have to add ad a WHERE clause that specifies the identifying column-value pair for that specific row.

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks cpradio and guido2004.

    Now, is it possible to select qualified rows from table1, based on WHERE and add a column from table2 when a column from table1 matches a column from table2?

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    Thanks cpradio and guido2004.

    Now, is it possible to select qualified rows from table1, based on WHERE and add a column from table2 when a column from table1 matches a column from table2?
    Yes, that would be using a JOIN with a WHERE clause

    Code:
    SELECT columns_from_first_table, columns_from_second_table
      FROM first_table AS t
        LEFT JOIN second_table AS t2 ON t.stock_number = t2.stock_number
      WHERE t.column_from_first_table = X

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks cpradio.

    I've written this query and have analyzed the results and am satisfied that it does what I need it to do. Thanks again to cpradio and guido2004.
    Code:
    SELECT DISTINCT plan.cliorder, plan.*, trkg.curwipprice_online FROM plan LEFT JOIN trkg ON plan.cliorder = trkg.cliorder WHERE plan.cliorder != '' AND plan.enabled != '0000-00-00 00:00:00' AND TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC, id ASC, ((1-(plan.curwipprice/plan.edprice))*100) DESC

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The DISTINCT will not do what you think it does. It will act on all columns in your query. You asked for one value from a group of stock numbers. The DISTINCT will return all rows from that GROUP of rows if any columns differ. So if you had five rows in groupid=24 for example, all of them will be returned if they satisfy the WHERE clause.

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Then what keyword should I use to return one row for each unique stock number (we call them cliorder)?

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks guelphdad.

    This code:
    Code:
    SELECT plan.*, trkg.curwipprice_online, trkg.addr FROM plan LEFT JOIN trkg ON plan.cliorder = trkg.cliorder WHERE (trkg.addr = '5740 OLD CHENEY RD') OR (plan.cliorder != '' AND plan.enabled != '0000-00-00 00:00:00' AND TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end)) ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC, id ASC, ((1-(plan.curwipprice/plan.edprice))*100) DESC
    produces this echo in php:

    Superboy and His Dog Krypto ----------5740 OLD CHENEY RD
    Wonder Woman----------5740 OLD CHENEY RD
    Creature from the Black Lagoon ----------5740 OLD CHENEY RD
    Seaview 8-Window----------5740 OLD CHENEY RD
    Peterbilt 377 A/E ----------1840 Pawnee St
    F-86F Sabre----------1840 Pawnee St
    Sherman III----------5740 OLD CHENEY RD
    Nakajima Ki-84----------5740 OLD CHENEY RD
    Panther G----------5740 OLD CHENEY RD
    Peterbilt 377 A/E ----------5740 OLD CHENEY RD
    F-86F Sabre----------5740 OLD CHENEY RD
    Peterbilt 353 ----------1840 Pawnee St
    Peterbilt 353 ----------5740 OLD CHENEY RD


    How do I get the "1840 Pawnee St" out of the selection from the right table?? I thought the OR would do that.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you write your query in 1 long line, it gets hard to understand.
    Try a format like this:
    Code:
    SELECT 
        plan.*
      , trkg.curwipprice_online
      , trkg.addr 
    FROM plan 
    LEFT JOIN trkg 
    ON plan.cliorder = trkg.cliorder 
    WHERE (trkg.addr = '5740 OLD CHENEY RD') 
    OR    (plan.cliorder != '' AND 
           plan.enabled != '0000-00-00 00:00:00' AND 
           TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end)
          ) 
    ORDER BY 
        ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC
      , id ASC
      , ((1-(plan.curwipprice/plan.edprice))*100) DESC
    Isn't that easier to read?

    With the OR, your query takes all rows that have OR the '5740' address, OR all the conditions in the part after the OR. One of them is enough.
    I don't know what the criteria are for the rows you want to extract, so I can't tell you how to write the WHERE conditions.

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks again guido2004.

    Based on my previous code, I changed the WHERE to:
    Code:
    (TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) AND trkg.addr = '5740 OLD CHENEY RD')
    this produces ten matches I can use, but fails to select rows from the LEFT table that DON'T have a corresponding match in the RIGHT table (makes sense). How do modify this query to also SELECT rows from the LEFT table that don't match the RIGHT table?

    Removing the '5740 OLD CHENEY RD' requirement in the WHERE selects those unmatched rows plus all the non '5740 OLD CHENEY RD' addresses that I don't want - catch-22! That makes me think I need an OR in my WHERE to select those unmatched rows from the LEFT, but so far no joy. Can an OR be wriiten to do that or do I need to change my approach?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    How do modify this query to also SELECT rows from the LEFT table that don't match the RIGHT table?
    change this --
    Code:
    SELECT plan.*
         , trkg.curwipprice_online
         , trkg.addr 
      FROM plan 
    LEFT OUTER
      JOIN trkg 
        ON plan.cliorder = trkg.cliorder 
     WHERE (trkg.addr = '5740 OLD CHENEY RD') 
        OR ...
    to this --
    Code:
    SELECT plan.*
         , trkg.curwipprice_online
         , trkg.addr 
      FROM plan 
    LEFT OUTER
      JOIN trkg 
        ON trkg.cliorder = plan.cliorder
       AND trkg.addr = '5740 OLD CHENEY RD' 
     WHERE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm sure you hear this a lot r937, very cool! Thank-you. I still have a little hair left.

    The google didn't take me to documentation for your use of AND in a LEFT OUTER JOIN. Please post a link if you have one.

  14. #14
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You use AND in the LEFT JOIN when you have conditions that must be matched in the right hand or second table. If you put those conditions in a where clause, those are applied after the join and the NULL or unmatched rows would be eliminated from the results. Essentially having them in the WHERE changes the LEFT JOIN to an INNER JOIN.

  15. #15
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks guelphdad.

    Was there some way I could've learned that in the manual?

    The longer I code the more I go to the manual and google first before posting, but I just couldn't find anything before posting. I'm sure that's because I didn't use an effective query.

    What's this situation even called? If you where to google it, what keywords would you use?

  16. #16
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    The following MySQL page has a bit more information about JOINs on it and shows an example of using AND and OR within a JOIN
    http://dev.mysql.com/doc/refman/5.0/en/join.html

  17. #17
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.
    .. is from the manual and I believe it covers this situation. I read that page before, but obviously didn't make the connection. Thankfully, you all are available.

    I think this topic has concluded.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    I'm sure you hear this a lot r937, very cool! Thank-you.
    not too often, but enough to make me keep doing it

    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
  •