Hi there,
I'm running MySQL 4.1, just FYI.
I'm a bit of a n00b, but unfortunately I'm a n00b who's client thinks he's an expert (this was my bosses doing, don't judge me on it!) and I'm trying to produce some reports from an asset database that they have.
There are three tables I'm interested in at this point. Currentmachine which stores reference (key), and machine name (and lots more that I'm not concerned with). Then there is CurrentSoftwareFiles, which contains reference (key) and path (of each exe file on the machine!). Finally, Softwareusage, which contains Machinename (key, I think) and path (the path of all the exes which have been used).
So I have to join them like this:
Code:
select blah from currentmachine cm
inner join currentsoftwarefiles csf on cm.reference=csf.reference
and
Code:
select blah from currentmachine cm
inner join softwareusage su on su.machinename=cm.machinename
As you can see, this is rather annoying. Now, here's the real crux of the problem. I need a report which lists csf.path for each executable, where the instance of that file on that particular machine does NOT have a corresponding entry in su. In other words, I want a list of every executable that hasn't been used.
Here's what I've produced so far:
Code:
select cm.machinename,
csf.path,
su.path
from currentmachine cm
inner join softwareusage su on su.machinename=cm.machinename
inner join currentsoftwarefiles csf on csf.reference=cm.reference
where
(su.path = csf.path)
This produces the OPPOSITE of what I want - this is a list of all the executables in csf which have a corresponding value in SoftwareUsage. I feel so close, but at this level, its not just about changing an = for a <> is it?
Anybody who can help me with this in the next 4 hours will be ordained as a god in my eyes, but after midnight GMT don't bother. I'll already be dead by then.
dBforums
>
Joins - the opposite of what I can do!
03-16-06, 14:12
Linear Mode
