Output query and alert if there is a duplicate between 2 tables

[swansong77]

I have 2 tables MembTableA and MembTableB. If a MemberNumber exists on both tables I want to somehow put an alert in my coldfusion output (I have an example "CFIF" in my code below) - and only show the 1 result from MemberTableA. Below is my query and output - can someone assist?

Query in CFC

SELECT dbo.MembTableA.MemberNumber, dbo.MembTableA.StatusCode, dbo.membTableB.Status
FROM dbo.MembTableA LEFT OUTER JOIN
dbo.membTableB ON dbo.MembTableA.MemberNumber = dbo.membTableB.MemberNumber
Where dbo.MembTableA.status IS NULL
AND (dbo.MembTableA.StatusCode) = <cfqueryparam value="555" cfsqltype="cf_sql_varchar">
or dbo.membTableB.Status = <cfqueryparam value="999" cfsqltype="cf_sql_varchar">
Output code on page

<cfinvoke component=.....
method="MemberNumberLst"
returnvariable="MemberNumberLst">

<table>
<tr>
<th>Member Number</th>
<th>Status Code</th>
</tr>
<cfoutput query="MemberNumberLst">
<tr>
<cfif MemberNumberLst.MemberNumber is on both tables ..... >
<td>#MemberNumber# **on both tables**</td>
<cfelse>
<td>#MemberNumber#</td>
</cfif>
<td>#StatusCode#</td>
</tr>
</cfoutput>
</table>

 

[r937]

simplifying your query by using table aliases...

SELECT a.MemberNumber
     , a.StatusCode
     , b.Status
  FROM dbo.MembTableA AS a
LEFT OUTER 
  JOIN dbo.membTableB AS b
    ON b.MemberNumber = a.MemberNumber
 WHERE a.status IS NULL
   AND a.StatusCode = <cfqueryparam value="555" cfsqltype="cf_sql_varchar">
    OR b.Status = <cfqueryparam value="999" cfsqltype="cf_sql_varchar">

are you sure you wrote the WHERE clause correctly?

because ANDs take precedence over ORs, so it's actually evaluated like this --

 WHERE (
       a.status IS NULL
   AND a.StatusCode = <cfqueryparam value="555" cfsqltype="cf_sql_varchar">
       )
    OR b.Status = <cfqueryparam value="999" cfsqltype="cf_sql_varchar">

see the difference? did you really want it like that?

as to your question, just add another column to the SELECT list --

SELECT a.MemberNumber
     , a.StatusCode
     , b.Status
     , CASE WHEN b.MemberNumber = a.MemberNumber
            THEN 'yes'
            ELSE 'no'  END  AS on_both 
  FROM ...

then you can do this --

<cfif MemberNumberLst.on_both is 'yes' >

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[swansong77]

Thanks. I am not sure If my WHERE is correct. I want it to show all MemberNumbers from table A and alert if there is a duplicate on B. I only want it to show the MemberNumber on B IF there is one on A. Can you help me re-write that?

Thanks again.

 

[r937]

I am not sure If my WHERE is correct.

what is the purpose of the second queryparam? are you looking only for status 999 rows that match the member numbers, or what?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon