SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More joining problems...

    hi hi, click here before we start:

    http://webclickhosting.com/wos/sql.php3


    you can see the SQL statement I am using, and the results listed below that.

    NOW, I have gotten the bad rows of mis-represented clients to go away (ie i got the clients.cid column to match with the wos.cidref column) HOWEVER, I only want the listings that contain the LATEST employee reference.

    In the statusLog table there are several columns, jobid, name, and date. The "date" field is a timestamp from MySQL. in the form of 12 characters...uhm...year-month-day-hour-minute-second i think. Anywho -


    I want only the results with the MOST RECENT entry from the statusLog table. How do I do that?



    Also, how do i still list the rows that don't have any entries in the statusLog table? Because as far as I know, if I put in a "WHERE statusLog.jobid = wos.jobid" i'll end up getting rid of all the jobs that don't have any entried in the statusLog table. How can I get around that?


    Thanks mucho!
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  2. #2
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    humm...

    Guess I am on my own with this one?
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't see the sql query on that page you link to. It would help if you give use the sql query, and a clear explaination of what data you want in the result set.

  4. #4
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    I can't see the sql query on that page you link to. It would help if you give use the sql query, and a clear explaination of what data you want in the result set.

    uhm....there are only two things on that page....the query text at the very top first thing, and a table of the results. How can you not see that? .....


    What I want is this, right now I am getting a lot of results that are invalid - ie, I don't want those. I am getting all the combinations with the entries from the statusLog table. In the statusLog table, there is a MySQL timestamp column named "date". I would like only the most recent entries to show up in the final results. Also, I would like entries to show up that have NO entries in the statusLog database.


    I can't explain it much better than that for me.
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - the SQL was not showing in my browser because I use an obscure browser - iCab - to view vBulletin forums. Don't ask Anyway, it doesn't start parsing text as HTML until it encounters a valid HTML tag so the SQL statement was not showing.

    Now I hope this helps. What you need to do is INNER JOIN tables wos and clients and LEFT OUTER JOIN the resulting set to table statusLog.

    As for showing only the most recent record for each group of results according to statusLog.date - that has got me stumped and I'm thinking that this might require a sub-query (which MySQL doesn't support). I can't get my head around that. But you can always sort that out in your PHP code once you have a result set. Well, I am assuming here that you mean you want the most recent record for each jobid or whatever. If you just want to limit the entire record set to records after a certain date that is easy, you just throw a condition into your where clause.

    Here are some test tables, test data and a query I have written (and the result set) which I hope demonstrates how to achieve the type of joins that you require.
    Code:
    CREATE TABLE Test1 (id INT, str CHAR(12));
    CREATE TABLE Test2 (id INT, str CHAR(12));
    CREATE TABLE Test3 (id INT, str CHAR(12));
    
    INSERT INTO Test1 VALUES
    (1, 'string1'), (2, 'string2'), (3, 'string3'), (4, 'string4');
    
    INSERT INTO Test2 VALUES
    (2, 'string2'), (3, 'string3'), (4, 'string4'), (5, 'string5'); 
    
    INSERT INTO Test3 VALUES 
    (3, 'string3'), (4, 'string4'), (5, 'string5');
    
    mysql> select * from Test1;
    +------+---------+
    | id   | str     |
    +------+---------+
    |    1 | string1 |
    |    2 | string2 |
    |    3 | string3 |
    |    4 | string4 |
    +------+---------+
    4 rows in set (0.00 sec)
    
    mysql> select * from Test2;
    +------+---------+
    | id   | str     |
    +------+---------+
    |    2 | string2 |
    |    3 | string3 |
    |    4 | string4 |
    |    5 | string5 |
    +------+---------+
    4 rows in set (0.00 sec)
    
    mysql> select * from Test3;
    +------+---------+
    | id   | str     |
    +------+---------+
    |    3 | string3 |
    |    4 | string4 |
    |    5 | string5 |
    +------+---------+
    3 rows in set (0.00 sec)
    
    Now here is the sql query. It creates a standard inner join between Test1, Test2 and a
    LEFT JOIN between that set and table Test3. As you can see from the result set what
    this does is that it includes all the results from the left hand set (the inner join of 
    Test1, Test2) even where there is no matching record in Test3. You can see that the 
    LEFT JOIN requires us to specifiy the join conditioin "ON Test3.id = Test1.id"
    
    mysql> SELECT Test1.str, Test2.str, Test3.str
        -> FROM Test1, Test2
        -> LEFT JOIN Test3 ON Test3.id = Test1.id
        -> WHERE Test1.id = Test2.id
        -> GROUP BY Test1.id;
    +---------+---------+---------+
    | str     | str     | str     |
    +---------+---------+---------+
    | string2 | string2 | NULL    |
    | string3 | string3 | string3 |
    | string4 | string4 | string4 |
    +---------+---------+---------+
    3 rows in set (0.00 sec)
    I hope that helps
    Last edited by freakysid; Jun 28, 2001 at 14:05.


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
  •