Posted by Joseph Melnick on 06/03/05 06:22

Hello Brian,

Yes. This type of query will do what you need.

select a.storenumber,a.storename
from tableA a
left join tableA b
on a.id=b.id
and b.storenumber in ("SA002","SA003","SA004","SA005","SA006","SA007")
where b.storenumber is null;

Joseph Melnick
JM Web Consultants
http://www.jphp.com/




"Brian" <not@given.com> wrote in message
news:g9Pne.5838$%21.2912@newsfe2-gui.ntli.net...
> Joseph and NC, your both stars, so here's a beer each (_)3 (_)3,
> in fact what the hell have two (_)3 (_)3, it's all doing what it should be
> doing now :)
>
> This simple site (ha ha ha ha) that I have been asked to do has turned
> into
> a nightmare, in the past week I have had to get my head round more
> JavaScript than
> I have ever used, deal with tables of 300000 + records, and more
> complicated
> MySQL statements than I though I would ever use.
> Having said all that I have learnt a hell of a lot in the past few weeks
> :)
>
> And now its nearly time to put it to bed and move on, notice how I slipped
> in nearly......
>
> One last question (well I hope last), I have a list of store number I was
> going to use
> a NOT IN statement, but worked out this is not going to work. What I am
> trying
> to do is, read in the store numbers and locations from the txt file and
> create a strings like
>
> $storenumberlist = '"SA002","SA003","SA004","SA005","SA006","SA007"';
>
> $storenamelist = '"A Town","Big Town","Small town","The City","Corner
> Shop","Another town"';
>
> now run some sort of query and return a list of store numbers and store
> location that DON'T appear in the table,
> e.g. if SA007 was not in the table then return 'SA007' and 'Another town'
> the trouble is I soon worked out that the NOT IN statement works the wrong
> way round for what I need, and
> the fact it can't return data thats not in the table, any ideas on how to
> do this?
>
> this is where I was heading, then worked out it was the wrong way
>
> SELECT DISTINCT store, storename FROM $table_name WHERE (timestamp >=
> $sdate) AND
> (timestamp <= $edate) AND (store NOT IN ('$storenumberlist')) ORDER BY
> store";
>
> Looks like I may have to send you both a case of virtual beer :0
>
> Brian
>
>
>
>
>
>
>

[Back to original message]