Eliminating Multiple Rows

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.

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.

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.

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.

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');

On Tue, 06 May 2008 18:50:20 +0200, Warren <w.gray.matter@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

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!

Warren wrote:
> 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!

Rik beat me to it. Offering sample data like this plus specifying what you
expect from the query makes it really easy for us to help you. Hope to see
you here again.

On Tue, 06 May 2008 22:01:09 +0200, Paul Lautman
<paul.lautman@btinternet.com> wrote:
> Warren wrote:
>> 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:
>>>
>>> > 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;
>
> Rik beat me to it. Offering sample data like this plus specifying what
> you
> expect from the query makes it really easy for us to help you. Hope to
> see
> you here again.

Yup, more people should just provide create table & sample data
statements, makes it quite easy & clear.
--
Rik Wasmus