SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Threaded View

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Order by Name with conditional output

    Code:
    myTable1
    ID1   Family_Name   Family_Ace
    
    1      Tailor            0 
    2      Fonda             0
    3      Jackson           1
    
    myTable2
    
     ID2  Table1_ID    First_Name        relation  First_Ace  
    
    1       1             Robert          11       0 
    2       1             Liz             12       1 
    3       3             Michael         11       0 
    4       2             Jane            12       1 
    5       2             Henry           11       0 
    6       3             Tom             12       0
    I have the above data in my two tables.
    And I made the below code.

    Code:
    CFML Code
    
    <cfset KeyWord="a">
    
    <cfquery datasource="DS" name="test">		
    	(	select  ID1, Family_Name as Name, Family_Ace as Ace_Relation 
    		from myTable1
    	
    		where Family_Name like binary '%#KeyWord#%' 	
    	)	
    	
    	UNION
    	
    	(	select Table1_ID, First_Name as Name, Relation as Ace_Relation
    		from myTable2
    	
    		inner join  on myTable2.Table1_ID = myTable1.ID1	
    		
    		where First_Name like binary '%#KeyWord#%'	
    	)	
    		     
    	order by Name  
    </cfquery>
    
    
    <cfoutput query="test">
    
    	#Name#,
    
    	<cfif Ace_Relation is 1 or Ace_Relation is 0>
    		Yes
    	<cfelseif Ace_Relation is 11 or Ace_Relation is 12>	
    		No
    	</cfif><br>
    
    </cfoutput>
    But the code produce an Error Message(Error Executing Database Query.), especially in the clasue " where First_Name like binary '%#KeyWord#%' ".

    Do you see what is the problem?

    My Target Result would be like following...

    Code:
    Target Result
    
    Fonda, Yes
    Jackson, Yes
    Jane, No
    Michael, No
    Tailor, Yes
    
    
    - Please Notice They are ordered by alphabet
    Thanks in Advance
    Last edited by dotJoon; Apr 27, 2004 at 14:54.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •