join problems

[Marlon Moyer]

I was trying to do a join and I'm having problems.  It looks like
ontap is confusing which column is which in the tables.

The join statement should read rails_todo.dbo.categories.id =
rails_todo.dbo.items.category_id


My function call:

<cfset items = request.tapi.sql.select("items","items.*,categories.category",
"",request.tapi.sql.join("categories",false))>

The resulting sql generated:

SELECT items.*,categories.category FROM RAILS_TODO.DBO.ITEMS LEFT JOIN
RAILS_TODO.DBO.CATEGORIES ON ( RAILS_TODO.DBO.CATEGORIES.category_id =
RAILS_TODO.DBO.ITEMS.id )


The table definitions:

CREATE TABLE [items] (
        [id] [smallint] IDENTITY (1, 1) NOT NULL ,
        [done] [tinyint] NOT NULL CONSTRAINT [DF_items_done] DEFAULT (0),
        [priority] [tinyint] NOT NULL CONSTRAINT [DF_items_priority] DEFAULT (0),
        [description] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_items_description] DEFAULT (' '),
        [due_date] [smalldatetime] NULL ,
        [category_id] [smallint] NOT NULL CONSTRAINT [DF_items_category_id]
DEFAULT (0),
        [note_id] [smallint] NULL ,
        [private] [tinyint] NOT NULL CONSTRAINT [DF_items_private] DEFAULT (0),
        [created_on] [smalldatetime] NOT NULL ,
        [updated_on] [smalldatetime] NOT NULL ,
        CONSTRAINT [PK_items] PRIMARY KEY  CLUSTERED
        (
                [id]
        )  ON [PRIMARY] ,
        CONSTRAINT [FK_items_categories] FOREIGN KEY
        (
                [category_id]
        ) REFERENCES [categories] (
                [id]
        ),
        CONSTRAINT [FK_items_notes] FOREIGN KEY
        (
                [note_id]
        ) REFERENCES [notes] (
                [id]
        )
) ON [PRIMARY]
GO

CREATE TABLE [categories] (
        [id] [smallint] IDENTITY (1, 1) NOT NULL ,
        [category] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_categories_category] DEFAULT (' '),
        [created_on] [smalldatetime] NOT NULL ,
        [updated_on] [smalldatetime] NOT NULL ,
        CONSTRAINT [PK_categories] PRIMARY KEY  CLUSTERED
        (
                [id]
        )  ON [PRIMARY]
) ON [PRIMARY]
GO




--
Marlon

"Now watch what you say or they'll be calling you a radical,
liberal, fanatical, criminal. "

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:42:1849
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/42
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:42
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.42
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

[Marlon Moyer]

I was able to get this to run using the tag version and specifiying
"column" and "references"

On 9/15/05, Marlon Moyer <marlon.moyer@...> wrote:

> I was trying to do a join and I'm having problems.  It looks like
> ontap is confusing which column is which in the tables.
>
> The join statement should read rails_todo.dbo.categories.id =
> rails_todo.dbo.items.category_id
>
>
> My function call:
>
> <cfset items = request.tapi.sql.select("items","items.*,categories.category",
> "",request.tapi.sql.join("categories",false))>
>
> The resulting sql generated:
>
> SELECT items.*,categories.category FROM RAILS_TODO.DBO.ITEMS LEFT JOIN
> RAILS_TODO.DBO.CATEGORIES ON ( RAILS_TODO.DBO.CATEGORIES.category_id =
> RAILS_TODO.DBO.ITEMS.id )
>
>
> The table definitions:
>
> CREATE TABLE [items] (
>         [id] [smallint] IDENTITY (1, 1) NOT NULL ,
>         [done] [tinyint] NOT NULL CONSTRAINT [DF_items_done] DEFAULT (0),
>         [priority] [tinyint] NOT NULL CONSTRAINT [DF_items_priority] DEFAULT (0),
>         [description] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL CONSTRAINT [DF_items_description] DEFAULT (' '),
>         [due_date] [smalldatetime] NULL ,
>         [category_id] [smallint] NOT NULL CONSTRAINT [DF_items_category_id]
> DEFAULT (0),
>         [note_id] [smallint] NULL ,
>         [private] [tinyint] NOT NULL CONSTRAINT [DF_items_private] DEFAULT (0),
>         [created_on] [smalldatetime] NOT NULL ,
>         [updated_on] [smalldatetime] NOT NULL ,
>         CONSTRAINT [PK_items] PRIMARY KEY  CLUSTERED
>         (
>                 [id]
>         )  ON [PRIMARY] ,
>         CONSTRAINT [FK_items_categories] FOREIGN KEY
>         (
>                 [category_id]
>         ) REFERENCES [categories] (
>                 [id]
>         ),
>         CONSTRAINT [FK_items_notes] FOREIGN KEY
>         (
>                 [note_id]
>         ) REFERENCES [notes] (
>                 [id]
>         )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [categories] (
>         [id] [smallint] IDENTITY (1, 1) NOT NULL ,
>         [category] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL CONSTRAINT [DF_categories_category] DEFAULT (' '),
>         [created_on] [smalldatetime] NOT NULL ,
>         [updated_on] [smalldatetime] NOT NULL ,
>         CONSTRAINT [PK_categories] PRIMARY KEY  CLUSTERED
>         (
>                 [id]
>         )  ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>
>
> --
> Marlon
>
> "Now watch what you say or they'll be calling you a radical,
> liberal, fanatical, criminal. "
>

--
Marlon

"Now watch what you say or they'll be calling you a radical,
liberal, fanatical, criminal. "

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:42:1850
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/42
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:42
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.42
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

[S. Isaac Dealey]

You've got a couple of options (in order of my own preference):

1) create a foreign key constraint between the categories and items
tables - the framework will then automatically pick up the constraint
and make the appropriate join when those 2 tables are specified

2) make sure the foreign key table (items) uses the same name for the
related column as the primary key table (categories) so in this case
the tables would be joined on "category_id" in both tables (this is
the assumption that results in the join you're seeing)

3) specify the join and reference columns (column and references)
either by using the tag syntax or I was under the impression the
sql.join function also allowed you to specify these. Check the
documentation under Function Libraries > MX > DBA for the
documentation on request.tapi.sql.join().

