outer join leading to inner member error

[langloic]

I am not too much of a SQL person so it might be really easy for you guys..

I have 3 tables (site s,contracts c and detail d).
Not every site has a contract but when a contract exist for a site there also and always exists d.status.
I need to list every sites regardless of them having a contract or not and I need to list the contract id and its status.
I have put an outer join to fetch all sites and the contract id when it exist but when I introduce the detail table then I get error:
The table ‘contract' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
My statement looks like this:
select s.site_id,c.id,d.status
from site s, contract c ,detail d
where c.x_contract2site=*s.objid
and c.status2gbst_elm=g.objid

Any hint would be much appreciated.
Thanks

 

[r937]

select s.site_id
     , c.id
     , d.status
  from site s
left outer
  join contract c
    on s.objid = c.x_contract2site
left outer
  join cdetail d
    on c.status2gbst_elm = d.objid

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

[Blue]SELECT[/Blue] s.site_id[Gray],[/Gray] c.id[Gray],[/Gray] d.status
  [Blue]FROM[/Blue] site s [Blue]INNER[/Blue] [Gray]JOIN[/Gray] cdetail d
     [Blue]ON[/Blue] s.objid[Gray]=[/Gray]d.objid
  [Fuchsia]Left[/Fuchsia] [Gray]OUTER[/Gray] [Gray]JOIN[/Gray] contract c
    [Blue]ON[/Blue] s.objid [Gray]=[/Gray] c.x_contract2site

Rudy, I think your code will not pickup d.status for those rows which have no row in contract.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

karl, that's correct

however, i did not switch the join condition like you did!!

the original query had what appears to be a typo --

select s.site_id,c.id,d.status
from site s, contract c ,detail d
where c.x_contract2site=*s.objid
and c.status2gbst_elm=g.objid

here you can clearly see that there is no table with the "g" alias

i assumed this was a typo, and the last line meant to have the following join condition --

... c.status2gbst_elm=d.objid

i think what you did is not correct

you changed the condition to this --

... s.objid=d.objid

so i think your code is more wrong than mine!!!





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

I stand corrected. I misread langloic's sentence regarding the existence of a detail row. In fact with you point in mind, the only error in your code (assuming our guess is correct) is the cdetail typo...which I perpetuated!
I think all of us have set a typo record per thread.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[langloic]

Sorry for the typo!!..
thanks for your help.

 

[r937]

hey, typos are a part of life

but we are curious, did either of our suggestions actually solve your problem?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[langloic]

Right... your answer r937, worked for what was described in my initial question (without the typo!) but... (there is always a but isn't there??):
A site can have more than one contract and i only want to get the most recent one.
So i need all sites (whether they have a contract or not ),
when there exists a contract i need the most recent one (id) and its status of the detail table.
when i tried (most of today ...) i only return the sites which have a contract not all of them..
here is my simplified statement if it means anything to anybody..(I don't think I was meant to write SQL ???):

select 'Site Name'=substring(s.name, 1, 50)
,
'ContractID'=substring(c.id, 1, 10)
, 'Contract status'= d.title

from table_site s
left outer join table_employee e1 on site_altsupp2employee=e1.objid
left outer join table_employee e2 on site_support2employee=e2.objid
left outer join table_contract c on c.x_contract2site=s.objid
left outer join table_detail d on c.status2gbst_elm=d.objid
, table_address a
where s.cust_primaddr2address = a.objid
and s.support_office2site = 29360186
and s.x_universal_lic>0
and s.status = 0
and s.type = 1
and c.create_dt in (select max(c.create_dt)
from table_contract c, table_site s
where c.x_contract2site=s.objid
and s.support_office2site = 29360186
and s.x_universal_lic>0
and s.status = 0
and s.type = 1
group by s.site_id
)
order by s.name

 

[r937]

why are you joining to employees twice? you don't actually retrieve anything from them

you have added the table_address table using comma syntax, don't mix comma syntax with JOIN syntax

your subquery uses GROUP BY and returns as many max dates as there are sites, so your outer query will return all contracts -- not necessarily just the latest in each site -- as long as the date is one of the max ones

if you are looking for only sites with contracts, you don't want LEFT OUTER JOIN anyway

and what's the deal with substring(c.id,1,10)? the contract id is a string longer than 10 characters? (that's not necessarily wrong, just unusual)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[langloic]

The thing is that i have simplied a huge (to my eyes.) query for the forum. i do fetch the name once for the DSM and once for the acct manager.

Why is it not possible to use comma syntax: I don't get any errors but I 'd be more than happy to do it the right way.

What i need the query to do is to retrieve all sites and the most recent contract when this exists (null if no contract exists and its detail status. (i can get rid of the address if necessary but so far this hasn't caused me a problem.)
so the result can be like:
site_id contract_id detail_status date
1 Null Null Null
2 1 open 14/02/05
3 4 active 23/02/05
4 Null Null Null

and data in database would be
contract_id create_dt for site_id
1 14/02/05 2
2 01/01/05 3
3 05/02/05 3
4 23/02/05 3

Thanks for all your tips.

 

[r937]

looks like your examples of contract id are integers, so i am even more perplexed why you would be taking a substring of them

select s.site_id
     , max_c.id
     , max_c.status
  from site s
left outer
  join (
    select c.id
         , c.x_contract2site
         , c.contract_dt
         , d.status
      from contract c
    inner
      join detail d
        on c.status2gbst_elm 
         = d.objid
     where c.contract_dt
         = ( 
           select max(contract_dt)
             from contract
            where x_contract2site
                = c.x_contract2site
           )
       ) as max_c
    on s.objid 
     = max_c.x_contract2site

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[langloic]

Thanks you ! this is exactly what I was after.
(fyi contract ids are of string format , I just put integers in my example).