On May 6, 1:08*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 06 May 2008 18:50:20 +0200, Warren <w.gray.mat...@gmail.com> wrote:
> > On May 6, 11:59*am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> >> On 6 May, 16:28, Warren <w.gray.mat...@gmail.com> wrote:
>
> >> > And some sample data. If the system is 2, I want to return only rows
> >> > 1, 2, 4, 7, 8 and 9.
>
> >> > row * * id * * *name * * * * * * * * * *system *software
> >> > 1 * * * 2 * * * Test Package * * * * * *1 ** * * * * * 2
> >> > 2 * * * 4 * * * WLM (Worker 1) *NULL * *NULL
> >> > 3 * * * 5 * * * WLM (Worker 2) *2 * * * * * * * 5
> >> > 4 * * * 6 * * * Inactive Software * * * NULL * *NULL
> >> > 5 * * * 9 * * * 1234 * * * * * * * * * * * * * *2 * * * * * * * 9
> >> > 6 * * * 9 * * * 1234 * * * * * * * * * * * * * *1 * * * * * * * 9
> >> > 7 * * * 10 * * *test * * * * * * * * * * * * * *1 * * * * * * * 10
> >> > 8 * * * 11 * * *1 * * * * * * * * * ** * * * * NULL * *NULL
> >> > 9 * * * 12 * * *Worker Package 1 * * * *NULL * *NULL
>
> >> > On May 6, 11:23 am, Warren <w.gray.mat...@gmail.com> wrote:
>
> >> > > The query is actually:
>
> >> > > SELECT sw.*, i.*
> >> > > FROM `SOFTWARE` sw
> >> > > LEFT OUTER JOIN `INSTALLS` i ON i.`software` = sw.`id`
> >> > > WHERE i.`system` IS NULL OR (NOT i.`system` = {id}) ;
>
> >> > > Warren wrote:
> >> > > > I've been trying to think of how to complete this with a single *
> >> query,
> >> > > > but my knowledge of exactly which function would be useful here is
> >> > > > limited.
>
> >> > > > The situation:
> >> > > > I have an indexed table that refers to two other tables, call it
> >> > > > 'INSTALLS'. INSTALLS contains two indexed columns, SYSTEM and
> >> > > > SOFTWARE, which point to their respective tables.
>
> >> > > > What I want to do, is for a given SYSTEM ID, return all SOFTWARE *
> >> IDs
> >> > > > which are *not* listed in INSTALLS.
>
> >> > > > The query so far:
> >> > > > SELECT sw.*, i.*
> >> > > > FROM `SOFTWARE` sw
> >> > > > LEFT OUTER JOIN `INSTALLS` i ON i.`software` = sw.`id`
> >> > > > OR i.`system` IS NULL OR (NOT i.`system` = {id}) ;
>
> >> > > > The problem with this is that if the software is listed as *
> >> installed
> >> > > > on another system, it will always be returned, even if it is *
> >> already
> >> > > > installed on the system with ID {id}.
>
> >> > > > I'm sorry if this is a bit confusing, it's hard to wrap my head
> >> > > > around, but I'll try to clarify if needed.
>
> >> Please do not top post.
>
> >> Please supply the sample data as CREATE TABLE plus INSERT exports
> >> (possibly from phpMyAdmin).
>
> >> It is not clear from your post of sample data what is in what table.
>
> > Apologies for top-posting. The data is as decribed below. I'm looking
> > for a query that would return software IDs 1 and 4, given system ID 2.
>
> mysql> SELECT s.id FROM software s
> * * *-> LEFT JOIN installs i
> * * *-> ON i.software = s.id
> * * *-> AND i.system = 2
> * * *-> WHERE i.system IS NULL;
> +------+
> | id * |
> +------+
> | * *1 |
> | * *4 |
> +------+
> 2 rows in set (0.00 sec)
> --
> Rik Wasmus

Genius. Many, many thanks!