Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39

    Unanswered: Theory question: What is a Join Predicate?

    Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate.
    I googled that, and I only found this useless tip:

    Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
    So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this query has a join predicate:

    select table1.foo,table2.bar from table1,table2 where table1.pk=table2.fk

    this one doesn't:

    select table1.foo,table2.bar from table1,table2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    Ok, thanks!...
    I never made a join without a join predicate (I normally make joins using the "inner join" (or left, right) and "on" words).

    What could be the reason of the error?
    Last edited by jigarzon; 10-25-04 at 09:51.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what could be the cause of the error?

    well, my crystal ball is down at the moment, so i can't see your query, and i'm going to have to just make a wild guess, but i'd say it was probably because you were missing a join predicate

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    you are right... here is the query.

    Every time I execute it, i can see how the Trace on the server I started throws a "Missing Join Predicate" error. I cannot guess whats wrong...

    Thanks for your time!

    Code:
    SELECT CONDITION.COD_CONDITION [Cod. Condicion], 
    	CONDITION.DENOM [Denom],  
    	CO_OF.VAL_CONDITION [Valor] 
    FROM MOV_OFER_CONDITION CO_OF 
    	INNER JOIN  MAE_CONDITIONS CONDITION 
    		ON CO_OF.COD_CONDITION=CONDITION.COD_CONDITION  
    	INNER JOIN MOV_OFERTAS_ITEM IT_OF 
    		ON CO_OF.DELEG=IT_OF.DELEG AND 
    		IT_OF.PED_COT = CO_OF.PED_COT 
    		AND IT_OF.CUIT_PROV = CO_OF.CUIT_PROV  
    WHERE IT_OF.PED_COT=2704 AND CO_OF.DELEG=1 AND CO_OF.STATE='A' 
    	AND CONDITION.STATE='A' AND IT_OF.STATE NOT IN ('B','H') 
    	AND IT_OF.CUIT_PROV='307073718420000000' 
    ORDER BY CONDITION.COD_CONDITION

  6. #6
    Join Date
    Oct 2004
    Location
    London
    Posts
    21
    Hi

    Do you have any triggers on this table? If so you should also look at those.

    Otherwise my only other guess is that the statement is timing out in some way maybe.

    If inserts, deletes and updates are not being carried out on these tables at the same time as your select statement, then you could try changing it do a dirty read by adding "with(nolock)" to the end of each table. This will stop a shared lock being created during the read but the data returned by the select could be out if it is being updated at the exact same time (even by a different user).

    Example:
    SELECT CONDITION.COD_CONDITION [Cod. Condicion],
    CONDITION.DENOM [Denom],
    CO_OF.VAL_CONDITION [Valor]
    FROM MOV_OFER_CONDITION CO_OF with(nolock)
    INNER JOIN MAE_CONDITIONS CONDITION with(nolock)
    ON CO_OF.COD_CONDITION=CONDITION.COD_CONDITION
    INNER JOIN MOV_OFERTAS_ITEM IT_OF with(nolock)
    ON CO_OF.DELEG=IT_OF.DELEG AND
    IT_OF.PED_COT = CO_OF.PED_COT
    AND IT_OF.CUIT_PROV = CO_OF.CUIT_PROV
    WHERE IT_OF.PED_COT=2704 AND CO_OF.DELEG=1 AND CO_OF.STATE='A'
    AND CONDITION.STATE='A' AND IT_OF.STATE NOT IN ('B','H')
    AND IT_OF.CUIT_PROV='307073718420000000'
    ORDER BY CONDITION.COD_CONDITION

    Hope this helps in some way.

    Eamon

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That whistling sound you hear is the steam coming out of Pat's ears...

    Here's a hint....avoid the hint

    Post the table DDL...I've never heard of what you're speaking about....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, one thing's clear, it's certainly not missing any join predicates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    I have no triggers on these tables... and they also do not have foreign keys, only primary ones (dont ask me why, I didn't make that design... however, joins should work right without any keys, dont they?).

    In response to EamonM, I'm sure no updates are being executed: I run the trace, I paste the query in query analizer, I press F5 15 times (for example), and i can see 15 "Missing Join Predicate" messages in the Trace log. I made that yesterday and today... I should have very, very bad luck to have exactly in those moments any updates... And the query isn't timing too much... (query analizer says: 00:00:00)... the result is 6 records.

    Here is the creation script of the 3 tables that uses the query:

    Code:
    CREATE TABLE [dbo].[MOV_OFER_CONDITION] (
    	[DELEG] [int] NOT NULL ,
    	[EJERC] [int] NOT NULL ,
    	[PED_COT] [int] NOT NULL ,
    	[RENGLON] [int] NOT NULL ,
    	[COD_CONDITION] [int] NOT NULL ,
    	[VAL_CONDITION] [nvarchar] (255),
    	[CUIT_PROV] [nvarchar] (30),
    	[STATE] [char] (1),
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[MAE_CONDITIONS] (
    	[COD_CONDITION] [int] NOT NULL ,
    	[RENGLON] [int] NOT NULL ,
    	[DENOM] [nvarchar] (255),
    	[OBLIG_COMPRA_DIRECTA] [char] (2),
    	[OBLIG_LICITACION] [char] (2),
    	[DATE_ALTA] [datetime] NULL ,
    	[STATE] [char] (1),
    	[DATE_VIG_DDE] [datetime] NULL ,
    	[DATE_VIG_HTA] [datetime] NULL ,
    	[STATE_HIS] [char] (1),
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[MOV_OFERTAS_ITEM] (
    	[DELEG] [int] NOT NULL ,
    	[EJERC] [int] NOT NULL ,
    	[PED_COT] [int] NOT NULL ,
    	[RENGLON] [int] NOT NULL ,
    	[CONJUNTO] [int] NULL ,
    	[RENGLON_PC] [int] NULL ,
    	[NRO_ITEM] [int] NULL ,
    	[CLASE] [char] (1),
    	[ITEM] [char] (8),
    	[GRUPO] [int] NULL ,
    	[CUIT_PROV] [nvarchar] (30),
    	[STATE] [char] (1),
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[MOV_OFERTAS_CONDITION] WITH NOCHECK ADD 
    	CONSTRAINT [PK_MOV_OFERTAS_CONDITIONES] PRIMARY KEY  CLUSTERED 
    	(
    		[DELEG],
    		[EJERC],
    		[PED_COT],
    		[RENGLON]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[MAE_CONDITIONS] WITH NOCHECK ADD 
    	CONSTRAINT [PK_MAE_CONDITIONS] PRIMARY KEY  CLUSTERED 
    	(
    		[COD_CONDITION],
    		[RENGLON]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[MOV_OFERTAS_ITEM] WITH NOCHECK ADD 
    	CONSTRAINT [PK_MOV_OFERTAS_ITEM] PRIMARY KEY  CLUSTERED 
    	(
    		[DELEG],
    		[EJERC],
    		[PED_COT],
    		[RENGLON]
    	)  ON [PRIMARY] 
    GO

  10. #10
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    I have no triggers on these tables... and they also do not have foreign keys, only primary ones (dont ask me why, I didn't make that design... however, joins should work right without any keys, dont they?).

    In response to EamonM, I'm sure no updates are being executed: I run the trace, I paste the query in query analizer, I press F5 15 times (for example), and i can see 15 "Missing Join Predicate" messages in the Trace log. I made that yesterday and today... I should have very, very bad luck to have exactly in those moments any updates... And the query isn't timing too much... (query analizer says: 00:00:00)... the result has 6 records.

    Here is the creation script of the 3 tables that uses the query:

    Code:
    CREATE TABLE [dbo].[MOV_OFER_CONDITION] (
    	[DELEG] [int] NOT NULL ,
    	[EJERC] [int] NOT NULL ,
    	[PED_COT] [int] NOT NULL ,
    	[RENGLON] [int] NOT NULL ,
    	[COD_CONDITION] [int] NOT NULL ,
    	[VAL_CONDITION] [nvarchar] (255),
    	[CUIT_PROV] [nvarchar] (30),
    	[STATE] [char] (1),
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[MAE_CONDITIONS] (
    	[COD_CONDITION] [int] NOT NULL ,
    	[RENGLON] [int] NOT NULL ,
    	[DENOM] [nvarchar] (255),
    	[OBLIG_COMPRA_DIRECTA] [char] (2),
    	[OBLIG_LICITACION] [char] (2),
    	[DATE_ALTA] [datetime] NULL ,
    	[STATE] [char] (1),
    	[DATE_VIG_DDE] [datetime] NULL ,
    	[DATE_VIG_HTA] [datetime] NULL ,
    	[STATE_HIS] [char] (1),
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[MOV_OFERTAS_ITEM] (
    	[DELEG] [int] NOT NULL ,
    	[EJERC] [int] NOT NULL ,
    	[PED_COT] [int] NOT NULL ,
    	[RENGLON] [int] NOT NULL ,
    	[CONJUNTO] [int] NULL ,
    	[RENGLON_PC] [int] NULL ,
    	[NRO_ITEM] [int] NULL ,
    	[CLASE] [char] (1),
    	[ITEM] [char] (8),
    	[GRUPO] [int] NULL ,
    	[CUIT_PROV] [nvarchar] (30),
    	[STATE] [char] (1),
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[MOV_OFERTAS_CONDITION] WITH NOCHECK ADD 
    	CONSTRAINT [PK_MOV_OFERTAS_CONDITIONES] PRIMARY KEY  CLUSTERED 
    	(
    		[DELEG],
    		[EJERC],
    		[PED_COT],
    		[RENGLON]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[MAE_CONDITIONS] WITH NOCHECK ADD 
    	CONSTRAINT [PK_MAE_CONDITIONS] PRIMARY KEY  CLUSTERED 
    	(
    		[COD_CONDITION],
    		[RENGLON]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[MOV_OFERTAS_ITEM] WITH NOCHECK ADD 
    	CONSTRAINT [PK_MOV_OFERTAS_ITEM] PRIMARY KEY  CLUSTERED 
    	(
    		[DELEG],
    		[EJERC],
    		[PED_COT],
    		[RENGLON]
    	)  ON [PRIMARY] 
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •