# Thread: Complex Query or just me?

1. Registered User
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```

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
select distinct
b1.anstext, b2.anstext
from tablea a, tableb b1, tableb b2
where a.ans1 = b1.anscode
and a.ans2 = b2.anscode;

3. Registered User
Join Date
Jan 2003
Location
New York
Posts
160
im going to try it out when i get home.

leaving work now

thnx

4. SQL Consultant
Join Date
Apr 2002
Location
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```

5. Registered User
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

6. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
yes, just change INNER to LEFT OUTER in the query i gave you

8. Registered User
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

#### Posting Permissions

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