Posted by Brian on 06/05/05 17:06

Hi Joseph,

In the best way i can put it WTF? I had a read up on the left join, and now
more confussed than when i started !

this is the final statment, but will not run

SELECT a.storenumber,a.storename FROM $customer a LEFT JOIN $customer b on
a.id=b.id AND b.storenumber in ('$storenumberlist') where b.storenumber is
null

Brian



"Joseph Melnick" <jmelnick@jphp.com> wrote in message
news:68OdnatSJvtsUQLfRVn-vQ@rogers.com...
> 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]