Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: Joins - the opposite of what I can do!

    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.
    Last edited by fishkake; 03-16-06 at 15:27.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    select cm.machinename,
    csf.path,
    su.path
    from currentmachine cm
    LEFT join softwareusage su on su.machinename=cm.machinename
    inner join currentsoftwarefiles csf on csf.reference=cm.reference
    where
    (su.path = csf.path)
       AND su.machinename IS NULL
    -PatP

  3. #3
    Join Date
    Dec 2005
    Posts
    28
    Thanks Pat, but I'm afraid this doesn't return anything

    The thing is, if I'm joining them on machinename, surely machinename can't be null...? I don't know what left joins are, looking them up now!

    Sorry it took me so long, I've been chasing other avenues, unfortunately no more fruitful...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •