SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  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)

    join ON fullName

    Code:
    last
    
    (id) last_name
    (1)  Tailor
    (2)  Williams
    (3)  Smith
    
    full
    
    (id) full_name
    (1)  Mary Smith
    (2)  Jane Tailor
    (3)  Tom Williams
    (4)  Mary Tailor
    I have 2 tables like the above.

    When the variables $myFirst is "Mary", the query below produces the result below.
    Code:
    query
    
    select CONCAT('$myFirst',' ', last_name) as fullName
    from last
    
    result
    
    Mary Tailor
    Mary Williams
    Mary Smith
    I like to produce my target result below with the variables "Mary".
    Code:
    target result
    
    Mary Smith
    Mary Tailor
    The trial query below is one of my trials for it, but failed.
    Code:
    trial query
    
    select CONCAT('$myFirst',' ', last_name) as fullName
    left join full ON fullName=full_name
    from last

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've read a lot of these posts coming from you, and time and time again Rudy answers you and seriously - you should now at least have slight knowledge how to do queries properly. Not only are your tables badly designed, I can't even see you spending more than 5 minutes of your own discovering things trough trial and error.

    I'm sorry if it sounds rude, but reading PHP and MySQL forums - you've got a question every day. Wouldn't it be better if you hired someone to do your job for you than ask questions which you should already know answers to?

  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)
    Hello, furicane.
    Take it easy, please.

    Quote Originally Posted by furicane View Post
    I've read a lot of these posts coming from you,
    Thanks for reading my Posts

    Quote Originally Posted by furicane View Post
    time and time again Rudy answers you and seriously
    Yes, that's true.
    Without him, I might leave this field.
    (Although I work for another field for making money, I still want to achieve my work in this field.)

    Quote Originally Posted by furicane View Post
    you should now at least have slight knowledge how to do queries properly.
    Although I don't even know you, I like to say that you'd better not to measure by your ruler only.
    (I am afraid I don't remember that you do give me any helping answer to my question.)


    Quote Originally Posted by furicane View Post
    Not only are your tables badly designed,
    Why do you think my table is badly designed?

    Quote Originally Posted by furicane View Post
    I can't even see you spending more than 5 minutes of your own discovering things trough trial and error.
    I don't understand this exactly.
    but
    I guess that you think I post a question without long think.
    If my guess is true, Your saying is not fact.
    Before I post something I think a log these days.

    Quote Originally Posted by furicane View Post
    I'm sorry if it sounds rude,
    because I don't know your character well, I don't understand what is the purpose of your saying.
    I think whether you're rude or not is depending to your purpose.

    Quote Originally Posted by furicane View Post
    you've got a question every day.
    Yes, some of these days.
    When I am busy, I can't post anything for some months.
    Did you ever answer to my question?

    Quote Originally Posted by furicane View Post
    Wouldn't it be better if you hired someone to do your job for you than ask questions
    I am considering it, But I didn't get the timing yet.

    Quote Originally Posted by furicane View Post
    questions which you should already know answers to?
    I like to know this phrase is correct English Pattern or Not.
    If this is correct English, would you please explain it in more easy phrase?

  4. #4
    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)
    Quote Originally Posted by dotJoon View Post
    The trial query below is one of my trials for it, but failed.
    what was the error message?

    come on, man, you should know by now that we can't guess it

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    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)
    Quote Originally Posted by r937 View Post
    what was the error message?
    In PHP
    Code:
    code
    
    $test=mysql_query(" 
    select CONCAT('$myFirst',' ', last_name) as fullName
    left join full ON fullName=full_name
    from last
    ") ;
    
    $i=1;
    while( $row = mysql_fetch_array($test)  )
    {
    echo $row['fullName']."<br>";
    
    
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL.

    In ColdFusion
    Code:
    'SQL syntax Error.' 
    it seems error. ('left join full ON fullName=full_name from last'  command line 2)
    Blue part is translation from my mother tongue to English

  6. #6
    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)
    1. you should not use FULL as a table name, because it's a reserved word

    2. the ON clause cannot use the column alias that you assigned in the SELECT clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    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)
    Quote Originally Posted by r937 View Post
    1. you should not use FULL as a table name, because it's a reserved word
    What a coincidence?
    However, the solution is very easy. ^^.
    I like to use fulName if it not a reserved word either?


    Quote Originally Posted by r937 View Post
    2. the ON clause cannot use the column alias that you assigned in the SELECT clause
    The query below produces an error because fulName is the column alias that I assigned in the select clause.

    Code:
    select CONCAT('$myFirst',' ', last_name) as fulName
    left join full ON fulName=full_name
    from last
    Is there anyway to use the column alias that I assign a query which is NOT in the SELECT clause?
    OR
    Should I reluctantly use the second option which has the inside query below
    Code:
    inside query
    
    select count(*) as count
    from full
    where 
    full_name='#fulName#'
    During the turning of the outside query below?
    Code:
    outside query
    
    select CONCAT('#myFirst#',' ', last_name) as fulName
    from last

  8. #8
    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)
    the answer is yes, and you should test it yourself

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    1. you should not use FULL as a table name, because it's a reserved word
    Quote Originally Posted by dotJoon View Post
    What a coincidence?
    However, the solution is very easy. ^^.
    I like to use fulName if it not a reserved word either?
    The problem wasn't the column name fullname, but the table name full

    And don't test queries like this:
    PHP Code:
    $test=mysql_query(
    select CONCAT('
    $myFirst',' ', last_name) as fullName
    left join full ON fullName=full_name
    from last
    "
    ) ; 
    but like this:
    PHP Code:
    // assign the query to a variable, so you can echo it and check its content
    $query 
      SELECT
        CONCAT('
    $myFirst',' ', last_name) as fullName
      LEFT JOIN full 
      ON fullName = full_name
      FROM last
    "
    ;
    // then run the query and use (for example) 'or die' to display the errors when they occur
    $test=mysql_query($query) or die("mysql error " mysql_error() . " in query $query"); 

  10. #10
    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)
    Is your "Yes" below is the answer to my question of "Is there anyway to use the column alias that I assign a query which is NOT in the SELECT clause"?

    Quote Originally Posted by r937 View Post
    the answer is yes, and you should test it yourself

    Is your "Yes" above is the answer to my question of inside query during the turning of outside query?

    Code:
    inside query
    SELECT CONCAT('#myFirst#',' ', last_name) as fullName
    FROM last
    
    outside query
    
    SELECT count(*) as count
    FROM fulName
    WHERE 
    full_name='#fullName#'
    By the way, I've changed the table name "full" to "fulName".

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,907
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by dotJoon View Post
    [/code]By the way, I've changed the table name "full" to "fulName".
    Why not call it fullName? full on it's own is not allowed, but fullName is, and fulName with just one L is incorrect English
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    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)
    Quote Originally Posted by dotJoon View Post
    Is your "Yes" above is the answer to my question of inside query during the turning of outside query?
    yes it is

    you have now run out of questions for one thread

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    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)
    Quote Originally Posted by ScallioXTX View Post
    Why not call it fullName? full on it's own is not allowed, but fullName is, and fulName with just one L is incorrect English
    Okay, I accept your advice.
    Thank you for your teaching me SQL and English.

    The outside query below now works fine.
    Code:
    select count(*) as count
    from fullName
    where 
    full_name='#fullName#'

  14. #14
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,907
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    you have now run out of questions for one thread
    Want me to close it for ya Rudy?

    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  15. #15
    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)
    Quote Originally Posted by r937 View Post
    you have now run out of questions for one thread
    Why don't you rephrase it in easy sentence?
    (I like to know the meaning of this sentence.)

  16. #16
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,907
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by dotJoon View Post
    Why don't you rephrase it in easy sentence?
    (I like to know the meaning of this sentence.)
    Basically it means:

    you have asked enough questions in this (forum) thread now, so you may not ask any more question in this (forum) thread
    (but should start a new thread for new questions)

    it was a joke
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    How about
    Code:
    SELECT 
      full_name
    FROM fullName
    INNER JOIN last
    ON full_name = CONCAT('$myFirst', ' ', last_name)

  18. #18
    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)
    Quote Originally Posted by guido2004 View Post
    How about
    handing him the answer is not helping him

    he will continue to come back and get us to do his work for him, hunnerts and hunnerts of times, unless we put a stop to it and tell him he has to learn how to do it himself
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •