Results 1 to 5 of 5

Thread: join problem

  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Question join problem

    I have 2 tables
    1st table= id,name ex; 2,bob
    2nd table= id,phonenumber ex; 2,433-3333 - 2,555-4444
    what I want is:
    2,bob,433-3333,555-4444

    I would like to have these joined,so the information is on the same row.
    similar to this:
    SELECT distinct parents.name,parents_1.name, parents.phonenumber, parents_1.phonenumber
    FROM parents INNER JOIN parents AS parents_1 ON parents.id = parents_1.id;

    Right now this gives me two many rows.
    There are two rows in the second table that belong to one row in the first. I want one row.

    Any help is appreciated. Thanks
    Last edited by blee1; 03-11-2004 at 10:09 AM.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You said there are duplicate rows in second table, but the query uses same table - parents.

    Assuming second table name is parents_1

    SELECT distinct parents.name,parents_1.name, parents.phonenumber, parents_1.phonenumber
    FROM parents
    INNER JOIN
    (select distinct name, phonenumber from parents_1) AS parents_1
    ON parents.id = parents_1.id;

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    Two tables

    table1:
    id name
    2 bob

    table2
    id phonenumber
    2 433-3333
    2 555-4444

    What I need is:

    id name phonenumber1 phonenumber2
    2 bob 433-3333 555-4444

    Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    select
    a.id,name,
    case a.id when a.id then min(phone) end as Phone1,
    case a.id when a.id then max(phone) end as Phone2
    from table1 a inner join table2 b on a.id = b.id
    group by a.id,a.name

    ------Sample Data --------------

    create table table1(id tinyint, name varchar(15))
    insert into table1 select 2, 'bob'


    create table table2(id tinyint, phone varchar(15))
    id phonenumber
    insert into table2 select 2, '433-3333'
    insert into table2 select 2, '555-4444'

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, Missouri, USA
    Posts
    4

    Lightbulb

    You must have some other key to distinguish the phone numbers for the same person.
    Say you have a column named ptype that can contain values like HOME, WORK then here's your query:

    select
    n.id, n.name,
    max(decode(p.ptype,'HOME',p.phonenum)) home,
    max(decode(p.ptype,'WORK',p.phonenum)) work
    from
    names n,
    phonenumbers p
    where
    n.id = p.id
    group by n.id, n.name

    If you have to add more possible phone number types like CELL, then you have to add a column to your select list
    max(decode(p.ptype,'CELL',p.phonenum)) mobile.

    P.S. It makes no difference which group function you are using in place of max(). It could be count or min. sum cannot be used because it will raise ORA-01722 invalid number for ptype
    d
    Last edited by ddrozdov; 04-12-2004 at 02:38 PM.

Posting Permissions

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