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