CODEWITHSUNDEEP
sql
Marc
Marc

Reputation: 29

combining queries in SQL

Attempting to return list of titles (t.processed) that exist in both MOF and SIL locations, and then subtract titles from that list that exist in SIL

Revised:

select t.processed, i.call
from item i, title t
where i.bib# = t.bib#
    and i.collection = 'PIC'
    and i.location in ('MOF', 'SIL')
except
select t.processed, i.call
from item i, title t
where i.bib# = t.bib#
    and i.collection = 'PIC'
    and i.location = 'SIL'

Unfortunately this returns no results, but it is a little closer to what I need

Sample output is fairly simple (and flexible):

processed        call
Mouse count      P WAL
Fly away home    P BUN

To further clarify: I would like to find the titles (ex. Fly away home) that are repeated in both the SIL and MOF locations, and then remove from that list the titles that occur in the SIL location

Upvotes: 0

Views: 106

Answers (4)

Marc
Marc

Reputation: 29

FINAL EDIT:

This would be the query I was looking for (my apologies for not explaining myself adequately)

select t.processed
from title t, item i
where t.bib# = i.bib#
and i.location = 'MOF'
and i.collection = 'PIC'
and i.bib# not in (select bib# from item where location = 'SIL' and collection = 'PIC')

I am still having trouble wrapping my head around subqueries. My trouble was in not using the primary key i.bib# as the basis for the subquery.

Thanks all for your suggestions!

Upvotes: 0

Ram
Ram

Reputation: 3091

Try

select i.item#, t.processed, i.call, i.collection
from item i, title t
where i.bib# = t.bib#
    and (
         (i.collection = 'PIC' and i.location <> 'SIL') 
         or ( i.collection = 'PIC' and  i.location = 'MOF')
        )

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Assuming Item is something like

ID Item Collection Location
1  1    PIC        MOF
2  1    PIC        SIL
3  2    PIC        MOF
4  3    PIC        SIL

then

Select select mof.item#, mof.call, mof.collection From Item mof
Left Join Item sil On mof.Item# = sil.Item# and sil.Collection = mof.Collection
Where sil.Location = 'SIL'
and mof.Location = 'MOF'
and mof.Collection = 'PIC'
and sil.ID is null

will get you close. Stop with the old join syntax...

Upvotes: 1

Joe
Joe

Reputation: 63424

The EXCEPT operator does this, for some flavors of SQL at least:

select i.item#, t.processed, i.call, i.collection
from item i, title t
where i.bib# = t.bib#
    and i.collection = 'PIC'
    and i.location = 'MOF'
except
select i.item#, t.processed, i.call, i.collection
from item i, title t
where i.bib# = t.bib#
    and i.collection = 'PIC'
    and i.location = 'SIL'

Upvotes: 0

Related Questions