JOIN

[Göran]

I have made a join between two tables. My problem is that In the two
tables I have two columns with the same name (storage.description,
language.description) and can´t find a way to alias one of the columns
to another name. It is simple to do it in sql.

I have this working:

<cfset theQuery = getTransfer().createQuery("from storage.Storage AS
Storage
JOIN language.Language AS Language
where Storage.storageId = :storageId
AND Language.language = :language")>
<cfset theQuery.setParam("storageId", arguments.storageId, "numeric")
>
<cfset theQuery.setParam("language", arguments.country, "string")>

<cfset storage = getTransfer().listByQuery( theQuery ) />

but there will be errors if I try to put anything before from. I have
also tried:

<cfimport prefix="t" taglib="/transfer/tags">

<t:query name="theQuery" transfer="#getTransfer()#">
select
Storage.storageId,Storage.file,Storage.datcreated,Storage.userid,Storage.protect,Storage.clicks,Storage.views,Storage.amount,Storage.paysystem,Storage.paymethod,Storage.storagePic,Storage.linktext,Storage.description
AS storageDescription
from storage.Storage AS Storage
JOIN language.Language AS Language
where Storage.storageId = <t:queryparam
value="#arguments.storageId#" type="numeric">
AND Language.language = <t:queryparam value="#arguments.country#"
type="string">
</t:query>

First I tried *, Storage.description AS storageDescription and got a
cryptic error message about line 0 :1:2 etc..

To put it simple is it possible to have something before FROM and what
is the syntax?

[Mark Mandel]

The 2nd query should work fine - what was the exact error?

See:
http://docs.transfer-orm.com/wiki/Transfer_Query_Language.cfm#Aliasing_column_names

Mark

--
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer

You received this message because you are subscribed to the Google Groups "transfer-dev" group.
To post to this group, send email to transf...@googlegroups.com
To unsubscribe from this group, send email to transfer-dev...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en

--
E: mark....@gmail.com
T: http://www.twitter.com/neurotic
W: www.compoundtheory.com

[Sergiy Galashyn]

Have you tried to enter the columns to join on explicitly?

Something like this

select ...
from storage.Storage as Storage
join language.Language AS Language
on Storage.SOMEID = Language.SOMEID
and Storage.storageId = ...

Regards,
Sergii

[Göran]

No, I have not tried that. Would this solve the problem? If I get the
storage.description instead of language.description as description in
the query it would be ok.

I did a normal cfquery to solve it temporarley:

select *,storage.description FROM storage,language
WHERE storage.id = #arguments.id#
AND language.isolang = '#arguments.lang#'

But it is a m2m relationship. I have a third link table
lnkstoragelanguage,that should be incorporated if I do a normal sql
query, I remember now. Much easier with Transfer..

@Mark
I can´t repeat the error message right now because I changed my code.
(and tired of restarting the framework).
According to your reference select *, stoarage.description AS
storageDescription FROM should also work. I will do another test.

Thanks for your input!