Home » SQL & PL/SQL » SQL & PL/SQL » a better way than MINUS
a better way than MINUS [message #445478] Tue, 02 March 2010 01:38 Go to next message
frax
Messages: 5
Registered: November 2009
Junior Member
Hi

I want to get a list of vendors based on country and campaign.
I first get all vendors based on country then i subtract the vendors i shouldn´t see (the vendors i should not see minus those i should see).
Below you see my query - is there a better way to get the same result?

3 tables
vendor - vendorInformation
vendorcountry_spec - A vendor can be located in different countries with different information.
vendorcountry_campaign - In a country there are different campaigns. If there is a row in this table, only those with the campaignID should see the vendor else everyone in that country.

The query:

SELECT v.vendorID 
FROM vendor v, vendorcountry_spec vs  
WHERE v.VENDORID = vs.VENDORID AND v.status = 'A'  
AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1  
AND  v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE'   
GROUP BY v.sortNr, v.vendorID 
 
MINUS

 (SELECT v.vendorID 
FROM vendor v, vendorcountry_spec vs  
WHERE v.VENDORID = vs.VENDORID AND v.status = 'A'  
AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1  
AND  v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE'  
AND v.vendorid in( 
select vc.vendorid from vendorcountry_campaign vc 
where vc.countrycode = 'SE' AND vc.campaignid != 1251) 
GROUP BY v.sortNr, v.vendorID 


MINUS

SELECT v.vendorID 
FROM vendor v, vendorcountry_spec vs  
WHERE v.VENDORID = vs.VENDORID AND v.status = 'A'  
AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1  
AND  v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE'  
AND v.vendorid in( 
select vc.vendorid from vendorcountry_campaign vc 
where vc.countrycode = 'SE' AND vc.campaignid = 1251) 
GROUP BY v.sortNr, v.vendorID )
order by vendorid
Re: a better way than MINUS [message #445485 is a reply to message #445478] Tue, 02 March 2010 02:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe I am overlooking something, but the second and third part look the same to me.
You could remove them and add a "and vendorid not in (<vendorcampaign subquery>)", (or not exists) but you should test if it speeds up things. Especially since you might need to introduce a distinct.

[Updated on: Tue, 02 March 2010 02:27]

Report message to a moderator

Re: a better way than MINUS [message #445486 is a reply to message #445478] Tue, 02 March 2010 02:27 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
frax wrote on Tue, 02 March 2010 08:38
I first get all vendors based on country then i subtract the vendors i shouldn´t see (the vendors i should not see minus those i should see).

What about displaying all vendors you want to see directly in one query (using NOT IN operator):
SELECT v.vendorID 
FROM vendor v, vendorcountry_spec vs  
WHERE v.VENDORID = vs.VENDORID AND v.status = 'A'  
  AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1  
  AND  v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE'   
  AND v.vendorid NOT in( 
    select vc.vendorid from vendorcountry_campaign vc 
    where vc.countrycode = 'SE' AND vc.campaignid != 1251) 
  AND v.vendorid NOT in( 
    select vc.vendorid from vendorcountry_campaign vc 
    where vc.countrycode = 'SE' AND vc.campaignid = 1251) 
GROUP BY v.sortNr, v.vendorID
order by vendorid

Be aware, that this query may incorrectly return nothing when VENDORID will contain NULL value. If it may happen, add the NOT NULL condition inside NOT IN subqueries, or use NOT EXISTS condition instead.

[Edit: NOT EXISTS -> NOT IN]

[Updated on: Tue, 02 March 2010 02:28]

Report message to a moderator

Re: a better way than MINUS [message #445489 is a reply to message #445486] Tue, 02 March 2010 02:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, now I see the diff between the 2nd and 3rd part.

a = x AND a != x
is the same as a is not null
Re: a better way than MINUS [message #445490 is a reply to message #445486] Tue, 02 March 2010 03:01 Go to previous message
frax
Messages: 5
Registered: November 2009
Junior Member
thank you for your input.
I change your answer a little bit otherwise it was very helpful!
SELECT v.vendorID 
FROM vendor v, vendorcountry_spec vs  
WHERE v.VENDORID = vs.VENDORID AND v.status = 'A'  
  AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1  
  AND  v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE'   
  AND v.vendorid NOT in( 
    select vc.vendorid from vendorcountry_campaign vc 
    where vc.countrycode = 'SE' AND vc.campaignid != 1251) 
  OR v.vendorid in( 
    select vc.vendorid from vendorcountry_campaign vc 
    where vc.countrycode = 'SE' AND vc.campaignid = 1251) 
GROUP BY v.sortNr, v.vendorID
order by vendorid



//Fredrik
Previous Topic: I'd like to use the same time on one Database two types of fonts English and Russian (Cyrillic)
Next Topic: UTC Date time should be in format 2010-02-24T17:08:09Z.
Goto Forum:
  


Current Time: Sun Sep 25 04:24:22 CDT 2016

Total time taken to generate the page: 0.07240 seconds