#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0

    Latest rows from two tables


    i have 2 tables with one to many relations both table has 2 columns has same column names barcode,result .if somebody override then create new row with those two columns has latest result and some extra data for collumns in second table and i need the result table with latest records by time. barcode can repeat as much as the user wants but in result table only one entry for one barcode(that has the latest timestamp)

    show create table 1

    CREATE TABLE `Test` (
    `UniqueID` int(11) NOT NULL auto_increment COMMENT 'Used for indexing table',
    `barcode` varchar(25) character set ascii NOT NULL,
    `Vendor` varchar(20) character set ascii NOT NULL,
    `Mod` varchar(30) character set ascii NOT NULL,
    `Version` varchar(128) character set ascii NOT NULL,
    `result` tinyint(1) NOT NULL,
    `Category` longtext character set ascii,
    `FDetails` longtext character set ascii,
    `ODetails` longtext character set ascii,
    `DateTest` datetime NOT NULL,
    `User` varchar(50) character set ascii default NULL,
    PRIMARYKEY (`UniqueID`),
    KEY`barcode` (`barcode`)
    )


    show create table2

    CREATE TABLE `Ovride` (
    `UniqueID` int(11) NOT NULL auto_increment,
    `Testid` int(11) NOT NULL COMMENT 'test table Unique ID',
    `barcode` varchar(25) NOT NULL,
    `result` tinyint(1) NOT NULL,
    `Comment` text,
    `User` varchar(50) NOT NULL,
    `TimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date record created',
    PRIMARY KEY (`UniqueID`)
    KEY`barcode` (`barcode`))

    sample data

    test

    UniqueID barcode Vendor Mod Version result Category FDetails ODetails DateTest User


    1 AA234 ABC A 1.2 0 c1 nothing nothing 2013-02-01 00:00:00 user2
    2 BB678 CBC A 1.5 0 c1 nothing nothing 2013-02-01 00:00:00 user1
    3 AA234 ABC A 1.8 1 c1 nothing nothing 2013-02-02 00:00:00 user1
    4 CC911 XYZ A 1.2 0 c1 nothing nothing 2013-02-01 00:00:00 user2
    6 BB678 CBC A 1.5 0 c1 nothing nothing 2013-02-05 00:00:00 user1

    UniqueID Testid barcode result Comment User TimeStamp

    1 2 BB678 1 comments... user3 2013-02-03 00:00:00 user1
    2 4 CC911 1 comments12.. user2 2013-02-05 00:00:00 user2


    output expected
    barcode Vendor Mod Version result Category FDetails ODetails Actaual test time(coming from table 1) Result time User Comment

    BB678 CBC A 1.5 0 c1 nothing nothing 2013-02-05 00:00:00 2013-02-05 00:00:00 user1 null
    AA234 ABC A 1.8 1 c1 nothing nothing 2013-02-02 00:00:00 2013-02-02 00:00:00 user1 null
    CC911 XYZ A 1.2 1 c1 nothing nothing 2013-02-01 00:00:00 2013-02-05 00:00:00 user2 comments12
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Techanalyst
    i have 2 tables with one to many relations
    how are these tables related? do you expect to perform a join or a union?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    how are these tables related? do you expect to perform a join or a union?
    the unique id in the test table and the testid in override table are same (primary key and foreign key) join or union dosn't matter it should work
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0

    Unhappy


    please help me...
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Techanalyst
    the unique id in the test table and the testid in override table are same (primary key and foreign key)
    so the row with uniqueid=1 in test (AA234) is actually related to the row with uniqueid=1 in ovride (BB678) ??

    somehow, i really don't think so

    Originally Posted by Techanalyst
    join or union dosn't matter it should work
    they are fundamentally different, and you should realize what that difference is
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    Originally Posted by r937
    so the row with uniqueid=1 in test (AA234) is actually related to the row with uniqueid=1 in ovride (BB678) ??

    somehow, i really don't think so

    they are fundamentally different, and you should realize what that difference is
    Think you read it wrong as I did first time.
    the unique id in the test table and the testid in override table are same (primary key and foreign key)
    But this doesn't explain how he can have the same id twice:
    1 2 BB678 1 comments... user3 2013-02-03 00:00:00 user1
    1 4 CC911 1 comments12.. user2 2013-02-05 00:00:00 user2
    @OP
    Are the examples "real" or did you create them 'out of the blue'

    Have you tried anything.
    Based on you result example, you should follow r937 suggestion about looking into the JOIN syntax.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by MrFujin
    Think you read it wrong as I did first time.


    But this doesn't explain how he can have the same id twice:


    @OP
    Are the examples "real" or did you create them 'out of the blue'

    Have you tried anything.
    Based on you result example, you should follow r937 suggestion about looking into the JOIN syntax.

    this is real ..

    same id was my typo error .i have edited that now
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    Guys,

    I got solution .this one is working for me.Please let me know you have any suggestion to improve this query

    Code:
    select fin.* from (Select t.uniqueid as UniqueId,t.barcode as barcode,t.vendor as Vendor,t.Mod as Mod,t.Version as Version,
    t.result as TResult, t.datetested as Actualtesttime ,t.user as TUser,t.ovrpass as Result,t.ovrtime as time,
    t.ovruser as User,t.comment as Comment,t.Category as Category,t.FDetails as FDetails,t.ODetails as ODetails,
    from
    (
        SELECT c.uniqueid,c.barcode,c.vendor,c.Mod,c.Version, c.pass,c.datetested,o1.pass as ovrpass,o1.timestamp as ovrtime,
        c.Category,t.FDetails,t.ODetail,o1.comment,c.user,o1.user as ovruser
        FROM Test c
        left JOIN (select * from Ovride o order by o.timestamp desc)as o1
                    ON c.UniqueID = o1.TestId
         where c.datetested between '2013-02-01 00:00:00' and '2013-03-06 00:00:00'
        ORDER BY c.datetested DESC) as t 
    GROUP BY  t.barcode) fin                  
    order by fin.Actualtesttime desc

IMN logo majestic logo threadwatch logo seochat tools logo