July 19, 2011 at 2:04 pm
Hello everyone.
I have three tables.
TableA has a primary key of PersonId
TableB has a foreign key of PersonId to reference PersonId in Table A
TableB has a primary key of FileID
TableC has a field named FileID that I suppose to reference TableB but there is no foreign key constraint to TableB.
There are mutilple FileId's for each BoxId.
I'm trying to find all BoxId that do not have entries in Table C
Does anyone have any suggestions?
Jonathan
July 19, 2011 at 2:08 pm
You don't need a foreign key to do a query.
How about something like:
select *
from TableB
where FileID not in (select FileID from TableC);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 2:21 pm
I have tried that query and it does return a fileid that is in the TableB but not in TableC.
Say there are like 21 entries of a fileid in TableB then there could be like 17 entries in TableC
If there are 21 entries of a fileid in TableB I want to make sure there are 0 in TableC
July 19, 2011 at 2:24 pm
Per your original post, FileID is the primary key in TableB. You CAN'T have multiple entries in a table for the primary key value.
So what are you actually looking at here? Please define your tables and the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 2:31 pm
There can be multiple fileids yes with different values that are associated with a boxid.
Table B
fieldid Boxid
1 4
2 4
3 4
4 4
5 4
Table C
Filedid
1
3
4
I'm trying to find what fieldid that exist in TableB do not have entries in TableC
July 19, 2011 at 2:44 pm
select distinct boxID
from TableB
where not exists
(select *
from TableB as TB2
inner join TableC
on TB2.FileID=TableC.FileID
where TB2.BoxID = TableB.BoxID);
If you have a table of valid BoxIDs (which you should, per standard normalization), you could use that instead of the outer TableB query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 3:45 pm
This is much appreciated how would I go about visualizing this type of query for next time.
Is this a recursive query. Any suggestions knowing what to write depending on the question?
jonathan
July 21, 2011 at 6:58 am
It's not a recursive query, it's what's called a "correlated subquery". Correlated subqueries reference a value in the outer query.
The subquery tests if a value exists that matches the BoxID of the outer query. Since that's what you're looking for, it's a good way to get it.
You can also do an outer join and check for rows without a match. Both do the same thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply