MOST COMPLICATED query ever

[dastr]

Hi all,
I have the following two tables (you may also refer to attachment)
Table A
Person Index Symbol
Inna - T1 - alpha
Inna - T4 - beta, theta, delta
Maria - T1 - delta, theta
Maria - T4 - alpha, gamma

And

Table Conversion
Symbol Index Combination Symbol value
Alpha - alphaT1 - 10
Beta - betaT1 - 18
Gamma - gammaT1 - 14
Delta - deltaT1 - 100
Theta - thetaT1 - 1
Alpha - alphaT4 - 10
Beta - betaT4 - 18
Gamma - gammaT4 - 14
Delta - deltaT4 - 100
Theta - thetaT4 - 1

And the idea is to get:

Table Outcome
Person SymbolValues Lowest Value
Inna - 10- 10
Inna - 18,100,1 - 1
Maria - 100,1 - 1
Maria - 10,14 - 10

Logic: in table Conversion we look for the symbol value on basis of Index and Symbol from Table A; these values get populated in Symbol value in table Outcome and then we need the Lowest Value of the latter in column Lowest Value Table Outcome.

Does anyone know how this could be done?
Thank you VERY VERY much!

 

[John Big Booty]

I'm having a little trouble following exactly what it is you are trying to achieve, but I suspect that the first thing you will want to do is properly normalise your table structure. Once that has been done your task should be a whole lot easier.

 

[MStef]

Your query is complicated (as John said) because your tables not normalized
well. Learn something about DATABASE NORMALIZATION.

 

[dastr]

Hi John,

Correct, it is a bit complicated and I have been struggling with this data for some time now, therefore I am here.

The logic - pls see Table A Inna - Inna has an index of T1 and the symbol is alpha. In table Conversion you see that the combination T1 and alpha - gives you a value of 10, so I want this value.

For Inna T4 (second line in table A) you have multiple symbols thou - beta, theta, delta adn same index T4; combination detaT4 gives you value 18 (table Conversion) combination theta and T4 - give you value 100 adn combination delta T4 - gives you value 1 - so in teh outcome for Inna line 2 - I will have Inna - 18,100,1 - in thsi case I would like to have the lowest value of these - ie 1.

I am not sure whether I managed to explain it and whether it is a bit more clear now?

pls let me know,
thx

I'm having a little trouble following exactly what it is you are trying to achieve, but I suspect that the first thing you will want to do is properly normalise your table structure. Once that has been done your task should be a whole lot easier.

 

[John Big Booty]

What do the indexes T1 and T4 represent?

Why do some indexes have multiple symbols and others don't?

I ask these questions so I can better understand what it is you are trying to achieve, and how to restructure your tables.

 

[dastr]

Hi John,

Indeed, a very good question.

The indexes give a sign of belonging (understand more like a group); say that according to which index they belong, the symbols may have different values.

If alpha belongs to T1, alpha has a meaning of 10; if alpha belongs to T4 - it has a value of 10.

Actually, my main concern is when I have multiple symbols in the same line:

Inna - T4 - beta, theta, delta

how I can look yp in teh conversion table these symbol;s if they are in the same line separated by comma?

Is it more clear now? Let me know if I may explain better.
Thank you.

What do the indexes T1 and T4 represent?

Why do some indexes have multiple symbols and others don't?

I ask these questions so I can better understand what it is you are trying to achieve, and how to restructure your tables.

 

[John Big Booty]

Have a look at the changes I've made to your DB

 

[dastr]

Thank you, John! Thx indeed, I was stuck on it for quite some time!

Have a look at the changes I've made to your DB

 

[dastr]

Hi John,

I am trying to implement your solution, however my data consists of over 36000 names (different names) and the symbols per name could be up to 20. Therefore although your solutions seems to work, it works only with a limited number of entries or am I missing something?

I originally though of splitting up the symbols separated by comma and looking them one by one (in combination with the INDEX) in the Conversion table, and then taking the lowest entry. Do you know how I may do this - split the symbols in different columns? - Please look in Table A in teh attached database?

Thank you

Have a look at the changes I've made to your DB

 

[dastr]

Hi John,

Here maybe a better example of what we are trying to achieve with my classmates:

We have that shoe shop:

Article - Size - Price - Index

ABC - 50,52 - $20 - T1
ABC - 60,62 - $50 - T1
123 - 66,68 - $110 - T4
123 - 70,72 - $80 - T4

Conversion Table

Index - SINGLE Size - Value
T1 - 50 - 1
T1 - 52 - 2
T1 - 60 - 7
T1 - 62 - 8

So for article ABC, we need to find the best price - to find it we need to take the INDEX and combine it with the single size - then this combination say for ABC - T1 and 50 has a value of 1; T1 and 52 value of 2, etc, the lowest value is 1 for article ABC so we need price $20.

In our example we are dealing with many articles, which does not work with your suggestion below, my apologies, maybe I shoudl have mentioned the high number of the entries earlier.

Thank you

Have a look at the changes I've made to your DB