Selecting same fields multiple times in same row?

Presumably the ID field is not unique.  To get two languages into the same row, use something like:

SELECT A.ID as ID, A.LangID as LangID1, A.LangName as LangName1, B.LangID as LangID2, B.LangName as LangName2 FROM mytable A, mytable B WHERE A.ID = B.ID AND A.ID=<some value>

To get three languages, use something like:

SELECT A.ID as ID, A.LangID as LangID1, A.LangName as LangName1, B.LangID as LangID2, B.LangName as LangName2, C.LangID as LangID3, C.LangName as LangName3 FROM mytable A, mytable B, mytable C WHERE A.ID = B.ID AND A.ID = C.ID AND A.ID=<some value>

The problem you face is knowing how many languages there are in order to correctly create your SELECT.

Hi Barthax,

I can see where you are going with the aliases, but in my case I have the languages in the same table, i.e. site_lang.id, site_lang.lang_id, site_lang.content.

site_lang.id will ofcourse be uniqe per record, but say I want to fetch 2 languages from that table in the same row.

site_lang.content for site_lang.id = 1 and site_lang.content for site_lang.lang_id = 2, the classic way would be:

SELECT content FROM site_lang WHERE id IN (1,2)

but that would leave me with 2 rows, I really want to return the 2 languages in the same row.

whoops, query above should be:

SELECT content FROM site_lang WHERE lang_id IN (1,2)

> If I have a table that stores different language values

I may have made an assumption, so to clarify: does the database hold strings of data in different languages?  You are obviously trying to get multiple records returned - what is the defining characteristic of those records (of differing languages) which makes them the same?

> site_lang.id will ofcourse be uniqe per record, but say I want to fetch 2 languages from that table in the same row.

Perhaps the following is what you're after (if not, please answer the above)?

SELECT A.ID as ID1, A.LangID as LangID1, A.LangName as LangName1, B.ID as ID2, B.LangID as LangID2, B.LangName as LangName2 FROM mytable A, mytable B WHERE A.ID <> B.ID AND A.ID IN (1,2) AND B.ID in (1,2)

I have a system where I store strings in multiple languages, depending on the user I want to show the right language, however, sometimes French isn't stored in the DB but English is, so I want to select a primary and secondary language at the same time so I don't have to re-query the DB is something is missing. I have a single table that holds 1 record/string for each language for each parent record. So say I want to fetch a welcome text in French but it isn't there, I want to display it in English instead.

I just tried the query you stated above, and it does fetch both langauges in the same row, however, they are not ordered, even though I specify that A is "primary" and B is "secondary" the primary and secondary values are mixed up in some of the rows.

We are getting close though. I truly appreciate your help this far.

Quick question, this one has been puzzling me, I can see what the A.ID <> B.ID does if I try with and without, but what does it actually mean in SQL ??? they can't be equal, but in what way does that affect the result?

As the "two tables" being retrieve in the FROM portion are actually the same table, what the A.ID <> B.ID ensures is that the content of A fields are going to be a different record to the content of the B fields.  It is a bit redundant in the end, but then you may have other fields or a need for expansion which makes it relevant once more.