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.