SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid 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 Family_Name or First_Name

    Code:
    myTable1
    ID1   Family_Name   Family_Ace
    
    1      Tailor            0 
    2      Fonda             0
    3      Jackson           1
    
    myTable2
    
     ID2  Table1_ID    First_Name        elder  First_Ace  
    
    1       1             Robert          1       0 
    2       1             Liz             0       1 
    3       3             Michael         1       0 
    4       2             Jane            0       1 
    5       2             Henry           0       0 
    6       3             Tom             0       0
    I have the above data in myTable1 and myTable2.
    And I have the below CFML code.

    Code:
    CFML Code
    <cfquery datasource="DS" name="test">
    	select Family_Name, First_Name, Family_Ace, First_Ace
    	from myTable1, myTable2
    	where ( ID1=Table1_ID and Family_Ace=1 and elder=1) or 
                          ( ID1=Table1_ID and First_Ace=1)		
    	order by ID1 
    </cfquery>
    
    
    <cfoutput query="test">
    	<cfif Family_Ace is 1>	
    		#Family_Name#
    	<cfelseif First_Ace is 1>
    		#First_Name#
    	</cfif><br>	
    </cfoutput>
    The CFML code will produce the following result.
    Code:
    result
    
    Liz 
    Jane 
    Jackson
    I like to produce the result in Alphabetical order.
    My Target result would be like the following.
    Code:
    Target Result
    
    Jackson
    Jane
    Liz
    Thanks in Advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    <cfquery datasource="DS" name="test">
    select Family_Name
         , First_Name
         , Family_Ace
         , First_Ace
         , case when Family_Ace=1
                then Family_Name
                else First_Name
            end as Display_Name
      from myTable1
    inner
      join myTable2
        on ID1 = Table1_ID 
     where Family_Ace=1 and elder=1
        or First_Ace=1
    order 
        by Display_Name 
    </cfquery>
    
    
    <cfoutput query="test">
     #Display_Name#<br />
    </cfoutput>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    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)
    Thanks, it works.


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
  •