One to One joins?

[Sean P. Ford]

Is there a simple way to join 2 tables that have a one to one relationship?

 

--

Sean Ford

SPF Management, LLC

c:  305.788.3673

e:  se...@seanford.com

w: http://seanford.com

w: http://twitter.com/seanford

w: http://facebook.com/seanpford

 

[salvatore fusto]

After loaded a table A in dataMgr, you can add to this other columns
from another table B: you can do this eithrt defininig the join in the
xml describing tables or programmatically using the addeColumns(table,
struct) method: in the struct argumen yoy defining the remote table,
the remote column, the name toi use to the added column, the local key
and the remote key; once added a column to table A, the
getrecords(tablename="A") will include the columns added too.
this is not done with a join, baut with a subselect.
you can not only add columns, but scalar value etc. See doc.
regards
salvatore

On 30 Set, 08:14, "Sean P. Ford" <s...@seanford.com> wrote:
> Is there a simple way to join 2 tables that have a one to one relationship?
>
> --
> Sean Ford
> SPF Management, LLC
> c:  305.788.3673

> e:  s...@seanford.com<mailto:s...@seanford.com>

[salvatore fusto]

Excuse me, the correct method and sintax are
setColumn(tablename="A",columnname="col_to_add_to_tableA",relation=str)
where str is a struct defining the join:
<cfset str ["type"] = "label">
<cfset str["table"] = "tableB"/>
<cfset str["field"] = "column_in_tableB_to_add"/>
<cfset str["join-field-local"] = "fk_to_tableB"/>
<cfset str["join-field-remote"] = "PK_in_tableB"/>

sorry
Salvatore

[Sean P. Ford]

Yes, I know how to do this for a single field. What I was curious to know is if there was a way to do it for several fields at once. Basically, I have two tables with a common key, and I would like to pull all of the fields from both tables in a single query. I know I could do this by creating relations for each field, but what if my tables had 100's of fields. Just exploring my options....for now, I'm using runSQL and a simple LEFT JOIN.

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

[salvatore fusto]

yes, you could add any number of fields from table B to table A,
calling SetColumn() with right parameters for each col you want to
add, ie 5 calls for 5 columns to add.
i've also made multilevel joins:tableA joined to tableB joined TO
tableC: first define columns to add to tabeB from tableC, and then add
colmns to tableA from tableB.
Consider a table of order (TO) , a table of account (TA) for orders
and a table of cities (TC) for accounts: you can first add column city
from TC to TA and the add columns firstname, lastname, city from TC to
TO
The setColumn() and the counterpart removeColumn() can be used
statically, ie on instantiation, or during the flow of your app,
adding/removing columns dinamically.
you can not only add joinned column (setting type="label") but sums,
counts etc from tableB, or even specify an sql expression which you
want to fetch records by.
hope this is more clear.
salvatore

[salvatore fusto]

last, you can add columns to a table not only fron another single
table, but from severals ones too.
Salvatore

[Steve Bryant]

Sean,

Great question. It (along with another email this week) inspired me to
release a custom tag set that I have been intending to release for
some time.
http://www.bryantwebconsulting.com/blog/index.cfm/2010/10/1/Introducing-CFDMQuery

As you can see, that would allow you to write a SQL statement with a
join on multiple tables (or any other SQL basically) and still take
advantage of DataMgr features such as relation fields.

What I don't have (yet) is a getSelectSQL method in DataMgr that works
like the getWhereSQL method. I hope to have that for the next build.
This should really make that easier by allowing code like this:

<CF_DMQuery name="qMyRecords">
SELECT <cf_DMSQL method="getSelectSQL" tablename="tableA">,
<cf_DMSQL method="getSelectSQL" tablename="tableB">
FROM tableA,tableB
WHERE tableA.field = tableB.field
</CF_DMQuery>

Hope that helps!

Steve