CODEWITHSUNDEEP
mysqlsqlselect
Travis
Travis

Reputation: 368

Select by locale then language?

Two tables:

a
  id
b
  id
  a_id
  url

The urls in table b have a pattern like:

.../en-us/...
.../en-gb/...
.../de-at/...
.../de-de/...
.../ja-jp/...

Query:

SELECT b.url
FROM a
JOIN b ON b.a_id = a.id

How can I further narrow these results to select the url like %/en-us/% first, then like %/en-%/% if en-us does not exist? In other words, if I have two urls, one for both en-us and en-gb, how can I "prioritize" en-us over en-gb and only show one or the other?

Upvotes: 1

Views: 287

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Use a CASE expression with ORDER BY:

SELECT b.url
FROM a
INNER JOIN b
    ON a.id = b.a_id
ORDER BY
    CASE WHEN b.url LIKE '%/en-us/%' THEN 0 ELSE 1 END;

If you really only want to show one URL type, then you probably want a WHERE clause, something like:

WHERE b.url LIKE '%/en-us/%`

Upvotes: 1

Mureinik
Mureinik

Reputation: 311438

You can create a custom sorting order with a case expression and a bunch of conditions:

SELECT   b.url
FROM     a
JOIN     b ON b.a_id = a.id
ORDER BY CASE WHEN b.url LIKE '%/en-us/%' THEN 0
              WHEN b.url LIKE '%/en-gb/%' THEN 1
              WHEN b.url LIKE '%/en-%/%'  THEN 2
              ELSE                             3
         END

Upvotes: 2

Related Questions