|
|
Posted by Brian on 06/08/05 02:37
Hi Joseph
After spending hours trying to get this to work, and then asking a friend
who spent a good hour trying to get it to work, we think we have worked
out that it's not going to work.
$storenumberlist' is a list of all stores
The rouble is the $customer (the main table) may not have a entry for
every store. So my friend said he does think it will work, any ideas
how to get this to work?
Brian
"Brian" <not@given.com> wrote in message
news:Fq0pe.97$q46.2@newsfe1-win.ntli.net...
> Hi Joseph
>
> I'm slowly getting there, but seem to be getting a lot more results than I
> though I would,
> can you see if this is right, I have to be honest I have read up on this
> left join and I'm
> a little confused about it, so not sure it I am doing something wrong, the
> online help
> on the MySQL site isn't very clear to somebody that has never done it !
>
> $query = "SELECT DISTINCT a.store,a.storename FROM $customer a LEFT JOIN
> $customer b ON a.id=b.id AND a.store = b.store AND b.store in
> ('$storenumberlist') WHERE (b.store is null) AND (a.timestamp >= $sdate)
> AND (a.timestamp <= $edate) ORDER BY b.store";
>
> the table stucsure is
>
> id INT AUTO_INCREMENT PRIMARY KEY,
> date varchar(50),
> orderno varchar(50),
> store varchar(100),
> storename varchar(100),
> dnote varchar(50),
> status varchar(20),
> product varchar(200),
> description varchar(200),
> ord varchar(50),
> del varchar(50),
> brand varchar(50),
> timestamp varchar(20)
>
>
> Once again, many thanks for this
>
> Brian
>
>
>
> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
> news:7cOdnbz6qv0n6j7fRVn-sg@rogers.com...
>> 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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Navigation:
[Reply to this message]
|