CODEWITHSUNDEEP
mysqlsqlselect
gubbfett
gubbfett

Reputation: 2177

MySQL select with statements

I'm learning some more SQL, and ran into a "problem",

I have two tabels like the link below http://www.sqlfiddle.com/#!2/403d4/1

EDIT: Since I'm quite retarded now from all SQL-test i have done this weekend, i asked really wrong... Sorry guys...

What i really wanted:

If i have the requested language, i want to get all the records, and if the requested language is not in the titles-table, i want it to say "" or null...

like if i ask for all records in 'de', i want:

1  |  ACHTUNG
2  |  NULL

and if i ask for all the records in 'en' i want

1  |  WARNING
2  |  Ambulance

Really sorry for the wrong question.

/* What i want to do: I have the ID of a record and the requested language. If the selected language does not exsists, i want it to take the other language.

like if i have: language = 'de' and id = 1 I want 'ACHTUNG',

if i have: language = 'de' and id = 2 i want "Ambulance" since there's no 'de'... */

How do i do this?

Upvotes: 3

Views: 157

Answers (2)

Nesim Razon
Nesim Razon

Reputation: 9794

http://www.sqlfiddle.com/#!2/403d4/89

SELECT rec.id, title.name
FROM Records rec
LEFT JOIN Titles title ON title.record_id = rec.id and title.language='de';

SELECT rec.id, title.name
FROM Records rec
LEFT JOIN Titles title ON title.record_id = rec.id and title.language='en';


ID  NAME
1   ACHTUNG
2   (null)

ID  NAME
1   Warning
2   Ambulance

Upvotes: 1

David Müller
David Müller

Reputation: 5351

You could apply a custom order by and only return the first row, like this:

SELECT
    rec.id,
    title.language,
    title.name

FROM Records rec

LEFT JOIN Titles title
ON title.record_id = rec.id

WHERE rec.id = 1
ORDER BY Field(title.language, "de") DESC
LIMIT 1

This query returns the record with your desired language first, if one exists. If not, it will just return what was found. You might want to take a look at this answer.

Upvotes: 0

Related Questions