Finding the result by my language order

Databases
1

1 Korea Korea English 2 Korea hánguó Chinese 3 Korea Hangook Korean 4 Italy Italy English 5 China zhōngguó Chinese 6 China China English 7 China Chine French 8 Germany déguó chinese 9 France France English 10 France la France French 11 America měiguó Chinese 12 America America English 13 America Meegook Korean 14 America les Etats- French I have myTable like the above.
The code below produces the result below

[code]SELECT englishName, languageName, language FROM myTable2 group BY englishName

Korea Korea English
Italy Italy English
China zhōngguó Chinese
Germany déguó chinese
France France English
America měiguó Chinese[/code]
Let’s suppose I am Korean, so my 1st language is Korean.
I study in Paris, So my 2nd language is French.
I do study English much, so my 3rd language is English.
I do sudy Chinese some, so my 4th language is Chinse.

I like to retrieve Korea in Korean language. Fortunately, there is Korea in Korean, i.e Hangook.
I like to retrieve Italy in Korean language, But no Italy in Korean in the DB, so I like to find it my 2nd language French, but it has not in DB. at last I found it Italy in my 3rd language.

I like to produces my target result like the below.

Korea Hangook Korean Italy Italy English China Chine French Germany déguó chinese France la France French America Meegook KoreanIs there anyway to get my target result in the above by your help?

2

first – learn how to use GROUP BY correctly

if you have GROUP BY englishName, then you cannot put languageName and language into the SELECT list unless inside an aggregate function

so this is incorrect –

SELECT englishName, languageName, language FROM myTable2 group BY englishName

but this is correct (if dubiously useful) –

SELECT englishName, MAX(languageName), MIN(language) FROM myTable2 group BY englishName

second – learn that there is no sequence of rows in a relational database table

so if you have these rows –

1 Korea Korea English 2 Korea hánguó Chinese 3 Korea Hangook Korean

then you cannot say which one comes first, because there is no sequence

well, actually, there is a sequence provided by the id value, but it does not accurately reflect your actual data – 1st language korean, 2nd french, etc.

once you learn these things, please ask your question again

3

SELECT englishName, max(languageName) , min (language) FROM myTable2 group BY englishName

Yes, it needs something specified one in GROUP BY like the above.
But, as you see, the code above doesn’t produces what I want.

I think the would-be code below which doesn’t work correctly might be tell what I wants clearer.

SELECT englishName, languageName(ORDER BY lanuage( Korean, French, English, Chinese) ) as languageName , language (ORDER BY lanuage( Korean, French, English, Chinese) ) as language FROM myTable2 group BY englishName
.

4

you should consider storing the language sequence

englishName languageName language sequence Korea Korea English 3 Korea hánguó Chinese 4 Korea Hangook Korean 1

5

what about the language sequence is dynamic?

min (language sequance)

The code above will work if language sequence is in the same table.

If it is in another table like the below?

[code]visitorTable

name lang1 lang2 lang3 lang4
joon Korean French English Chinse
rudy English French Chinse Korean
[/code]
Can I join myTable and visitorTable when I have the information that Joon is visited at the moement for producing the target result?

6

sure, that might work

7

I am afraid but I have to ask this again.

How can I join myTable and visitorTable with the information Joon is visited at the moment for producing my target result?

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.