Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2013
    Posts
    9

    Unanswered: MYSQL Query Problem

    Hi,

    i have two table :- Products_tbl and Productdiary_tbl

    Note: Text content in the code blocks is automatically word-wrapped
    Product_tbl data :-

    product_id(PK) | name | update_date | price

    1 | prod1 | 25-07-2013 | 10
    2 | prod2 | 26-07-2013 | 11
    3 | prod3 | 27-07-2013 | 12
    4 | prod4 | 28-07-2013 | 13

    Productdiary_tbl data :-

    id (PK) | product_id | update_date | price
    101 | 1 | 25-07-2013 | 9
    102 | 2 | 24-07-2013 | 11
    103 | 1 | 29-07-2013 | 12
    104 | 3 | 25-07-2013 | 15
    105 | 1 | 30-07-2013 | 18
    106 | 2 | 31-07-2013 | 20
    107 | 3 | 1-08-2013 | 40



    i want to get price from Productdiary_tbl for latest date (so only one record i want ), if entry is not there then price will come from Product_tbl (if no record in subtable then take the record from main table) table like here product_id - 4 entry in not in Productdiary_tbl for that price will come from Product_tbl.

    i used below query for get the data but i am not getting data :-

    select products_tbl.id,products_tbl.PRICE from products_tbl
    where products_tbl.id not in
    (select productsdiary_tbl.id,productsdiary_tbl.price from productsdiary_tbl where productsdiary_tbl.datee = (select max(productsdiary_tbl.datee) from productsdiary_tbl))
    union
    select productsdiary_tbl.id,productsdiary_tbl.price from productsdiary_tbl where datee =(select max(productsdiary_tbl.datenew) from productsdiary_tbl));


    Thanks
    Anchit

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like...
    Code:
    SELECT pt.product_id
         , pt.name
         , COALESCE(pd.update_date , pt.update_date) AS update_date
         , COALESCE(pd.price       , pt.price      ) AS price
     FROM  Product_tbl      AS pt
     LEFT  OUTER JOIN
           (SELECT product_id
                 , MAX(update_date) AS update_date
             FROM  Productdiary_tbl
             GROUP BY
                   product_id
           )                AS pd_max
     INNER JOIN
           Productdiary_tbl AS pd
      ON   pd.product_id  = pd_max.product_id
       AND pd.update_date = pd_max.update_date 
      ON   pd.product_id  = pt    .product_id
    ;
    This was not tested.
    But, it looks to conform to SQL standard.

    Mimer SQL Developers - Mimer SQL-2003 Validator
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT pt.product_id
         , pt.name
         , COALESCE(pd.update_date , pt.update_date) AS update_date
         , COALESCE(pd.price       , pt.price      ) AS price
     FROM  Product_tbl      AS pt
     LEFT  OUTER JOIN
           (SELECT product_id
                 , MAX(update_date) AS update_date
             FROM  Productdiary_tbl
             GROUP BY
                   product_id
           )                AS pd_max
     INNER JOIN
           Productdiary_tbl AS pd
      ON   pd.product_id  = pd_max.product_id
       AND pd.update_date = pd_max.update_date 
      ON   pd.product_id  = pt    .product_id
    ;
    Code:
    Result:
    
    The following feature outside Core SQL-2003 is used:
    
    F591, "Derived tables"
    
    The following vendor reserved word is used:
    
    NAME
    Last edited by tonkuma; 07-29-13 at 18:53.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to use UNION,
    try something like this.

    Note: I revised your query ...
    (1) removed unnecessary qualifiers.
    (2) ammended table names and column names to be corresponded with your sample data.
    (3) removed unnecessary where clause.
    (3) added "WHERE pd2.product_id = pd1.product_id".

    Code:
    select product_id
         , price
     from  product_tbl 
     where product_id
           not in (select product_id
                    from  productdiary_tbl
                  ) 
    union 
    select product_id
         , price
     from  productdiary_tbl pd1
     where update_date
           = (select max(update_date)
               from  productdiary_tbl pd2
               WHERE pd2.product_id = pd1.product_id
             )
    ORDER BY
          product_id
    ;

  4. #4
    Join Date
    Jun 2013
    Posts
    9
    Quote Originally Posted by tonkuma View Post
    If you want to use UNION,
    try something like this.

    Note: I revised your query ...
    (1) removed unnecessary qualifiers.
    (2) ammended table names and column names to be corresponded with your sample data.
    (3) removed unnecessary where clause.
    (3) added "WHERE pd2.product_id = pd1.product_id".

    Code:
    select product_id
         , price
     from  product_tbl 
     where product_id
           not in (select product_id
                    from  productdiary_tbl
                  ) 
    union 
    select product_id
         , price
     from  productdiary_tbl pd1
     where update_date
           = (select max(update_date)
               from  productdiary_tbl pd2
               WHERE pd2.product_id = pd1.product_id
             )
    ORDER BY
          product_id
    ;

    Hi Tonkuma,

    Thanks For your Time and efforts on this, really Appreciate .

    i was trying below query :-

    select id,name, ifnull( (select price
    from productdiary_tbl sd where datee = (select max(datee) from productdiary_tbl sdd where sd.product_id=p.product_id)) , p.price) as price from product_tbl p

    i got the data, but do you think this approach is right ?
    or is there any bug in this query ?

    Thanks
    Anchit

  5. #5
    Join Date
    Jun 2013
    Posts
    9
    My query is not working properly ..... so i am using your query ...

    Thanks for help.


    Thanks
    Anchit

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that your query sould be like...
    Code:
    select /*id*/product_id
         , name
         , ifnull(  (select price
                      from  productdiary_tbl sd
                      where sd.product_id = p.product_id
                        AND /*datee*/update_date
                            = (select max(/*datee*/update_date)
                                from  productdiary_tbl sdd
                                where /*sd*/sdd.product_id = p.product_id
                              )
                    )
                  , p.price
                 ) as price
     from  product_tbl p
    ;
    Note: I amended the column names to be corresponding to you examples in your first post.

  7. #7
    Join Date
    Jun 2013
    Posts
    9
    Quote Originally Posted by tonkuma View Post
    I thought that your query sould be like...
    Code:
    select /*id*/product_id
         , name
         , ifnull(  (select price
                      from  productdiary_tbl sd
                      where sd.product_id = p.product_id
                        AND /*datee*/update_date
                            = (select max(/*datee*/update_date)
                                from  productdiary_tbl sdd
                                where /*sd*/sdd.product_id = p.product_id
                              )
                    )
                  , p.price
                 ) as price
     from  product_tbl p
    ;
    Note: I amended the column names to be corresponding to you examples in your first post.
    Hi,

    i was not getting proper output from old query so i tried new one. but i got :-
    error code 1242, SubQuery returns more them 1 row.

    Can you please help me out with this ?


    Thanks
    Anchit

  8. #8
    Join Date
    Jun 2013
    Posts
    9
    i got the reason, why i didn't get correct data from old query , because time was same for multiple rows as below :-

    ID date
    ---- -----
    0729e3f8-727d-4f54-af37-5f1eed038bd8 2013-08-07 01:43:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 2013-08-07 01:43:00
    18103776-fffd-4a23-afdd-f15fb027cb9f 2013-08-07 02:15:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 2013-08-07 01:43:00


    Can we modify old query as a workaround for time issue ?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by anchitanc View Post
    i got the reason, why i didn't get correct data from old query , because time was same for multiple rows as below :-

    ID date
    ---- -----
    0729e3f8-727d-4f54-af37-5f1eed038bd8 2013-08-07 01:43:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 2013-08-07 01:43:00
    18103776-fffd-4a23-afdd-f15fb027cb9f 2013-08-07 02:15:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 2013-08-07 01:43:00


    Can we modify old query as a workaround for time issue ?
    For what table?

    You showed two tables in your first post, like...
    i have two table :- Products_tbl and Productdiary_tbl

    Note: Text content in the code blocks is automatically word-wrapped
    Product_tbl data :-

    product_id(PK) | name | update_date | price

    1 | prod1 | 25-07-2013 | 10
    2 | prod2 | 26-07-2013 | 11
    3 | prod3 | 27-07-2013 | 12
    4 | prod4 | 28-07-2013 | 13

    Productdiary_tbl data :-

    id (PK) | product_id | update_date | price
    101 | 1 | 25-07-2013 | 9
    102 | 2 | 24-07-2013 | 11
    103 | 1 | 29-07-2013 | 12
    104 | 3 | 25-07-2013 | 15
    105 | 1 | 30-07-2013 | 18
    106 | 2 | 31-07-2013 | 20
    107 | 3 | 1-08-2013 | 40

  10. #10
    Join Date
    Jun 2013
    Posts
    9
    Quote Originally Posted by tonkuma View Post
    For what table?

    You showed two tables in your first post, like...
    For Productdiary_tbl Table. And Product_tbl date is not don't useful, we dont need to use it beacause anyhow if Productdiary_tbl date is null then it will pick from Product_tbl so no need any date checking for Product_tbl.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want you to publish sample/test data, like

    Code:
    Product_tbl data :- 
    
    product_id(PK) | name  | update_date | price 
    1              | prod1 | 25-07-2013  | 10 
    2              | prod2 | 26-07-2013  | 11 
    3              | prod3 | 27-07-2013  | 12 
    4              | prod4 | 28-07-2013  | 13 
    
    Productdiary_tbl data :- 
    
    id (PK)                              | product_id | update_date         | price 
    101                                  | 1          | 25-07-2013          |  9 
    102                                  | 2          | 24-07-2013          | 11 
    103                                  | 1          | 29-07-2013          | 12 
    104                                  | 3          | 25-07-2013          | 15 
    105                                  | 1          | 30-07-2013          | 18 
    106                                  | 2          | 31-07-2013          | 20 
    107                                  | 3          |  1-08-2013          | 40
    0729e3f8-727d-4f54-af37-5f1eed038bd8 |            | 2013-08-07 01:43:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 |            | 2013-08-07 01:43:00
    18103776-fffd-4a23-afdd-f15fb027cb9f |            | 2013-08-07 02:15:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 |            | 2013-08-07 01:43:00
    And,
    the results from the test data.
    Last edited by tonkuma; 08-08-13 at 04:13.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    I want you to publish sample/test data, like

    Code:
    Product_tbl data :- 
    
    product_id(PK) | name  | update_date | price 
    1              | prod1 | 25-07-2013  | 10 
    2              | prod2 | 26-07-2013  | 11 
    3              | prod3 | 27-07-2013  | 12 
    4              | prod4 | 28-07-2013  | 13 
    
    Productdiary_tbl data :- 
    
    id (PK)                              | product_id | update_date         | price 
    101                                  | 1          | 25-07-2013          |  9 
    102                                  | 2          | 24-07-2013          | 11 
    103                                  | 1          | 29-07-2013          | 12 
    104                                  | 3          | 25-07-2013          | 15 
    105                                  | 1          | 30-07-2013          | 18 
    106                                  | 2          | 31-07-2013          | 20 
    107                                  | 3          |  1-08-2013          | 40
    0729e3f8-727d-4f54-af37-5f1eed038bd8 |            | 2013-08-07 01:43:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 |            | 2013-08-07 01:43:00
    18103776-fffd-4a23-afdd-f15fb027cb9f |            | 2013-08-07 02:15:00
    0729e3f8-727d-4f54-af37-5f1eed038bd8 |            | 2013-08-07 01:43:00
    And,
    the results from the test data.
    Although, I didn't think that this was the exact data in your reqirements.
    but, you showed some of these data.

    So, I showed the some example(including various situations, something like some exeptional conditins) to ease to display your requirements.

    It might be not so useful to describe your requirements by leteral statemens for me,
    because I'm not so well at English.


    Rather, I want you to show the sampe/test data and your required results from the data.

Posting Permissions

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