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

[earwig75]

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>

[Carl Von Stetten · Accepted solution]

earwig75,

Since you are doing a left outer join, if there is a matching MemberNumber in MembTableB, you just need to check if the dbo.membTableB.MemberNumber column is not blank.  You'll need to add the dbo.MembTableB.MemberNumber column to your SELECT statement.  However, because ColdFusion's query results don't carry fully qualified column names (i.e. dbo.membTableA.MemberNumber and dbo.membTableB.MemberNumber will both be listed as MemberNumber), you will have to use an alias on that column.  Also, if you use aliases on table names, it will simplify the rest of your query.  Here's a cleaner version:

SELECT     tblA.MemberNumber, tblA.StatusCode, tblB.Status, tblB.MemberNumber as MemberNumberB

FROM         dbo.MembTableA  AS tblA LEFT OUTER JOIN

                      dbo.membTableB AS tblB ON tblA.MemberNumber = tblB.MemberNumber

                                Where tblA.status IS NULL

                                AND (tblA.StatusCode) = <cfqueryparam value="555" cfsqltype="cf_sql_varchar">

                                or tblB.Status = <cfqueryparam value="999" cfsqltype="cf_sql_varchar">

Will MemberNumber always appear in MembTableA and only sometimes in MembTableB?  If so, then the modified query should work.  If the number might appear in MembTableA sometimes, or might appear in MembTableB sometimes, then the query probably needs to be redesigned.  If MemberNumber will always appear in MembTableA, your <cfif> could then look like this:

<cfif MemberNumberLst.MemberNumberB <> ''>

Also, you might want to check the location of the parenthesis in your WHERE clause.  AND operators take precedence over OR operators, so this query is going to either find records where both the tblA.status is null AND tblA.StatusCode equals '555', or records where tblB.Status equals '999'.  Is this precisely what you want?

HTH,

-Carl V.

[earwig75]

@Carl

table A will always have a membernumber. table B only sometimes.

When I output from the same query you gave me it still shows both member numers from both tables. Even using the alias tblB.MemberNumber as MemberNumberB... on my output I only have #MemberNumber# but it is still outputting the IMEI from the other table.  Is there a way to only display one?

[Carl Von Stetten]

@earwig75,

Can you elaborate on:

When I output from the same query you gave me it still shows both member numers from both tables.

Maybe an example screenshot to show what you mean?  Maybe an example dump of the results from your CFC query?

[earwig75]

@Carl, I apologize, it isn't showing both. I did have to use neq instead of <> in my CFIF since its CF not SQL.

Thanks for everything.

[Carl Von Stetten]

Sorry about that.  Yes, NEQ or IS NOT would be the appropriate operators.  Alternatively, you could also do <CFIF Len(MemberNumberLst.MemberNumberB)> which would be true if anything other than a NULL or blank value was in that column of the table.

-Carl V.

[Dan_Bracuk]

I suggest looking at your database design.  You might be better off with just one member table.