CODEWITHSUNDEEP
sql-servert-sql
loren
loren

Reputation: 13

Joining a third table, but not a typical join

This is my query right now:

 SELECT
 a.stacct, b.hacct, a.stfrqcode, b.thfrqcode 
 FROM st a
 LEFT JOIN th b
 ON b.hacct = a.stacct

The issue: I have a third table that has the relation of each stfrqcode to each thfrqcode (for example ax = d4); problem is how do I pull back everything and tell if they match? In the reference table there are two columns stfrqcode and thfrqcode -- they have different values in each column.

Schema

table st:

stacct,

stfrqcode

table th:

hacct,

thfrqcode

table frqcodes:

thfrqcode,

stfrqcode

data from frqcodes

ns x00
ed x22
zs x33
ao x44

Result set should be: stacct, hacct, and basically a yes/no if there is a match between stfrqcode and thfrqcode.

Upvotes: 1

Views: 82

Answers (3)

Gareth
Gareth

Reputation: 936

Assuming I understand your table structure (with some dummy values I created):

Table st

stacct    stfrqcode
fred         A
mary         B
joseph       C

Table th

hacct     thfrqcode
fred         J
mary         H

Table relation

stfrqcode   thfrqcode
   A            J
   B            Q

and you are wanting this solution (I removed hacct as the value can only be either equal to stacct or null if there is no match):

stacct    match
fred       yes
mary       no
joseph     no

this is the query I would try:

SELECT
 a.stacct, (CASE WHEN c.strfrqcode IS NULL THEN 'no' ELSE 'yes' END) AS match
 FROM st a
 LEFT JOIN th b
 ON b.hacct = a.stacct
 LEFT JOIN relation c
 ON c.strfrqcode = a.strfrqcode and c.thfrqcode = b.thrfrqcode and b.thrfrqcode IS NOT NULL

Upvotes: 1

William Rose
William Rose

Reputation: 971

If your frqcodes table isn't necessarily unique on (stfrqcode, thfrqcode), or just because you can, you may like to try an exists clause in the select list:

SELECT a.stacct, b.hacct, a.stfrqcode, b.thfrqcode,
       CASE WHEN EXISTS (SELECT 1
                         FROM frqcodes AS c
                         WHERE c.stfrqcode = a.stfrqcode
                           AND c.thfrqcode = b.thfrqcode)
            THEN 'Yes'
            ELSE 'No'
       END AS relationship_exists
FROM st a
     LEFT JOIN th b
     ON b.hacct = a.stacct

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40319

SELECT
   a.stacct
  ,b.hacct
  ,case when c.stfrqcode is null then 'No' else 'Yes' end IsMatch
 from st a
  left outer join th b
   on b.hacct = a.stacct
  left outer join ThirdTable c
   on c.stfrqcode = a.stfrqcode
    and c.thfrqcode = b.thfrqcode

...might be some typos in there, I couldn't debug it.

Upvotes: 3

Related Questions