hth

> I was trying to do a join and I'm having problems.
> It looks like ontap is confusing which column is
> which in the tables.

> The join statement should read
> rails_todo.dbo.categories.id =
> rails_todo.dbo.items.category_id


> My function call:

> <cfset items = request.tapi.sql.select("items","items.*,ca
> tegories.category",
> "",request.tapi.sql.join("categories",false))>

> The resulting sql generated:

> SELECT items.*,categories.category FROM
> RAILS_TODO.DBO.ITEMS LEFT JOIN
> RAILS_TODO.DBO.CATEGORIES ON (
> RAILS_TODO.DBO.CATEGORIES.category_id =
> RAILS_TODO.DBO.ITEMS.id )


> The table definitions:

> CREATE TABLE [items] (
> [id] [smallint] IDENTITY (1, 1) NOT NULL ,
> [done] [tinyint] NOT NULL CONSTRAINT [DF_items_done]
> DEFAULT (0),
> [priority] [tinyint] NOT NULL CONSTRAINT
> [DF_items_priority] DEFAULT (0),
> [description] [varchar] (512) COLLATE
> SQL_Latin1_General_CP1_CI_AS
> NOT NULL CONSTRAINT [DF_items_description] DEFAULT (' '),
> [due_date] [smalldatetime] NULL ,
> [category_id] [smallint] NOT NULL CONSTRAINT
> [DF_items_category_id]
> DEFAULT (0),
> [note_id] [smallint] NULL ,
> [private] [tinyint] NOT NULL CONSTRAINT
> [DF_items_private] DEFAULT (0),
> [created_on] [smalldatetime] NOT NULL ,
> [updated_on] [smalldatetime] NOT NULL ,
> CONSTRAINT [PK_items] PRIMARY KEY  CLUSTERED
> (
> [id]
> )  ON [PRIMARY] ,
> CONSTRAINT [FK_items_categories] FOREIGN KEY
> (
> [category_id]
> ) REFERENCES [categories] (
> [id]
> ),
> CONSTRAINT [FK_items_notes] FOREIGN KEY
> (
> [note_id]
> ) REFERENCES [notes] (
> [id]
> )
> ) ON [PRIMARY]
> GO

> CREATE TABLE [categories] (
> [id] [smallint] IDENTITY (1, 1) NOT NULL ,
> [category] [varchar] (20) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT
> NULL CONSTRAINT [DF_categories_category] DEFAULT (' '),
> [created_on] [smalldatetime] NOT NULL ,
> [updated_on] [smalldatetime] NOT NULL ,
> CONSTRAINT [PK_categories] PRIMARY KEY  CLUSTERED
> (
> [id]
> )  ON [PRIMARY]
> ) ON [PRIMARY]
> GO

s. isaac dealey   954.522.6080
new epoch : isn't it time for a change?

add features without fixtures with
the onTap open source framework

http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:42:1851
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/42
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:42
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.42
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54