I have a query that gies me the distice from a postalcode to a store. It
gives me the five closest stores:

SELECT `store_id`
FROM `distance`
WHERE `postcode` =3000
ORDER BY `distance` ASC
LIMIT 0 , 4

Output is here
1080
1008
1121
1073

I also have a query that will give me the data from the store:
SELECT `StoreNumber` , `StoreName` , `Address` , `ZipCode` , `City` ,
`StorePhone`
FROM `stores`
WHERE `StoreNumber` =1080
LIMIT 0 , 30

Now obviously I would like to have a query that wll give me an outpit
like:
1080 Store_X Street_X 3000 Leuven 016/123456
1008 Store_B Street_B 3200 Aarschot
1121 Store_Z Street_Z 2000 Antwerpen 03/1234567
1073 Store_A Street_A 9000 Gent 09/1234567

How can I achieve this? I have looked, yet when I try I get an error
that there is more then one line.

houghi
--
They say pesticides have been linked to low sperm counts.
In my opinion if you have bugs down there that are so bad
you need to use a pesticide, you're not gonna get laid anyway.

On Tue, 30 Oct 2007 22:10:24 +0100, houghi <houghi@houghi.org.invalid>
wrote:

> I have a query that gies me the distice from a postalcode to a store. It
> gives me the five closest stores:
>
> SELECT `store_id`
> FROM `distance`
> WHERE `postcode` =3000
> ORDER BY `distance` ASC
> LIMIT 0 , 4

That's 4, not 5 :P

> Output is here
> 1080
> 1008
> 1121
> 1073
>
> I also have a query that will give me the data from the store:
> SELECT `StoreNumber` , `StoreName` , `Address` , `ZipCode` , `City` ,
> `StorePhone`
> FROM `stores`
> WHERE `StoreNumber` =1080
> LIMIT 0 , 30
>
> Now obviously I would like to have a query that wll give me an outpit
> like:
> 1080 Store_X Street_X 3000 Leuven 016/123456
> 1008 Store_B Street_B 3200 Aarschot
> 1121 Store_Z Street_Z 2000 Antwerpen 03/1234567
> 1073 Store_A Street_A 9000 Gent 09/1234567
>
> How can I achieve this? I have looked, yet when I try I get an error
> that there is more then one line.

Giving the query you tried would have helped. Try something like:

SELECT d.`store_id`, s.*
FROM `distance` d
JOIN `stores` s
ON s.StoreNumber = d.store_id
WHERE `postcode` =3000
ORDER BY d.`distance` ASC
LIMIT 0, 4
--
Rik Wasmus

houghi wrote:
> How can I achieve this? I have looked, yet when I try I get an error
> that there is more then one line.

Obviously, just when I send post, I find the answer. Just do the
following 5 times with an increasing LIMIT

SELECT `StoreNumber`, `StoreName`, `Address`, `ZipCode`, `City`,
`StorePhone` FROM `stores` WHERE `StoreNumber` = (
SELECT `store_id`
FROM `distance`
WHERE `postcode` =3000
ORDER BY `distance` ASC
LIMIT 0 , 1
)

It works, however I can imagine there is a nicer way.

houghi
--
They say pesticides have been linked to low sperm counts.
In my opinion if you have bugs down there that are so bad
you need to use a pesticide, you're not gonna get laid anyway.

Rik Wasmus wrote:
>> SELECT `store_id`
>> FROM `distance`
>> WHERE `postcode` =3000
>> ORDER BY `distance` ASC
>> LIMIT 0 , 4
>
> That's 4, not 5 :P

Yeah, I saw that I had 4 samples, so I changed the code, but not the
line explaining what I wanted. :-/

>> How can I achieve this? I have looked, yet when I try I get an error
>> that there is more then one line.
>
> Giving the query you tried would have helped. Try something like:

You are right. I am sory for not having done that. I should have known
better.

> SELECT d.`store_id`, s.*
> FROM `distance` d
> JOIN `stores` s
> ON s.StoreNumber = d.store_id
> WHERE `postcode` =3000
> ORDER BY d.`distance` ASC
> LIMIT 0, 4

That works partialy. OK, it work completely, yet I need something a
little bit differently. I have stores in "stores" that I do not have in
"distance" and thus would not like to see. I asume I could add an extra
field in "stores" to wether to show it or not and that would be my
prefered choice.

Will look into that. Mainly I need to add the following information
anyway: Is the store open or closed. Is the store for b2b or b2c? I do
not want b2b customers going to a b2c store.

The above already helped me a lot. Thanks.

Just another question for people with experience: would it be wiser to
have the names store_id and StoreNumber or would it be better to use the
same name for both? I can see advantages and disadvatages in both, yet
would like to hear wath people working with databases and MySQL think
about that.

houghi
--
houghi http://houghi.org http://www.plainfaqs.org/linux/
http://www.netmeister.org/news/learn2quote.html
>
> Today I went outside. My pupils have never been tinier...

On Wed, 31 Oct 2007 09:07:24 +0100, houghi <houghi@houghi.org.invalid>
wrote:

> Rik Wasmus wrote:
>>> SELECT `store_id`
>>> FROM `distance`
>>> WHERE `postcode` =3000
>>> ORDER BY `distance` ASC
>>> LIMIT 0 , 4
>>
>> That's 4, not 5 :P
>
> Yeah, I saw that I had 4 samples, so I changed the code, but not the
> line explaining what I wanted. :-/
>
>>> How can I achieve this? I have looked, yet when I try I get an error
>>> that there is more then one line.
>>
>> Giving the query you tried would have helped. Try something like:
>
> You are right. I am sory for not having done that. I should have known
> better.
>
>> SELECT d.`store_id`, s.*
>> FROM `distance` d
>> JOIN `stores` s
>> ON s.StoreNumber = d.store_id
>> WHERE `postcode` =3000
>> ORDER BY d.`distance` ASC
>> LIMIT 0, 4
>
> That works partialy. OK, it work completely, yet I need something a
> little bit differently. I have stores in "stores" that I do not have in
> "distance" and thus would not like to see.

And it won't. This query checks for store_id's in distance, and joins
stores to it. If a store_id is not in distance, it will never be a result
of this query. If you do get a store you don't want, please post some
sample data and the query used.
--
Rik Wasmus

Rik Wasmus wrote:
>> That works partialy. OK, it work completely, yet I need something a
>> little bit differently. I have stores in "stores" that I do not have in
>> "distance" and thus would not like to see.
>
> And it won't. This query checks for store_id's in distance, and joins
> stores to it.

And it does that perfectly. However I need to exclude some stores.

> If a store_id is not in distance, it will never be a result
> of this query. If you do get a store you don't want, please post some
> sample data and the query used.

Will do later today or tomorrow, when I am at my own PC again and have
more time.

houghi
--
houghi http://houghi.org http://www.plainfaqs.org/linux/
http://www.netmeister.org/news/learn2quote.html
>
> Today I went outside. My pupils have never been tinier...