Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    7

    Exclamation Unanswered: query returning duplicates

    I have written this query which searches the table and return all owners whose surnames are like 'BLACK'. It runs fine but it is returning duplicate rows. I think this may be a problem with my joins. Does anyone have any suggestions?

    Code:
    SELECT p.person_id FROM person p, owner o, buliding b, person_status ps
    WHERE o.owner_id = p.owner_id AND b.building_id = h.building_id
    AND p.person_id=ps.status_id AND ps.status_type='H' 
    and  o.surname LIKE '%BLACK';

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first suggestion would be to post the DDL for your tables. It would help us to help you if we knew the columns and keys for your tables.

    My next suggestion would be to use the SQL-92 join syntax if your MySQL version supports them, something like:
    Code:
    SELECT p.person_id
       FROM person p
       INNER JOIN owner o
          ON o.owner_id = p.owner_id 
       INNER JOIN buliding b
          ON b.building_id = h.building_id
       INNER JOINT person_status ps
          ON p.person_id=ps.status_id
       WHERE  ps.status_type='H' 
          AND o.surname LIKE '%BLACK';
    This makes me suspect several of your join predicates, but without knowing your primary and foreign key definitions I can't prove that these are your problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This query should be giving a syntax error:

    SELECT p.person_id FROM person p, owner o, buliding b, person_status ps
    WHERE o.owner_id = p.owner_id AND b.building_id = h.building_id
    AND p.person_id=ps.status_id AND ps.status_type='H'
    and o.surname LIKE '%BLACK';

    h.building_id but I don't see any alias h for any table.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jan 2013
    Posts
    7
    Quote Originally Posted by Pat Phelan View Post
    My first suggestion would be to post the DDL for your tables. It would help us to help you if we knew the columns and keys for your tables.

    My next suggestion would be to use the SQL-92 join syntax if your MySQL version supports them, something like:
    Code:
    SELECT p.person_id
       FROM person p
       INNER JOIN owner o
          ON o.owner_id = p.owner_id 
       INNER JOIN buliding b
          ON b.building_id = h.building_id
       INNER JOINT person_status ps
          ON p.person_id=ps.status_id
       WHERE  ps.status_type='H' 
          AND o.surname LIKE '%BLACK';
    This makes me suspect several of your join predicates, but without knowing your primary and foreign key definitions I can't prove that these are your problems.

    -PatP
    Thanks i gave this a try but it still seems to be returning duplicates. I have included a list of the tables and columns below. Can you see if there is anything i am doing wrong ?

    person table

    person_id pk
    person_num
    building_id
    owner_id
    address
    phone
    email

    building table

    building_id pk
    building_code
    building_name
    address
    phone
    fax
    email


    status table

    status_type
    status_id pk
    date_applied
    status_reason
    person_id

    owner table

    owner_id pk
    surname
    forename
    address
    telephone
    email
    job

  5. #5
    Join Date
    Jan 2013
    Posts
    7
    Quote Originally Posted by it-iss.com View Post
    This query should be giving a syntax error:

    SELECT p.person_id FROM person p, owner o, buliding b, person_status ps
    WHERE o.owner_id = p.owner_id AND b.building_id = h.building_id
    AND p.person_id=ps.status_id AND ps.status_type='H'
    and o.surname LIKE '%BLACK';

    h.building_id but I don't see any alias h for any table.
    This is just a typing error!

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some issues...
    (1) How to fix the typing error "h.building_id"?

    (2) Is "AND p.person_id=ps.status_id" right?
    Is it "AND p.person_id=ps.person_id"?

    (3) There was no person_status table in your list of the tables and columns.
    Is status table same as person_status table?
    If so, which name is right?

  7. #7
    Join Date
    Jan 2013
    Posts
    7
    (1)SELECT p.person_id FROM person p, owner o, buliding b, person_status ps
    WHERE o.owner_id = p.owner_id AND b.building_id = p.building_id
    AND p.person_id=ps.status_id AND ps.status_type='H'
    and o.surname LIKE '%BLACK';

    (2) This is right- the status_id holds person_ids

    (3)The status table is the person_status table. I have named it incorrectly.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the query you are running as opposed to eh query you are either typing here or making up.

    ferinstance what is buliding b?

    also In my books you should be using he JOIN syntax not the depreacted theta syntax:-where tablea.columnx = tableb.columny
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (2) This is right- the status_id holds person_ids

    (3)The status table is the person_status table. I have named it incorrectly.
    Then, I'm curious what are the difference of status_id and person_id in the person_status table.

    You wrote...
    ...

    status table

    status_type
    status_id pk
    date_applied
    status_reason
    person_id

    ...

Posting Permissions

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