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.

create table `systems` (
`id` double ,
`hostname` varchar (255)
);
insert into `systems` (`id`, `hostname`) values('1','System1');
insert into `systems` (`id`, `hostname`) values('2','System2');

create table `software` (
`id` double ,
`name` varchar (255)
);
insert into `software` (`id`, `name`) values('1','Package1');
insert into `software` (`id`, `name`) values('2','Package2');
insert into `software` (`id`, `name`) values('3','Package3');
insert into `software` (`id`, `name`) values('4','Package4');

create table `installs` (
`id` double ,
`system` double ,
`software` double
);
insert into `installs` (`id`, `system`, `software`)
values('1','1','1');
insert into `installs` (`id`, `system`, `software`)
values('2','2','2');
insert into `installs` (`id`, `system`, `software`)
values('3','1','2');
insert into `installs` (`id`, `system`, `software`)
values('4','2','3');