February 4, 2005 at 8:54 am
Alright, I'm having a really stupid moment! I have three tables:
Buildings, Suites, RealmStageSuites
Buildings has a GUID PK of BuildingID with a natural (search) key of BuildingNumber.
Suites has a GUID PK of SuiteID with a natural (search) key of SuiteName.
RealmStageSuites has a compound natural key of BuildingID and SuiteName - which coorespond with the natural keys in the above mentioned tables.
So, I need to join buildings table and suites table to match them up with RealmStageSuites.
What I need to do is delete all records from suites that does not have a cooresponding record in RealmStageSuites. I have two queries that for whatever reason look the same to me - but are selecting different sets up records.
This query selects (and would be changed to delete) 311 records
-----------------------------------------------------------
select s.suiteid, s.buildingid, s.SuiteName, b.buildingnumber, g.suitename, g.buildingID
from suites s
inner join buildings b on s.buildingid=b.buildingid
LEFT JOIN RealmStageSuites g on g.buildingid=b.buildingnumber AND g.suitename=s.suitename
WHERE g.suitename IS NULL AND g.buildingID is NULL
-----------------------------------------------------------
This query selects (and would be changed to delete) 187 records
________________________________________________________________
select s.suiteid, s.buildingid, s.SuiteName, b.buildingnumber
from suites s
inner join buildings b on s.buildingid=b.buildingid
where (b.buildingNumber
NOT IN (SELECT buildingID from RealmStageSuites)
AND s.SuiteName
NOT IN (SELECT SuiteName from RealmStageSuites))
order by buildingnumber
_________________________________________________________________
Which is actually selecting what I want?
Thanks
February 4, 2005 at 9:07 am
Don't forget the LEFT JOIN rule - if you place a WHERE condition on any column in the left joined table, you essentially convert the left join to an INNER join.
February 4, 2005 at 9:08 am
After some additional testing, it looks like query 1 is correct:
select s.suiteid, s.buildingid, s.SuiteName, b.buildingnumber, g.suitename, g.buildingID
from suites s
inner join buildings b on s.buildingid=b.buildingid
LEFT JOIN RealmStageSuites g on g.buildingid=b.buildingnumber AND g.suitename=s.suitename
WHERE g.suitename IS NULL AND g.buildingID is NULL
Anyone know what's wrong with Q2?
February 4, 2005 at 9:11 am
The relationship between Buildings/Suites is not clear. Can 2 different buildings have a suite with the same name ? Or is SuiteName always unique ?
February 4, 2005 at 9:13 am
Sorry about that. Yes, many different suites can have the same name in different buildings. For instance, suite 300 can occur many times (in different buildings)
February 4, 2005 at 9:19 am
In that case I'd construct it as an EXISTS, because you have a compound, 2 column natural key to check.
select s.suiteid As DeletableKey
from suites s
inner join buildings b
on s.buildingid = b.buildingid
where not exists (
select *
from RealmStageSuites g
where g.suitename = s.suitename
and g.buildingid = b.buildingnumber -- Is this right ? ID = Number ?
)
February 4, 2005 at 9:28 am
PW, thanks for your help. Your query selects the same 311 records as my query 1. I like the way you used exists - I will take a closer look at that keyword.
As an aside, I think Query 2 is having troubles because of:
where (b.buildingNumber
NOT IN (SELECT buildingID from RealmStageSuites)
AND s.SuiteName
NOT IN (SELECT SuiteName from RealmStageSuites))
I was essentially trying to select records where the combo of buildingnumber + SuiteName as not showing up. Instead, it was ignoring missing suite records simply by the existence of the
where (b.buildingNumber
NOT IN (SELECT buildingID from RealmStageSuites)
As soon as any record was found with the correct building number - all records with the building didn't make the result set.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply