Complex SQL Query.

Ok,

Table Structure :

FileProduct
===========

ID int -> Unique Key (Long Integer)
FileProduct Text -> Description of product.

FileDetails
===========

ID int -> Unique Key (Long Integer)
ProductID Text -> Relational Link into the FileProduct Table above.
Filename Text -> Name of the file.
Version Text -> Version Details of File.

PCDetails
=========

ID int -> Unique Key (Long Integer)
PCName Text -> Name of PC
FileName Text -> FileName found on PC.
Version Text -> Version Details.

Table Data (what is in each table):

FileProduct
===============
ID FileProduct
-- --
1 P1

File Details
===============
ID ProductID Filename Version
-- -- -
1 1 F1.DAT 1
2 1 F1.DAT 2
3 1 F3.DAT 2
4 1 F4.DAT 2

PCDetails
=========
ID PCName FileName Version
-- -- -
1 PC1 F1.DAT 2
2 PC1 F3.DAT 2
3 PC1 F4.DAT 2
4 PC2 F1.DAT 1
5 PC2 F3.DAT 2
6 PC2 F6.DAT 3

Ok now here is the problem. What I am trying to do is
how to make a SQL statment that will return every PCName
that has has the items in the FileProduct.

Ok here is how I would like it processed

Any file with the same name would be joined by an OR condition.

So the logic would be.

If the PC record has (F1.DAT - Version 1 OR F1.DAT - Version 2) AND (F3.DAT - Version 2)
and (F4.DAT - Version 2) then it would be a succesfull match and return PC1.
So as you can see files with the same name are ORed together and files with different
names are ANDed together.

In the PC Details this would match rows 1,2 and 3. However, PC2 would not be matched
because it does not have a match for F4.DAT.

Now a Product could have mulltiple files in it and there would be multiple products.

I figure this is possible with some magic SQL - but I can't figure it out..
My instincts say this is possible with just a SQL statement.

Any help greatly appreciated !!! :)

Thanks,

Ward.

Ok, a little tricky, but if you infer from the requirement that you want to match PCs that have matching files and versions (or some version of a file) you could do something like this

Code Snippet

select * from pcdetails a

where ([filename] in (select [filename] from filedetails) and version in (select version from filedetails where [filename] = a.filename))

and (PCName not in (select PCName from pcdetails where version not in (select version from filedetails)))

The above, given the table structures and test data you provided returns this result:

Code Snippet

ID PCName FileName Version

1 PC1 F1.DAT 2

2 PC1 F3.DAT 2

3 PC1 F4.DAT 2

Maybe this will get you kick-started. I know it doesn't solve this issue of if a PC doesn't have all the files on it, but you should be able to figure that out with a little time.

Hope this at least helps.

|||

Code Snippet

select PCName
from (
select distinct PCName
from PCDetails
) as PCDetails
where not exists ( -- where there is no...
select * from [File Details] -- ...Filename/version in File Details...
where not exists ( -- ...that does not appear (note ANDs below) ...
select *
from PCDetails as P2 -- ...in PCDetails...
where P2.PCName = PCDetails.PCName -- ...for that particular PCName
and P2.FileName = [File Details].Filename
and P2.Version = [File Details].Version
)
)

This is my guess as to what you want.

Strategy:
Select PCNames for which there is no "missing" file/version.
More specifically:
Select from among the distinct PCNames in PCDetails
those PCNames for which not one of the Filename/versions
present in File Details fails to appear for that
particular PCName in PCDetails.

Steve Kass
Drew University
http://www.stevekass.com