SitePoint Sponsor

User Tag List

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

    producing 1 time, not 3 or 2 times.

    Code:
     data in myTable1
    
    (Family_ID)  (Family_Name_Blah)  
    (1)          (Fonda is a good family name.) 
    (2)          (I like the name Tailor.)
    
    data in myTable2
    
    
    (First_ID)  (Family_Number)  (First_Name)  
    (1)         (1)              (Henry) 
    (2)         (1)              (Jane) 
    (3)         (1)              (Tom) 
    (4)         (2)              (Robert) 
    (5)         (2)              (Liz)
    I have 2 tables like the above.
    myTable1 has 2 fields and myTabl22 has 3 fields.
    "Family_Number" field in myTable2 is the foreign key which is connected to myTable1

    Let's make the following SQL code and its result in the below

    Code:
     SQL code1
    select Family_Name, First_Name
    from myTable1, myTable2
    where Family_Name like '%Fonda%' and Family_ID=Family_Number
    
    result1
    
    Fonda is a good family name. Henry 
    Fonda is a good family name. Jane 
    Fonda is a good family name. Tom 
    
     SQL code2
    select Family_Name, First_Name
    from myTable1, myTable2
    where Family_Name like '%Tailor%' and Family_ID=Family_Number
    
    result2
    
    I like the name Tailor. Robert 
    I like the name Tailor. Liz
    I like to produce target result in the below

    Code:
    target result1
    Fonda is a good family name. Henry Jane Tom
    
    target result2
    I like the name Tailor. Robert  Liz
    Can I do that with your help?

    Thanks in Advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you are on mysql 4.1, you can use the new GROUP_CONCAT() function

    otherwise, do not try to do this with sql

    you can do it relatively easily in your scripting language

    what you are doing is "denormalizing" the result set from a one-to-many relationship into a one-to-one, where the first "one" is the grouping value, and the second "one" consists of a list

    if you are familiar with coldfusion, you would do it like this:
    Code:
    <cfoutput query="foo" group="Family_Name">
    <p>#Family_Name# <cfoutput> #First_Name#</cfoutput>
    </cfoutput></p>
    in other scripting languages, it's quite a bit more code
    r937.com | rudy.ca | 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,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your ColdFusion Code works. Thank you.


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
  •