December 2, 2002 at 1:36 am
Hi
I am getting the missing join predicate warning for this query, to my understanding, its a complete load of you know what... thoughts, perhaps its the self join? running sp2, sqlserver 2k ee.
SELECT appf1.function_id as Subsystem_Function_ID,
appf1.function_name as Subsystem_Function_Name,
appf2.function_id as Menu_Group_Function_ID,
appf2.graphic_name as Menu_Group_Graphic_Name,
appf2.function_name as Menu_Group_Function_Name
FROM
Application_Function appf1, Application_Function appf2
WHERE
appf1.application_code = 'xxxxx'
AND appf1.function_id = 600
andappf1.function_id = appf2.parent_function_id
andappf1.application_code = appf2.application_code
ORDER BY
appf1.function_id, appf2.function_id--, appf3.function_id
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 2, 2002 at 5:39 am
I get no errors when I create a table like so
create table Application_Function (
function_id int not null,
function_name varchar(200) not null,
graphic_name varchar(200) not null,
application_code varchar(50) not null,
parent_function_id int not null
)
Can you post the DDL of your table so I can look. Also, what is the exact error message as this will help us to find a possible answer?
December 2, 2002 at 10:58 pm
Hi there Antares686, here is the DML
CREATE TABLE [Application_Function] (
[application_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[function_id] [int] NOT NULL ,
[parent_function_id] [int] NOT NULL ,
[function_code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[function_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[url_name] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[graphic_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sort_order] [int] NOT NULL CONSTRAINT [DF_Application_Function_sort_order] DEFAULT (0),
[disabled_ind] [int] NOT NULL CONSTRAINT [DF_Application_Function_disabled_ind] DEFAULT (0),
[update_count] [int] NOT NULL CONSTRAINT [DF_Application_Function_update_count] DEFAULT (0),
[last_updated_on] [datetime] NOT NULL CONSTRAINT [DF_Application_Function_last_updated_on] DEFAULT (convert(datetime,getdate())),
[last_updated_by] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [Function_PK] PRIMARY KEY CLUSTERED
(
[application_code],
[function_id],
[parent_function_id]
) WITH FILLFACTOR = 90 ON [CORPSYS_INDEX]
) ON [CORPSYS_INDEX]
GO
I have remove the two fk links to "application" and "function", if you want that, well, the script will cascade into 6 other tables.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 3, 2002 at 5:25 am
Still no issue. What I suggest do to test on your end is as follows.
Set up a test database.
Using the DDL you provided create this table in the test DB on just on Primary filegroup.
Using Import wizard copy a few thousand rows over.
Test your query.
If works then try creating the additional cascade and FKs into the test DB one at the time and see what setting throws you.
If that does not fail let us know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply