 | |
#1
 
| |||
| |||
|
Eliminating Multiple Rows -
05-06-2008
, 10:20 AM
#2
| |||
| |||
|
|
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. |
#3
| |||
| |||
|
|
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. |
#4
| |||
| |||
|
|
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... (AT) gmail (DOT) 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 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.
#5
| |||
| |||
|
|
On 6 May, 16:28, Warren <w.gray.mat... (AT) gmail (DOT) 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... (AT) gmail (DOT) 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}) ; |
Quote:
|
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. |
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');
#6
| |||
| |||
|
|
On May 6, 11:59*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote: On 6 May, 16:28, Warren <w.gray.mat... (AT) gmail (DOT) 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... (AT) gmail (DOT) 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. |
-> LEFT JOIN installs i
-> ON i.software = s.id
-> AND i.system = 2
-> WHERE i.system IS NULL;
+------+
Quote:
|
id | +------+ 1 | 4 | +------+ |
--
Rik Wasmus
#7
| |||
| |||
|
|
On Tue, 06 May 2008 18:50:20 +0200, Warren <w.gray.mat... (AT) gmail (DOT) com> wrote: On May 6, 11:59*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote: On 6 May, 16:28, Warren <w.gray.mat... (AT) gmail (DOT) 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... (AT) gmail (DOT) 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 |
#8
| |||
| |||
|
|
On May 6, 1:08 pm, "Rik Wasmus" <luiheidsgoe... (AT) hotmail (DOT) com> wrote: On Tue, 06 May 2008 18:50:20 +0200, Warren <w.gray.mat... (AT) gmail (DOT) com wrote: On May 6, 11:59 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com wrote: On 6 May, 16:28, Warren <w.gray.mat... (AT) gmail (DOT) 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... (AT) gmail (DOT) 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! |
expect from the query makes it really easy for us to help you. Hope to see
you here again.
#9
| |||
| |||
|
|
Warren wrote: On May 6, 1:08 pm, "Rik Wasmus" <luiheidsgoe... (AT) hotmail (DOT) com> wrote: On Tue, 06 May 2008 18:50:20 +0200, Warren <w.gray.mat... (AT) gmail (DOT) com wrote: On May 6, 11:59 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com wrote: On 6 May, 16:28, Warren <w.gray.mat... (AT) gmail (DOT) 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... (AT) gmail (DOT) 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. |
statements, makes it quite easy & clear.
--
Rik Wasmus
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.