Self Join - best way to go?

This won't work, Q*Bert. I believe he wants all OrgCodes where they have
BOTH 'CALS' AND 'SHTOFF', not where one or the other exists. This is the
time to use the EXISTS statement:

SELECT distinct OrganisationCode oc
FROM organisation_product
WHERE EXISTS (SELECT *
FROM OrganisationCode oc2
WHERE oc2.Product_Sector = 'CALS'
AND oc2.OrganisationCode = oc.OrganisationCode)
AND EXISTS (SELECT *
FROM OrganisationCode oc2
WHERE oc2.Product_Sector = 'SHTOFF'
AND oc2.OrganisationCode = oc.OrganisationCode)

The exists statement will be faster than IN. Also, you should index this
table on Product_Sector, Organisation_Code for faster performance.

Kevin

"Q*Bert" <luke_davis_76@hotmail.com> wrote:
>
>Alex,
>select Distinct OrganisationCode
>from Organisation_Product
>where Product_Sector='CALS' and Product_Sector='SHTOFF'
>
>Will always return 0 because Product_selector can not be both values
>Try using OR instead of and.
>OR
>Try a Where clause of Where Product_Selector in ('CALS','SHTOFF')
>so...
>SELECT OrganisationCode
>FROM Organisation_Product
>WHERE Product_Sector='CALS' OR Product_Sector='SHTOFF'
>GROUP BY OrganisationCode ^^
>
>OR
>SELECT OrganisationCode
>FROM Organisation_Product
>WHERE Product_Sector IN ('CALS', 'SHTOFF')
>GROUP BY OrganisationCode
>
>I don't like using IN, they can be slow.
>
>:BTW: Organisation is spelled Organization. Proper spelling leads to
>easier maintenance . ^
>
>And don't use distinct, use GROUP BY OrganisationCode.
>Distinct is often used to eliminate duplicates. Many times those duplicates
>are there for a reason and you just may not know what that reason is. A
>group By forces you to understand your data better.
>
>Hope this helps
>Q*Bert
>
>"Alex" <alex_j.taylor@virgin.net> wrote:
>>
>>Hello Everyone.
>>
>>Download the table from here http://www.alextaylor.net/db1.zip (~650k)
>>or an example of the table is at the foot of this post.
>>
>>I'm having a few problems with an SQL statement retrieving products from
>>a table against an organisation code. What I want is to provide a list
of
>>organisationcodes when we request products i.e.
>>
>>This returns no results when about 100 should be returned.
>>
>select Distinct OrganisationCode
>from Organisation_Product
>where Product_Sector='CALS' and Product_Sector='SHTOFF'
>>
>>
>>
>>I then tried a nested sql statement. This worked but when I tried to add
>>a distinct to the results set, the execute time started to run into minutes:
>>
>>select Distinct OrganisationCode
>>from Organisation_Product
>>Where OrganisationCode in
>> (select OrganisationCode
>> from Organisation_Product
>> where Product_Sector='CALS')
>>and OrganisationCode in
>> (select OrganisationCode
>> from Organisation_Product
>> where Product_Sector='SHTOFF')
>>
>>
>>
>>
>>
>>Then finally a self join does work well but if I wanted to add more than
>>2 search options, I would have to create a table alias per search option
>>- not good if its a boolean statement of say 50 search options. Is this
>really
>>the case?
>>
>>
>>SELECT DISTINCT organisation_product.OrganisationCode
>>FROM organisation_product, organisation_product AS org_prod
>>WHERE org_prod.organisationcode=organisation_product.OrganisationCode
>>And org_prod.Product_Sector='SHTOFF' And organisation_product.Product_Sector='CALS'
>>
>>
>>
>>I just cant think of a better solution? any ideas?
>>
>>Thanks for any help!!
>>
>>Alex
>>
>>________________________________________________________
>>
>>OrganisationCode Product_Sector
>>10009 GRT
>>10009 DMAIL
>>10009 GRT
>>10009 LBLSHT
>>10009 LEAF
>>10009 NWSSHT
>>10009 PCKDSC
>>10009 PLBIND
>>10009 PSTCRD
>>10009 PSTSML
>>10009 RPTACC
>>10009 SAM
>>10009 SGNBAN
>>10009 SGNNAM
>>10009 STCOM
>>10011 LEAF
>>10011 LBLSHT
>>10011 PSTCRD
>>10011 STCOM
>>10011 STWED
>>10021 RPTACC
>>
>>
>>
>>
>>
>

If you are using Oracle use the INTERSECT set operator

select OrganisationCode
from Organisation_Product
where Product_Sector='CALS'
INTERSECT
select OrganisationCode
from Organisation_Product
where Product_Sector='SHTOFF'

If you are using SQL Server you will have to use an EXISTS subquery for every
extra condition


"Alex" <alex_j.taylor@virgin.net> wrote:
>
>Hello Everyone.
>
>Download the table from here http://www.alextaylor.net/db1.zip (~650k)
>or an example of the table is at the foot of this post.
>
>I'm having a few problems with an SQL statement retrieving products from
>a table against an organisation code. What I want is to provide a list of
>organisationcodes when we request products i.e.
>
>This returns no results when about 100 should be returned.
>
>select Distinct OrganisationCode
>from Organisation_Product
>where Product_Sector='CALS' and Product_Sector='SHTOFF'
>
>
>
>I then tried a nested sql statement. This worked but when I tried to add
>a distinct to the results set, the execute time started to run into minutes:
>
>select Distinct OrganisationCode
>from Organisation_Product
>Where OrganisationCode in
> (select OrganisationCode
> from Organisation_Product
> where Product_Sector='CALS')
>and OrganisationCode in
> (select OrganisationCode
> from Organisation_Product
> where Product_Sector='SHTOFF')
>
>
>
>
>
>Then finally a self join does work well but if I wanted to add more than
>2 search options, I would have to create a table alias per search option
>- not good if its a boolean statement of say 50 search options. Is this
really
>the case?
>
>
>SELECT DISTINCT organisation_product.OrganisationCode
>FROM organisation_product, organisation_product AS org_prod
>WHERE org_prod.organisationcode=organisation_product.OrganisationCode
>And org_prod.Product_Sector='SHTOFF' And organisation_product.Product_Sector='CALS'
>
>
>
>I just cant think of a better solution? any ideas?
>
>Thanks for any help!!
>
>Alex
>
>________________________________________________________
>
>OrganisationCode Product_Sector
>10009 GRT
>10009 DMAIL
>10009 GRT
>10009 LBLSHT
>10009 LEAF
>10009 NWSSHT
>10009 PCKDSC
>10009 PLBIND
>10009 PSTCRD
>10009 PSTSML
>10009 RPTACC
>10009 SAM
>10009 SGNBAN
>10009 SGNNAM
>10009 STCOM
>10011 LEAF
>10011 LBLSHT
>10011 PSTCRD
>10011 STCOM
>10011 STWED
>10021 RPTACC
>
>
>
>
>