Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2011
    Location
    Charleston, SC
    Posts
    12

    Unanswered: Getting the Distinct Records

    I have a Multi-valued table that has several fields, including a FK to the Main table, and a code field. However, for this I only problem I'm only interested in the ID field and the Code fields

    here would be an example

    ID CODE
    ------- ------------
    12345 AA
    12345 AB
    12344 AA
    12346 AA
    12348 AA
    12348 AB


    I want to return all ROWS and/or ID's that have AA but don't have an AB

    thanjs
    Last edited by vswindell; 01-06-11 at 12:50. Reason: I want to see more results

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    SELECT id
    FROM the_table_with_no_name
    WHERE code IN ('AA', 'AB')
    GROUP BY id
    HAVING COUNT(*) = 1

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id
      FROM daTable
    GROUP
        BY id
    HAVING COUNT(CASE WHEN code='AA' THEN 'humpty' END) > 1
       AND COUNT(CASE WHEN code='AB' THEN 'dumpty' END) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select id from tablea where code = 'aa'
    minus
    select id from tablea where code = 'ab'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2011
    Location
    Charleston, SC
    Posts
    12

    Retuning the Row

    Thanks,

    Is there away to return the rows of those records that don't have 'AB'

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there away to return the rows of those records that don't have 'AB'
    yes
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    select cols from ur_table a
    where code = 'aa'
    and not exists (select 1 from ur_table b
    where a.id = b.id
    and code = ab')
    Dave

  8. #8
    Join Date
    Jan 2011
    Location
    Charleston, SC
    Posts
    12

    Willl that work

    I guess I should have said that there are other codes in the table other than AA, and AB, there could be AC, and AD

    I want the ones that have AA but not AB

    any assistance

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vswindell View Post
    any assistance
    see post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2011
    Location
    Charleston, SC
    Posts
    12

    technical stanniv

    The Minus one?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Individual posts in each thread are clearly NUMBERED near top right corner.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vswindell View Post
    The Minus one?
    no, the one before that

    O.M.G. i just read it again and saw that it has an error

    pls change > 1 to > 0

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2011
    Location
    Charleston, SC
    Posts
    12

    Macceen Means

    Now to make this interesting.

    I used the minus statement to return the whole row back from the table.

    Select * from ThatTable where Code='aa'
    Minus
    Select * from ThatTable where code ='ab'

    Now can I join this table with another query

    for example

    If I have another table that I did a Query

    Select T_PID, Date from ThisTable where code in ('AS','AR')

    Is it possible to join the output of the minus query with with select from this table? and How?

    The things I tried dinna work

    The ThisTable.T_PID is the same as the ThatTable.TPid

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select T_PID, Date from ThisTable where code in ('AS','AR')
    and T_PID in (Select id from tablea where code = 'aa'
    minus
    select id from tablea where code = 'ab')
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

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