Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: duplicate record

    Hi,
    I have 2 tables on DB2 version 6

    Tab_A
    id name
    1 shop1
    2 shop2

    Tab_B
    id_tab_a keyword
    1 book
    1 bookstore
    1 handbook
    2 restaurant

    I need to write a SQL query that find who have 'book' as keyword on Tab_B, but with no duplicated record. If i write:
    Select distinct id, name left join Tab_B on id=id_tab_a where
    keyword like '%book%'
    obtain 3 duplicated records.

    Thanks,
    Jhon

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Wouldn't you just need something like:

    select distinct a.id,a.name from tab_b as b, tab_a as a
    where (b.id_tab_a = a.id) and (b.keyword like '%book%')

    Andy

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    Really, i have join on other four table by id of table_a, it will work?

    Thank You for the answer.
    John

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    John,
    can you be more specific. Like maybe show the layouts of the tables and what you want from the query? I can help with specifics, not generalities, since I do not know your setup.

    Andy

    Originally posted by fartman
    Really, i have join on other four table by id of table_a, it will work?

    Thank You for the answer.
    John

  5. #5
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: duplicate record

    Select distinct
    id,
    name
    left outter join Tab_B
    on id=id_tab_a
    where keyword like '%book%'
    and id is null

  6. #6
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18

    Re: duplicate record

    You can use the statement
    select a.name
    from tab1 a
    where 1 = ( select count(*)
    from tab2 b
    where b.id = a.id and b.keyw like '%book%' )

    Saludos, Antonio.

  7. #7
    Join Date
    Oct 2003
    Posts
    6

    Re: duplicate record

    Problem solved with:

    select id,name from tab_a
    left join tab_b on id=id_tab_a where keyword like '%book%'
    group by id,name

    Thank you to all.
    Jhonny

    Originally posted by aloz
    You can use the statement
    select a.name
    from tab1 a
    where 1 = ( select count(*)
    from tab2 b
    where b.id = a.id and b.keyw like '%book%' )

    Saludos, Antonio.

Posting Permissions

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