Posted by Joseph Melnick on 06/06/05 01:07

Hello Brian,

I have to apologize as I did not type the connecting clause A to B on
storenumber

> 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

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



Joseph Melnick


<not@given.com> wrote in message
news:mFDoe.2032$jS3.2012@newsfe2-win.ntli.net...
> 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]