By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

mysql help needed

P: n/a
I've a query with multiple link possibilities:

SELECT model1.Name as ModelName, model2.Name as ModelName, model3.Name as
ModelName, shmodel.shmodelname as ModelName, ...

If I then try to do a "order by ModelName", then the query fails (Unknown
column 'ModelName' in 'order clause'). Any idea ?
Jul 17 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
*** Bob Bedford wrote/escribió (Tue, 8 Feb 2005 15:58:19 +0100):
SELECT model1.Name as ModelName, model2.Name as ModelName [...] If I then try to do a "order by ModelName", then the query fails (Unknown
column 'ModelName' in 'order clause'). Any idea ?


Which ModelName column do you mean? You have two.
--
-- Álvaro G. Vicario - Burgos, Spain
-- Thank you for not e-mailing me your questions
--
Jul 17 '05 #2

P: n/a
"Alvaro G Vicario" <al******************@telecomputeronline.com> a écrit
dans le message de news: fw*****************************@40tude.net...
*** Bob Bedford wrote/escribió (Tue, 8 Feb 2005 15:58:19 +0100):
SELECT model1.Name as ModelName, model2.Name as ModelName

[...]
If I then try to do a "order by ModelName", then the query fails (Unknown
column 'ModelName' in 'order clause'). Any idea ?


Which ModelName column do you mean? You have two.


I need one column only ! in some cases the ModelName comes from table
model1, sometimes from model2. It's there any way to create only one column
?
Jul 17 '05 #3

P: n/a
*** Bob Bedford wrote/escribió (Tue, 8 Feb 2005 16:50:33 +0100):
Which ModelName column do you mean? You have two.


I need one column only ! in some cases the ModelName comes from table
model1, sometimes from model2. It's there any way to create only one column
?


Try a join with the IFNULL() function:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
IFNULL() returns a numeric or string value, depending on the context in
which it is used.

--
-- Álvaro G. Vicario - Burgos, Spain
-- Thank you for not e-mailing me your questions
--
Jul 17 '05 #4

P: n/a

"Alvaro G Vicario" <al******************@telecomputeronline.com> a écrit
dans le message de news: hx****************************@40tude.net...
*** Bob Bedford wrote/escribió (Tue, 8 Feb 2005 16:50:33 +0100):
Which ModelName column do you mean? You have two.


I need one column only ! in some cases the ModelName comes from table
model1, sometimes from model2. It's there any way to create only one
column
?


Try a join with the IFNULL() function:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
IFNULL() returns a numeric or string value, depending on the context in
which it is used.


FANTASTIC !!!! thanks a lot !
Jul 17 '05 #5

P: n/a
NC
Bob Bedford wrote:
SELECT model1.Name as ModelName, model2.Name as ModelName
If I then try to do a "order by ModelName", then the query
fails (Unknown column 'ModelName' in 'order clause'). Any idea ?
Which ModelName column do you mean? You have two.


I need one column only ! in some cases the ModelName comes from
table model1, sometimes from model2.


Somehow, I have a feeling your data model needs revision...
It's there any way to create only one column?


If you need one column only, then you should define one column
only. Something like this:

SELECT CONCATENATE(model1.Name, model2.Name, model3.Name,
shmodel.shmodelname) as ModelName, ...

Cheers,
NC

Jul 17 '05 #6

P: n/a
>> I need one column only ! in some cases the ModelName comes from
table model1, sometimes from model2.


Somehow, I have a feeling your data model needs revision...

Not so easy: let's explain. I've a database coming from a company. Some
datas aren't available in this DB, and I've to create some items myself. I
can't do it on the other company's DB as the record are dropped regularly
and filled with new datas (updates, new items, etc). So I need to have 2
tables, one with the company's datas, and one with mine.
It's there any way to create only one column?


If you need one column only, then you should define one column
only. Something like this:

SELECT CONCATENATE(model1.Name, model2.Name, model3.Name,
shmodel.shmodelname) as ModelName, ...

Concatene seems to fail if any items is NULL.
Jul 17 '05 #7

P: n/a
Bob Bedford wrote:

"Alvaro G Vicario" <al******************@telecomputeronline.com> a écrit
dans le message de news: hx****************************@40tude.net...
*** Bob Bedford wrote/escribió (Tue, 8 Feb 2005 16:50:33 +0100):
Which ModelName column do you mean? You have two.

I need one column only ! in some cases the ModelName comes from table
model1, sometimes from model2. It's there any way to create only one
column
?

Try a join with the IFNULL() function:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
IFNULL() returns a numeric or string value, depending on the context in
which it is used.

FANTASTIC !!!! thanks a lot !

Even better, try the Coalesce(list) function.
Your SELECT then becomes:

SELECT COALESCE(model1.Name, model2.Name, model3.Name, shmodel.shmodelname)
as ModelName, ...

Coalesce(list) takes the first non-NULL in the list.
CJP

--
Christopher J Pomasl Suse Linux 9.0
Senior Software Engineer Starband 360 4/68
Computer Associates SPEBSQSA, Lead/Bari, SOR
IBM Certified Specialist - DB2 UDB V6/V7 User
IBM Certified Solutions Expert - DB2 V7 Family Application Development
IBM Certified Solutions Expert - DB2 UDB Database Administration for OS/390
Always remember, you are unique...just like everyone else.

Jul 17 '05 #8

P: n/a

"pomasl<nospam> @starband.net>" <"pomasl<nospam> a écrit dans le message de
news: 85**************@fe25.usenetserver.com...
Bob Bedford wrote:

"Alvaro G Vicario" <al******************@telecomputeronline.com> a écrit
dans le message de news: hx****************************@40tude.net...
*** Bob Bedford wrote/escribió (Tue, 8 Feb 2005 16:50:33 +0100):

> Which ModelName column do you mean? You have two.

I need one column only ! in some cases the ModelName comes from table
model1, sometimes from model2. It's there any way to create only one
column
?
Try a join with the IFNULL() function:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
IFNULL() returns a numeric or string value, depending on the context in
which it is used.

FANTASTIC !!!! thanks a lot !

Even better, try the Coalesce(list) function.
Your SELECT then becomes:

SELECT COALESCE(model1.Name, model2.Name, model3.Name,
shmodel.shmodelname)
as ModelName, ...

Great ! thanks for info. Probably I'm not the only one to find this useful.

Cheers
Jul 17 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.