#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    12
    Rep Power
    0

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


    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

    Code:
    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

    Code:
    <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>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    11
    Looks like you need to make your SQL a bit smarter instead of trying to handle the logic in CF. Perhaps add a subquery to your SELECT to count the occurrences of MemberNumber in MemTableB.

    You'll probably want to clean this up for your purposes, but here's the general idea. You might not even need to join the tables.

    Code:
    SELECT dbo.MembTableA.MemberNumber, dbo.MembTableA.StatusCode, dbo.membTableB.Status,
       (SELECT count(dbo.MembTableB.MemberNumber) FROM dbo.MembTableB WHERE dbo.MembTableB.MemberNumber = dbo.MembTableA.MemberNumber) as counter 
    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">
    Or instead of a subquery, you might use a GROUP BY clause with a COUNT() in your select.

    Comments on this post

    • kiteless agrees
    Last edited by rawk; September 11th, 2012 at 12:44 PM.

IMN logo majestic logo threadwatch logo seochat tools logo