Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: Complex Query or just me?

    i have 2 tables. i want to compare 2 columns from the first table and display the contents in the second table (hard to explain)
    example:
    Code:
    [TableA]
    id             ans1      ans2
    =============================
    bob            abc       xyz
    joe            abc       www
    mike           def       www
    foo            def       xyz
    bar            abc       xyz
    
    [TableB]
    anscode            anstext
    =========================================
    abc                Abc is the first 3 letters
    def            	   DEF JAM music
    www                World Wide Web
    xyz                XYZ best 3 looking letters in the alphabet
    
    [Result should be]
    Column1                          Column2	                                Count
    ==========================================================================================
    Abc is the first 3 letters       XYZ best 3 looking letters in the alphabet         2
    Abc is the first 3 letters       World Wide Web                                     1
    DEF JAM music                    World Wide We                                      1
    DEF JAM music                    XYZ best 3 looking letters in the alphabet         1
    Beyond Limitation

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    select distinct
    b1.anstext, b2.anstext
    from tablea a, tableb b1, tableb b2
    where a.ans1 = b1.anscode
    and a.ans2 = b2.anscode;

  3. #3
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    thnx for the reply
    im going to try it out when i get home.

    leaving work now

    thnx
    Beyond Limitation

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, instead of DISTINCT, this requires a GROUP BY, because the COUNT is needed

    so to modify littlefoot's code slightly...
    Code:
    select b1.anstext
         , b2.anstext
         , count(*) as occurrences
      from tablea a
    inner
      join tableb b1
        on a.ans1 = b1.anscode
    inner
      join tableb b2
        on a.ans2 = b2.anscode
    group
        by b1.anstext
         , b2.anstext
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    perfect
    thats what i needed
    thnx

    just curious, how much different would it be if i wanted to show every possibly, meaning the of the counts would be 0 if the match never occurs. if it is a major add-on dont worry - not sure what the client really wants. thnx
    Beyond Limitation

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Gosh, Rudy ... didn't scroll right enough to see the "count" output column ... Sorry, Vextout.

    As of your last post: I'd say you'll need outer join to fetch such records.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, just change INNER to LEFT OUTER in the query i gave you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    thnx again

    i started playing with the final query i had that with the inner join trying to display all the possibilites with the counts and realized that the whole query has to be changed, but at least i know what to do now.

    thnx again for all the help
    Beyond Limitation

Posting Permissions

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