www.webdeveloper.com
Results 1 to 6 of 6

Thread: Comparing three tables, selecting lowest value + company?

  1. #1
    Join Date
    Jun 2007
    Posts
    400

    Comparing three tables, selecting lowest value + company?

    Ok, so I basically have three tables.

    Company1
    opendt date
    time char(5)
    socsec char(11)

    Company2
    Same as above

    Company 3
    Same as above

    These tables all join on social security number (yeah I know it's bad practice, but that's how they do things here) for each customer. So every customer should have exactly 1 record for each company. And really, unless something got messed up earlier, each customer should have the same opendt for all 3 tables as well (so I think this can be disregarded?)

    All I need to do is, for each customer, find the "lowest" time and the company connected to that time, and then dump it all to a new table that would look something like this:

    New Table
    socsec char(11)
    company char(???)
    time char(5)


    And the data in the table should look something like this:

    111-11-1111 Company1 5:30
    222-22-2222 Company3 1:20
    333-33-3333 Company2 15:45
    444-44-4444 Company3 21:23

    So again, the final table would have each customer, and the company for which they had the lowest time, and that time.

    I know this is probably not that difficult but I don't have much experience comparing this kind of thing within MySQL itself. I'd generally use PHP for something like this, but I need to do it all with MySQL this time.

  2. #2
    Join Date
    Oct 2009
    Posts
    658
    Convert time to timestamp. Then join on social. Then group by social, then extract min timestamp, then format timestamp

    Code:
    SELECT s.social Min(s.t) FROM (
     SELECT * FROM (
      SELECT C1.social,  DATE('2012-01-01 ' + <thetime in hms>) As t FROM WTFCompany1 C1
      LEFT JOIN FOR THE SAME QUERY BUT ON C2 then On
    ) AS s
    ) k
    Just same concept just build the elements
    Good Luck

    Santos Systems

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Nah... do it like this:

    Code:
    SELECT sosec, min(opendt), company
    FROM (
      SELECT *, 'company1' as company FROM Company1 
        UNION ALL
      SELECT *, 'company2' as company FROM Company2
        UNION ALL
      SELECT *, 'company3' as company FROM Company3
    )
    GROUP BY sosec;

    You'll still need to concatenate opendt and opentime and turn that into a real timestamp (the way it's stored now is a mess for comparison).

    Edit: smack the person who stored time as char(5). (i feel like 'time' is a keyword and char(5).... wow, lol)
    Last edited by eval(BadCode); 08-03-2012 at 08:58 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  4. #4
    Join Date
    Oct 2009
    Posts
    658

    Talking

    Quote Originally Posted by eval(BadCode) View Post
    Nah... do it like this:

    Code:
    SELECT sosec, min(opendt) 
    FROM (
      SELECT * FROM Company1 
        UNION ALL
      SELECT * FROM Company2
        UNION ALL
      SELECT * FROM Company3
    )
    GROUP BY sosec;

    You'll still need to concatenate opendt and opentime and turn that into a real timestamp (the way it's stored now is a mess for comparison).

    Edit: smack the person who stored time as char(5).
    Doh right. Silly me
    Good Luck

    Santos Systems

  5. #5
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    I think my query is a little bit funky.

    I think this is a dangerously crazy fix and it warrants adding a note to the query explaining why:

    There's really no way to specify which `company` you mean in the outer query, because UNION ALL with sosec means violating the rules of a primary key (in other words, we're not using a primary key... we don't even have one to work with unless we concat(sosec, company)). Now that's not a problem by itself, but we have a field we're selecting `company` which isn't in an aggregate function and it isn't in the GROUP BY clause; so, which record will MySQL pull the `company` field from??? I'm not entirely sure, but I suspect it will choose it from the first record (in each group) using insert-ordering, and since you're wanting the company and opendt fields to go hand-in-hand (as part of the result) you'll need to tell MySQL to order them by time ascending in the inner query since you're wanting the minimum(opendt)... A long time ago, I convinced myself that having to use tricks like this is the result of bad schema design.

    Code:
    SELECT sosec, min(opendt), company
    FROM (
      SELECT *, 'company1' as company FROM Company1 
        UNION ALL
      SELECT *, 'company2' as company FROM Company2
        UNION ALL
      SELECT *, 'company3' as company FROM Company3
      ORDER BY opendt ASC, `time` ASC
    )
    GROUP BY sosec;
    Last edited by eval(BadCode); 08-03-2012 at 09:32 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  6. #6
    Join Date
    Oct 2009
    Posts
    658
    Quote Originally Posted by eval(BadCode) View Post
    A long time ago, I convinced myself that having to use tricks like this is the result of bad schema design.
    Understatement. The fact that you had to do a union all on three separate tables is a testament by itself. I assume that this is for a reporting functionality based on how the output data needs to be structured. That being said eval's query will be terribly slow (mine is worse). Please tell us that at least you have an index on the sosec field. If they complain. Time to suggest normalizing the tables
    Good Luck

    Santos Systems

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles