Thanks for the help on previous question, here I have a new one...
Hello all, I need some help on setting up a query to solve the following.
Consider a table with this structure and these data:

ID    Location    Customer    Number
1            1                1
2            1                2
3            1                1
4            1                2
5            1                2
1            2                                3
2            2                                4
3            2                                2
1            3
2            3

I want to see all records with location = 2, but I would like to see the
Customer ID as well for the record where the ID matches. In this case it
should return three records:

ID    Location    Customer    Number
1            2                1                3
2            2                2                4
3            2                1                2

Can someone help out?

Thanks
Petter Topp
D7, Dbisam 4

Hi

Just curious but why do you not save the CustomerID on the other location
records instead only on the first?

Bye
Rolf


"Petter Topp" <petter.topp@atcdata.no> schrieb im Newsbeitrag
news:3EB13BBA-C675-4514-9C9B-4B5E00571E32@news.elevatesoft.com...
> Thanks for the help on previous question, here I have a new one...
> Hello all, I need some help on setting up a query to solve the following.
> Consider a table with this structure and these data:
>
> ID    Location    Customer    Number
> 1            1                1
> 2            1                2
> 3            1                1
> 4            1                2
> 5            1                2
> 1            2                                3
> 2            2                                4
> 3            2                                2
> 1            3
> 2            3
>
> I want to see all records with location = 2, but I would like to see the
> Customer ID as well for the record where the ID matches. In this case it
> should return three records:
>
> ID    Location    Customer    Number
> 1            2                1                3
> 2            2                2                4
> 3            2                1                2
>
> Can someone help out?
>
> Thanks
> Petter Topp
> D7, Dbisam 4
>
>
>
>

Hello Rolf,

This is the way data is received from mobile devices.
I've considered handeling this when data is posted, but if it's possible I
would like to keep the data as is for reference to what has actually
happened.

Do you have any Idea?

Petter


"Rolf Frei" <rolf@eicom.ch> skrev i melding
news:9B1E7460-1844-4ED1-B6FA-12D6D3FE93B6@news.elevatesoft.com...
> Hi
>
> Just curious but why do you not save the CustomerID on the other location
> records instead only on the first?
>
> Bye
> Rolf
>
>
> "Petter Topp" <petter.topp@atcdata.no> schrieb im Newsbeitrag
> news:3EB13BBA-C675-4514-9C9B-4B5E00571E32@news.elevatesoft.com...
>> Thanks for the help on previous question, here I have a new one...
>> Hello all, I need some help on setting up a query to solve the following.
>> Consider a table with this structure and these data:
>>
>> ID    Location    Customer    Number
>> 1            1                1
>> 2            1                2
>> 3            1                1
>> 4            1                2
>> 5            1                2
>> 1            2                                3
>> 2            2                                4
>> 3            2                                2
>> 1            3
>> 2            3
>>
>> I want to see all records with location = 2, but I would like to see the
>> Customer ID as well for the record where the ID matches. In this case it
>> should return three records:
>>
>> ID    Location    Customer    Number
>> 1            2                1                3
>> 2            2                2                4
>> 3            2                1                2
>>
>> Can someone help out?
>>
>> Thanks
>> Petter Topp
>> D7, Dbisam 4
>>
>>
>>
>>
>
>
>

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:3EB13BBA-C675-4514-9C9B-4B5E00571E32@news.elevatesoft.com...
> Thanks for the help on previous question, here I have a new one...

I have a feeling this problem is more complicated than what you're
describing, but something like this might work:

SELECT T1.ID, T1.LOCATION,
IF(T1.CUSTOMER = NULL THEN T2.CUSTOMER ELSE T1.CUSTOMER), T1.NUMBER
FROM TABLE T1
JOIN TABLE T2 ON T1.ID = T2.ID
WHERE T1.LOCATION = 2

just eyeballing it (not tested) this should work for all three locations.

Robert

> Hello all, I need some help on setting up a query to solve the following.
> Consider a table with this structure and these data:
>
> ID    Location    Customer    Number
> 1            1                1
> 2            1                2
> 3            1                1
> 4            1                2
> 5            1                2
> 1            2                                3
> 2            2                                4
> 3            2                                2
> 1            3
> 2            3
>
> I want to see all records with location = 2, but I would like to see the
> Customer ID as well for the record where the ID matches. In this case it
> should return three records:
>
> ID    Location    Customer    Number
> 1            2                1                3
> 2            2                2                4
> 3            2                1                2
>
> Can someone help out?
>
> Thanks
> Petter Topp
> D7, Dbisam 4
>
>
>
>

Hello Robert,

What is T2, is it a sub query?

Petter

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:7F231E40-69EB-4F71-A639-BC776C526079@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:3EB13BBA-C675-4514-9C9B-4B5E00571E32@news.elevatesoft.com...
>> Thanks for the help on previous question, here I have a new one...
>
> I have a feeling this problem is more complicated than what you're
> describing, but something like this might work:
>
> SELECT T1.ID, T1.LOCATION,
> IF(T1.CUSTOMER = NULL THEN T2.CUSTOMER ELSE T1.CUSTOMER), T1.NUMBER
> FROM TABLE T1
> JOIN TABLE T2 ON T1.ID = T2.ID
> WHERE T1.LOCATION = 2
>
> just eyeballing it (not tested) this should work for all three locations.
>
> Robert
>
>> Hello all, I need some help on setting up a query to solve the following.
>> Consider a table with this structure and these data:
>>
>> ID    Location    Customer    Number
>> 1            1                1
>> 2            1                2
>> 3            1                1
>> 4            1                2
>> 5            1                2
>> 1            2                                3
>> 2            2                                4
>> 3            2                                2
>> 1            3
>> 2            3
>>
>> I want to see all records with location = 2, but I would like to see the
>> Customer ID as well for the record where the ID matches. In this case it
>> should return three records:
>>
>> ID    Location    Customer    Number
>> 1            2                1                3
>> 2            2                2                4
>> 3            2                1                2
>>
>> Can someone help out?
>>
>> Thanks
>> Petter Topp
>> D7, Dbisam 4
>>
>>
>>
>>
>
>

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:F9DFA7EB-D95F-493E-8D96-11CC98358883@news.elevatesoft.com...
> Hello Robert,
>
> What is T2, is it a sub query?
>

No, it is the same physical table, just using a different cursor. From
DBISAM's view, it is accessing two different tables.

Robert

Of course, TABLE is the reference to the table name....

I have tried it, but have put on a couple of changes, but don't exactly now
how they affect performance.
They do however give me the intended result.
This is clearly SQL outside of my grasp, is there any performance issues
here?

SELECT T1.ID, T1.LOCATION,
IF(T1.CUSTOMER = NULL THEN T2.CUSTOMER ELSE T1.CUSTOMER), T1.NUMBER
FROM TABLE T1
RIGHT JOIN TABLE T2 ON T1.ID = T2.ID
WHERE T1.LOCATION = 2
GROUP BY ID

PS. I have tested this using even more fields like this, and it seems to
work nicely.

Thanks
Petter

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:CF30D5FF-1989-4666-B1EC-3A42913E71F9@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:F9DFA7EB-D95F-493E-8D96-11CC98358883@news.elevatesoft.com...
>> Hello Robert,
>>
>> What is T2, is it a sub query?
>>
>
> No, it is the same physical table, just using a different cursor. From
> DBISAM's view, it is accessing two different tables.
>
> Robert
>
>
>

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:8146F4A9-D87E-4BA5-A34A-4C5BD55FBA56@news.elevatesoft.com...
> This is clearly SQL outside of my grasp, is there any performance issues
> here?
>

No, it's pretty simple stuff. An index in ID obviously would make it faster.

Why did you change the JOIN to RIGHT JOIN? Why the GROUP BY when you don't
have any aggregate fields? Does your table have duplicate ID / Location
pairs?

I suspected this was a bit more complicated than originally stated

Robert

> SELECT T1.ID, T1.LOCATION,
> IF(T1.CUSTOMER = NULL THEN T2.CUSTOMER ELSE T1.CUSTOMER), T1.NUMBER
> FROM TABLE T1
> RIGHT JOIN TABLE T2 ON T1.ID = T2.ID
> WHERE T1.LOCATION = 2
> GROUP BY ID
>
> PS. I have tested this using even more fields like this, and it seems to
> work nicely.
>
> Thanks
> Petter
>
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
> news:CF30D5FF-1989-4666-B1EC-3A42913E71F9@news.elevatesoft.com...
>>
>> "Petter Topp" <petter.topp@atcdata.no> wrote in message
>> news:F9DFA7EB-D95F-493E-8D96-11CC98358883@news.elevatesoft.com...
>>> Hello Robert,
>>>
>>> What is T2, is it a sub query?
>>>
>>
>> No, it is the same physical table, just using a different cursor. From
>> DBISAM's view, it is accessing two different tables.
>>
>> Robert
>>
>>
>>
>
>

Hi Robert,

Yes I have duplicate ID and LOCATION although not in pairs, and without
these changes, the query returned three sets of the ID.
It was actually enough with the Group By to solve this.
But you introduced me into running two cursors in a query, that is really
nice.

Thank you very much, and have a nice week end.
Petter Topp



"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:3AFF4F90-2BEA-4D25-AC72-5996EB9AB0C9@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:8146F4A9-D87E-4BA5-A34A-4C5BD55FBA56@news.elevatesoft.com...
>> This is clearly SQL outside of my grasp, is there any performance issues
>> here?
>>
>
> No, it's pretty simple stuff. An index in ID obviously would make it
> faster.
>
> Why did you change the JOIN to RIGHT JOIN? Why the GROUP BY when you don't
> have any aggregate fields? Does your table have duplicate ID / Location
> pairs?
>
> I suspected this was a bit more complicated than originally stated
>
> Robert
>
>> SELECT T1.ID, T1.LOCATION,
>> IF(T1.CUSTOMER = NULL THEN T2.CUSTOMER ELSE T1.CUSTOMER), T1.NUMBER
>> FROM TABLE T1
>> RIGHT JOIN TABLE T2 ON T1.ID = T2.ID
>> WHERE T1.LOCATION = 2
>> GROUP BY ID
>>
>> PS. I have tested this using even more fields like this, and it seems to
>> work nicely.
>>
>> Thanks
>> Petter
>>
>> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
>> news:CF30D5FF-1989-4666-B1EC-3A42913E71F9@news.elevatesoft.com...
>>>
>>> "Petter Topp" <petter.topp@atcdata.no> wrote in message
>>> news:F9DFA7EB-D95F-493E-8D96-11CC98358883@news.elevatesoft.com...
>>>> Hello Robert,
>>>>
>>>> What is T2, is it a sub query?
>>>>
>>>
>>> No, it is the same physical table, just using a different cursor. From
>>> DBISAM's view, it is accessing two different tables.
>>>
>>> Robert
>>>
>>>
>>>
>>
>>
>
>

Hello Robert,

I'm still at work with this problem, and as you stated, it might be a bit
more complicated than it seems....
If you had the time I could post the Dbisam table with some sample data, and
I could explain what the problem is.
Could yoiu confirm this before I post a message in the binaries.

Regards
Petter Topp

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:7F231E40-69EB-4F71-A639-BC776C526079@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:3EB13BBA-C675-4514-9C9B-4B5E00571E32@news.elevatesoft.com...
>> Thanks for the help on previous question, here I have a new one...
>
> I have a feeling this problem is more complicated than what you're
> describing, but something like this might work:
>
> SELECT T1.ID, T1.LOCATION,
> IF(T1.CUSTOMER = NULL THEN T2.CUSTOMER ELSE T1.CUSTOMER), T1.NUMBER
> FROM TABLE T1
> JOIN TABLE T2 ON T1.ID = T2.ID
> WHERE T1.LOCATION = 2
>
> just eyeballing it (not tested) this should work for all three locations.
>
> Robert
>
>> Hello all, I need some help on setting up a query to solve the following.
>> Consider a table with this structure and these data:
>>
>> ID    Location    Customer    Number
>> 1            1                1
>> 2            1                2
>> 3            1                1
>> 4            1                2
>> 5            1                2
>> 1            2                                3
>> 2            2                                4
>> 3            2                                2
>> 1            3
>> 2            3
>>
>> I want to see all records with location = 2, but I would like to see the
>> Customer ID as well for the record where the ID matches. In this case it
>> should return three records:
>>
>> ID    Location    Customer    Number
>> 1            2                1                3
>> 2            2                2                4
>> 3            2                1                2
>>
>> Can someone help out?
>>
>> Thanks
>> Petter Topp
>> D7, Dbisam 4
>>
>>
>>
>>
>
>
>