CODEWITHSUNDEEP
sqloracle-databasewhere-clausehaving
Ariel A
Ariel A

Reputation: 604

Using the OR operator in a nested SQL Statement

I have a database with vendors, what they sell and where they are located.

I need to search for vendors that are based off of a specific locality like a state or for vendors who sell a certain number of products.

An example of this question is:

What are the full names of all vendors who can supply more than one item or are based in Illinois?

This would be easy if I could use two sql queries (but for this problem I cannot).

Assuming there is no joins between tables used, my solution is incorrect but this is what I tried

select 
cs.vendor_id, name, count(cs.PRODUCT_ID) 
from 
grocery.vendor 
where 
va.state_territory_province = 'Illinois' 
group by 
(cs.vendor_id)
or /# error found here #/
having 
(count(cs.product_id)>1);

ERROR at line 1: ORA-00933: SQL command not properly ended

If I try each one separately, I get these results

Using Illonois

select 
cs.vendor_id, v.name, count(cs.PRODUCT_ID) 
from 
grocery.vendor v 
inner join grocery.vendor_address va 
on (v.vendor_id = va.vendor_id) 
inner join grocery.can_supply cs 
on (v.vendor_id = cs.vendor_id) 
where 
va.state_territory_province = 'Illinois' 
group by 
(cs.vendor_id, v.name);

VENDOR_ID NAME COUNT(CS.PRODUCT_ID)

33 Drinks R Us                       1
35 Jungle Man                        1
34 Poland Spring                     1

Using the number of products

select 
cs.vendor_id, v.name, count(cs.PRODUCT_ID) 
from grocery.vendor v 
inner join grocery.vendor_address va 
on (v.vendor_id = va.vendor_id) 
inner join grocery.can_supply cs 
on (v.vendor_id = cs.vendor_id) 
group by (cs.vendor_id, v.name) 
having(
count(cs.product_ID)>1);

VENDOR_ID NAME COUNT(CS.PRODUCT_ID)

 8 Orgo Home Farm                    3
17 Wellness                          2
21 Wily Wonka                        4
27 Camel                             3
29 Supplies R Us                     5
13 Clean Me Please                   5
15 Oral Care Inc                     2
31 Cheese Cake Factory               2
37 Crunchy                           2
 1 Moo Moo Milk Farm                 4
 4 Haagen Daz                        3
26 Beer Inc                          4
 6 Sailor Bob                        3
10 Dawn                              2
16 SPAM                              2
18 Wonder inc                        3
 5 Butcher Mat                       3
 9 Soda Forever                      4
14 Wash Shampoo Inc                  4
24 Huntz                             4
20 Hershey                           3
22 Bake Me Inc                       5
30 We Make Pizza                     2
36 Taste Treat                       3
 7 Monkey Paradise                   6
19 Puff                              5

26 rows selected.

Basically I want to merge these two queries into one. Is there a way to nest these together?

Upvotes: 1

Views: 175

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I would be inclined to do:

select v.*
from gorcery.vendor v join
     grocery.vendor_address va
     on v.vendor_id = va.vendor_id
where va.state_territory_province = 'Illinois' or
      (select count(*)
       from grocery.can_supply cs 
       where v.vendor_id = cs.vendor_id
      ) > 1;

Note: this isn't perfect, because a vendor can have multiple addresses. So, I think the better solution is:

select v.*
from gorcery.vendor v
where exists (select 1
              from  grocery.vendor_address va
              where v.vendor_id = va.vendor_id and
                    va.state_territory_province = 'Illinois'
             ) and
      (select count(*)
       from grocery.can_supply cs 
       where v.vendor_id = cs.vendor_id
      ) > 1;

Upvotes: 3

eifla001
eifla001

Reputation: 1157

how about using UNION clause for the two queries?

Upvotes: 1

sstan
sstan

Reputation: 36513

You should be able to put both conditions in the HAVING clause:

select v.vendor_id, 
       v.name, 
       count(cs.PRODUCT_ID) 
  from grocery.vendor v 
  join grocery.vendor_address va 
    on v.vendor_id = va.vendor_id
  join grocery.can_supply cs 
    on v.vendor_id = cs.vendor_id
 group by v.vendor_id, v.name, va.state_territory_province
having va.state_territory_province = 'Illinois'
    or count(cs.product_ID) > 1

As pointed out in the comments, I couldn't just use va.state_territory_province in the HAVING clause (silly me), so I added it to the GROUP BY clause. I am assuming that every vendor only has one address.

Upvotes: 2

Related Questions