Join ON fullName

[b]last[/b]

[COLOR="blue"](id) last_name[/COLOR]
(1)  Tailor
(2)  Williams
(3)  Smith

[b]full[/b]

[COLOR="Blue"](id) full_name[/COLOR]
(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.

[b]query[/b]

select CONCAT('$myFirst',' ', last_name) as fullName
from last

[b]result[/b]

Mary Tailor
Mary Williams
Mary Smith

I like to produce my target result below with the variables “Mary”.

[b]target result[/b]

Mary Smith
Mary Tailor

The trial query below is one of my trials for it, but failed.

[b]trial query[/b]

select CONCAT('$myFirst',' ', last_name) as [COLOR="red"]fullName[/COLOR]
left join full ON [COLOR="Red"]fullName[/COLOR]=full_name
from last

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?

Hello, furicane.
Take it easy, please.

Thanks for reading my Posts

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.)

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.)

Why do you think my table is badly designed?

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.

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.

Yes, some of these days.
When I am busy, I can’t post anything for some months.
Did you ever answer to my question?

I am considering it, But I didn’t get the timing yet.

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?

what was the error message?

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

:slight_smile:

In PHP

[b]code[/b]

$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>";


[B]Warning[/B]: mysql_fetch_array(): supplied argument is not a valid MySQL.

In ColdFusion

[COLOR="Blue"]'SQL syntax Error.' 
it seems error.[/COLOR] ('left join full ON fullName=full_name from last' [COLOR="Blue"] command line[/COLOR] 2)

Blue part is translation from my mother tongue to English

  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

What a coincidence?
However, the solution is very easy. ^^.
I like to use fulName if it not a reserved word either?

The query below produces an error because fulName is the column alias that I assigned in the select clause.

select CONCAT('$myFirst',' ', last_name) as fu[COLOR="Red"]l[/COLOR]Name
left join full ON fu[COLOR="red"]l[/COLOR]Name=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

[b]inside query[/b]

select count(*) as count
from full
where 
full_name='#fulName#' 

During the turning of the outside query below?

[b]outside query[/b]

select CONCAT('#myFirst#',' ', last_name) as fu[COLOR="Red"]l[/COLOR]Name
from last

the answer is yes, and you should test it yourself

:slight_smile:

The problem wasn’t the column name fullname, but the table name full

And don’t test queries like this:

$test=mysql_query(" 
select CONCAT('$myFirst',' ', last_name) as fullName
left join full ON fullName=full_name
from last
") ;

but like this:


// 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");

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”?

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

[b]inside query[/b]
SELECT CONCAT('#myFirst#',' ', last_name) as fullName
FROM last

[b]outside query[/b]

SELECT count(*) as count
FROM [COLOR="Red"]fulName[/COLOR]
WHERE 
full_name='#fullName#' 


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

yes it is

you have now run out of questions for one thread

:smiley:

Okay, I accept your advice.
Thank you for your teaching me SQL and English.

The outside query below now works fine.

select count(*) as count
from [COLOR="Red"]full[/COLOR]Name
where 
full_name='#fullName#' 

Want me to close it for ya Rudy?

:rofl:

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

How about


SELECT 
  full_name
FROM fullName
INNER JOIN last
ON full_name = CONCAT('$myFirst', ' ', last_name) 

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