Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: inner join doubles the result...

    i have 2 inner joins for my 3 tables(coding_log,user,batch_log), however the result is doubled
    e.g.
    records = 50
    overtime = 05:00

    but the result of the query is
    records = 100
    overtime = 10:00

    when i remove 2nd inner join the result is right.

    FROM
    coding_log
    inner join `user` on (coding_log.last_name = `user`.last_name)
    inner join batch_log on (coding_log.job_no = batch_log.job_number)

    what seems to be the problem?

    thanks in advance
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Your left join on lastname

    YOU CANT JOIN ON LASTNAME !!!
    Prob there are more Mr.Smith or Miss.Smith(or both) working there.

    Please see other post ass well
    We cant help you if you give half of a problem.
    i.e: what day is it 6 days after .....
    After wich day you would ask , well exactly !!!!

    Whats your where condition !?!?
    Please paste whole SQL and a row from each table or pic of table stucture.
    Last edited by Marvels; 02-12-08 at 07:22.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    the query is:
    SELECT
    coding_log.date,
    coding_log.last_name,
    `user`.tl,
    coding_log.job_no,
    coding_log.job_name,
    max(coding_log.time_end) as 'time out',
    min(coding_log.time_start)as 'time in',
    if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
    subtime(max(coding_log.time_end),'16:00'),
    if(min(coding_log.time_start) and max(coding_log.time_end) <= '16:00', 'no ot',
    subtime(max(coding_log.time_end),min(coding_log.ti me_start)))) as 'Over Time',

    sum(coding_log.total_batches_coded) as 'total batches coded',
    SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(coding_log.tim e_end,coding_log.time_start))))
    as 'Total Man Hours'

    FROM
    coding_log
    inner join `user` on (coding_log.last_name = `user`.last_name)
    inner join batch_log on (coding_log.job_no = batch_log.job_number)

    group by
    coding_log.`date`,
    coding_log.last_name,
    coding_log.job_no

    order by
    coding_log.`date` desc, coding_log.time_start desc
    //*the result is doubled!!!i dont know why?!!!*//

    can you analyze this?

    thanks in advance
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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