mysql help needed

[Bob Bedford]

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 ?

[Alvaro G Vicario]

*** 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
--

[Bob Bedford]

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

[Alvaro G Vicario]

*** 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
--

[Bob Bedford]

"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 !

[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

[Bob Bedford]

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

[pomasl]

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.

[Bob Bedford]

"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