CODEWITHSUNDEEP
phpmysql
charlymz
charlymz

Reputation: 147

MYSQL order by second table

i have a table called properties, i want to get all records in this table that match "hold=1" but an extra table holds property names in diferent languages i need to get the english language or if doesnt exist the language markes as default

table properties:

pid     hold

property_translations:

pid     lang_id     pname     description     isDefault

i normally get the language using a union

(select pname from property_translations where lang_id='en' and pid=$pid)
union
(select pname from property_translations where isDefault='Yes' and pid=$pid)
limit 1

Upvotes: 1

Views: 81

Answers (4)

Barmar
Barmar

Reputation: 780724

SELECT p.pid pid, pname
from properties p
JOIN (SELECT t1.pid tpid, IFNULL(t2.pname, t1.pname) pname
      FROM translations t1
      LEFT OUTER JOIN translations t2
      ON t1.pid = t2.pid
      AND t2.lang_id = 'en'
      WHERE t1.isDefault = 'Yes') t
ON p.pid = t.pid
WHERE p.hold = 1

Upvotes: 0

Aleks G
Aleks G

Reputation: 57306

Provided your select gets you the desired results and you only want to filter them by hold=1, you can simply join the first table:

(select pname from property_translations t
              join properties p on (t.pid=p.pid and p.hold = 1)
              where lang_id='en' and pid=$pid)
union
(select pname from property_translations t
              join properties p on (t.pid=p.pid and p.hold = 1)
              where isDefault='Yes' and pid=$pid
              and not exists (select 1 from property_translations t2
                              where t2.pid=t.pid and t2.lang_id='en' and t2.isDefault='Yes'))

Please note that I'm not checking the validity of your original select, just showing how to add the check against the second table.

Upvotes: 0

John Conde
John Conde

Reputation: 219804

Try:

   SELECT pt.pname
     FROM property_translations AS pt
LEFT JOIN properties AS p ON p.pid = pt.pid
    WHERE pt.pid = $pid
      AND p.hold = 1
      AND pt.lang_id='en'
      AND isDefault='Yes'

Upvotes: 0

Sam Grondahl
Sam Grondahl

Reputation: 2467

SELECT p.pid, t.pname FROM properties p LEFT OUTER JOIN translations t ON p.pid = t.pid
    WHERE p.hold=1 AND t.isDefault='Yes' AND NOT EXISTS
    ( SELECT * FROM translations ti WHERE ti.pid = p.pid AND ti.lang_id='en')
    UNION SELECT p.pid, t.pname FROM properties p 
    LEFT OUTER JOIN translations t ON p.pid = t.pid
    WHERE p.hold=1 AND t.lang_id='en';

Upvotes: 1

Related Questions