Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33

    Unanswered: rows and select weirdness ....

    I have a select statement :

    select x.trace_id, x.log_datetime, y.log_datetime from gen_stat_tmp as x inner join gen_stat_tmp as y on x.trace_id = y.trace_id and x.systrace = y.systrace and x.reference = y.reference and x.message_type = '0200' and y.message_type = '0210' and x.trace_id like '%XPR%'

    that runs on a table that has :
    mysql> select count(*) from gen_stat_tmp;
    +----------+
    | count(*) |
    +----------+
    | 9747 |
    +----------+
    rows - WHY then do I get a result that has :
    61104 rows in set

    returned ?????????????

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but you are selecting from 2 copies of the table, not one

    consider this sampletable which has only 5 rows --

    foo bar
    A 1234
    A 3467
    A 5678
    B qwer
    B asdf

    if you run this query --

    select x.foo
    , y.foo
    from sampletable as x
    inner
    join sampletable as y
    on x.foo = y.foo

    you should get 13 rows back


    rudy

  3. #3
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33
    Originally posted by r937
    yes, but you are selecting from 2 copies of the table, not one

    consider this sampletable which has only 5 rows --
    you should get 13 rows back
    rudy
    ok - but why ??
    and how can I get just 5 rows ??? instead of the 13 ??

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why? because that's the data that's in the table, and when you join that way, that's what you get

    how do you get only 5? a different query

    go back to your own example...
    Code:
    select x.trace_id
         , x.log_datetime
         , y.log_datetime 
      from gen_stat_tmp as x 
    inner 
      join gen_stat_tmp as y 
        on x.trace_id = y.trace_id 
       and x.systrace = y.systrace 
       and x.reference = y.reference 
       and x.message_type = '0200' 
       and y.message_type = '0210' 
       and x.trace_id like '%XPR%'
    why do you join the table to itself? what are you trying to achieve?

    rudy

  5. #5
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33
    Originally posted by r937
    why do you join the table to itself? what are you trying to achieve?
    I am trying to do a calculation of the time difference between a 200 and a 210 message_type.

    by grouping lines in the database that are the same......

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you are on the right track

    61 thousand rows is too many to use for debugging

    narrow it down with a filter on x

    move your filter conditions out of the ON and into the WHERE --
    Code:
    select x.trace_id
         , x.log_datetime
         , y.log_datetime 
      from gen_stat_tmp as x 
    inner 
      join gen_stat_tmp as y 
        on x.trace_id   = y.trace_id 
       and x.systrace   = y.systrace 
       and x.reference  = y.reference 
    
     where x.message_type = '0200' 
       and y.message_type = '0210' 
       and x.trace_id like '%XPR%'
    now put more and more restrictive conditions into the where clause until you get only a few x's

    then see how they are matched up with y's

    this will help you find a better set of conditions


    rudy

  7. #7
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33
    Originally posted by r937
    now put more and more restrictive conditions into the where clause until you get only a few x's

    then see how they are matched up with y's

    this will help you find a better set of conditions


    ok - this is definatly better - only have 150 rows with the above example - adding more columns brings it to 23 .....

    MANY THANKS

Posting Permissions

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