• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

help with an sql join query?

Hi,

I have two tables in my access database as follows.
I am trying to get the records those exist in tblMap2 for TranID = "Borrower" and does not exist in tblMap1. In the below tables I need to get the records d3 and d4 for TranID = "Borrower" because these records does not exist in tblMap1 where TranID = "Borr".

Can anybody please help me how to write this query?
Thanks.
tblMap1

TranID     FieldName
------     ---------
Borr       d1
Borr       d2
Grntr  	   d1
Grntr      d2
Grntr      d3
Cos        d1
Cos        d2
Cos        d3


tblMap2

TranID     FieldName
------     ---------
Borrower   d1
Borrower   d2
Borrower   d3
Borrower   d4
Guarantor  d1
Guarantor  d2
Guarantor  d3
Cosigner   d1
Cosigner   d2
Cosigner   d3

Open in new window

0
ipjyo
Asked:
ipjyo
  • 2
  • 2
  • 2
  • +4
6 Solutions
 
dportasCommented:
SELECT tblMap2.FieldName
FROM tblMap2
LEFT OUTER JOIN tblMap1
ON tblMap1.FieldName = tblMap2.FieldName
AND tblMap1.TranID = 'Borr'
WHERE tblMap2.TranID = 'Borrower'
AND tblMap1.TranID IS NULL;

RIGHT outer join is just a different syntax for exactly the same operation as LEFT outer join. Best stick to one or the other syntax. Mostly people seem to prefer the LEFT syntax 99% of the time so I recommend you use that too.
0
 
Michael VasilevskySolutions ArchitectCommented:
SELECT TranID FROM tblMap2 WHERE TranID NOT IN (SELECT TranID FROM tblMap1)
0
 
Bill PrewCommented:
I don't think you can do this based upon what you have shown here.  You seem to be indicating that you want a join om TranID, and for SQL to know that "Borrower" equals "Borr", but there is no way for it to know that.

~bp
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SharathData EngineerCommented:
try this

select *
  from tblMap2
 where TranID = 'Borrower'
   and FieldName not in (select FieldName from tblMap1 where TranID = 'Borr')

Open in new window

0
 
QlemoBatchelor and DeveloperCommented:
Bill is right. Either you have to hard-code correlance, or use the same TranIDs on both tables, or use a translation table containing both kinds of TranID, e.g. "Borrower", "Borr".

With hard-coded, it would look like this:

select  * from tblMap2 m2 where not exists (
  select * from tblMap1 m1
  where m1.TranID =
    case m2.TranID
      when 'Borr' then 'Borrower'
      when 'Grntr' then 'Guarantor'
      when 'Cos' then 'Cosigner'
    end
   and m1.FieldName = m2.FieldName
)
0
 
ipjyoAuthor Commented:
hi @dportas,

When I tried the query as you shown it is saying "JOIN expression not supported" and highlighting the below text after "AND" condition.

tblMap1.TranID = 'Borr'
0
 
hnasrCommented:
try this sample:

tblMap1Map2.mdb
0
 
ipjyoAuthor Commented:
hi @Sharath,

It is working fine with the query you have shown.

Thanks very much everybody for your help.
0
 
QlemoBatchelor and DeveloperCommented:
That solution fits 100% to your question, but are you certain you want your query restricted to only look for Borrowers?
0
 
Bill PrewCommented:
Wow.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now