Solved

access if-statement

Posted on 2008-10-16
5
853 Views
Last Modified: 2012-08-14
please give me the complete SQL to make an if-statement (with the field name "T/C") based on the following criteria and add it to  the attached code snippet
1.  if the field called "cust_ID" is not null in both "table C and table T, then I want the record to say "C/T"
2.  if  "cust_ID" is null in table C, but not null  in table T, then I want it to say "T"
3. if  "cust_ID" is null in table T, but not null  in table C, then I want it to say "C"

thank you very much
SELECT M.cust_ID
FROM qryConverterTransCustomers_01 AS C RIGHT JOIN (qryConverterTransCustomers_02 AS T RIGHT JOIN tmpMasterTable AS M ON T.cust_ID = M.cust_ID) ON C.cust_ID = M.cust_ID;

Open in new window

0
Comment
Question by:gringotani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22732715
Will c.cust_id AND t.cust_id ever be null?  If so, neither will be displayed with this query.  Regardless, this should handle C, T or T/C.

SELECT M.cust_ID
,iif(c.cust_id is not null, iif(m.cust_id is not null, 'T/C', 'C'), iif(m.cust_id is not null,'T','NEITHER'))
FROM qryConverterTransCustomers_01 AS C RIGHT JOIN (qryConverterTransCustomers_02 AS T RIGHT JOIN tmpMasterTable AS M ON T.cust_ID = M.cust_ID) ON C.cust_ID = M.cust_ID;

0
 

Author Comment

by:gringotani
ID: 22739635
Hi, thanks for answering my question.  Your SQl doesnt exactly match my conditons.  pls fix.  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22740272
Well if you tell me what it is/isn't doing that would make it a lot easier?
0
 

Author Comment

by:gringotani
ID: 22740744
the purpose of the if statement is to tell me if csut-ID is present in either table T or C. the if statement should not contain table M.  
1.  if the field called "cust_ID" is not null in both "table C and table T, then I want the record to say "C/T"
2.  if  "cust_ID" is null in table C, but not null  in table T, then I want it to say "T"
3. if  "cust_ID" is null in table T, but not null  in table C, then I want it to say "C"

thank you very much
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 50 total points
ID: 22743027
Got it.  And do you want t/c or c/t.  Because you said t/c at first and c/t now.  Or doesn't it matter?

This should do it.
SELECT M.cust_ID
,iif(c.cust_id is not null, iif(t.cust_id is not null, 'T/C', 'C'), iif(t.cust_id is not null,'T','NEITHER'))
FROM qryConverterTransCustomers_01 AS C RIGHT JOIN (qryConverterTransCustomers_02 AS T RIGHT JOIN tmpMasterTable AS M ON T.cust_ID = M.cust_ID) ON C.cust_ID = M.cust_ID;

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot tables in SQL 1 46
SQL Syntax 6 58
SQL Server how to create a DYNAMIC TABLE? 11 51
SQL join ...want to return one row 4 15
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question