Complex Query or just me?

vextout · #1 (**permalink**) 12-23-04, 14:04

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

Littlefoot · #2 (**permalink**) 12-23-04, 16:28

select distinct
b1.anstext, b2.anstext
from tablea a, tableb b1, tableb b2
where a.ans1 = b1.anscode
and a.ans2 = b2.anscode;

vextout · #3 (**permalink**) 12-23-04, 16:49

thnx for the reply
im going to try it out when i get home.

leaving work now

thnx

r937 · #4 (**permalink**) 12-23-04, 22:20

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

vextout · #5 (**permalink**) 12-23-04, 23:58

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

Littlefoot · #6 (**permalink**) 12-24-04, 01:29

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.

r937 · #7 (**permalink**) 12-24-04, 01:36

yes, just change INNER to LEFT OUTER in the query i gave you

vextout · #8 (**permalink**) 12-25-04, 00:50

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

Internet Services

Web Development

Digital Marketing

Consumer Tech