SQL Data Manipulation Question -- Crazy man!

weblingual · #1 (**permalink**) 01-01-05, 18:29

Below is a sample recordset from a table in my MySQL 4.1 DB. You can see 5 rows of data below. Each row has a langID associated with it (1=Eng, 2=Japanese & 3=Korean). If my primary (selected) language is Japanese(2), then I need to grab only 3 of the following records:

- 73(langID:1) - because there is no Japanese version.
- 69(langID:2) - the Japanese Version
- 82(langID:3) - because there is no Japanese version

Note: if there is a (langID=1) and (langID=3) value in the results above AND NO (langID=2) equivalent, then I need to grab the English version. (how's that for a twist?)

Is this possible with SQL or do I need to come up with a Cold Fusion solution?

Thanks for the brain-cycles...

Code:

CompanyPID     company           langID
73             CAPITOL RECORDS     1
69             DRT RECORDS         1
69             DRT の記録             2
69             DRT 부용공             3
82             BMI 쯔이               3

r937 · #2 (**permalink**) 01-01-05, 20:19

please let me know if this works --

Code:

select L2.CompanyPID
     , L2.company
  from yourtable as L2
 where L2.langID = 2
union all
select CompanyPID
     , company
  from yourtable as L1
left outer
  join yourtable as L2
    on L1.CompanyPID 
     = L2.CompanyPID 
   and L2.langID = 2     
 where L1.langID = 1
   and L2.CompanyPID is null 
union all 
select CompanyPID
     , company
  from yourtable as L3
left outer
  join yourtable as L2
    on L3.CompanyPID 
     = L2.CompanyPID 
   and L2.langID = 2     
left outer
  join yourtable as L1
    on L3.CompanyPID 
     = L1.CompanyPID 
   and L1.langID = 1
 where L3.langID = 3
   and L2.CompanyPID is null 
   and L1.CompanyPID is null

r937 · #3 (**permalink**) 01-01-05, 20:22

p.s. please don't cross-post

i've deleted the duplicate post in the SQL forum

Internet Services

Web Development

Digital Marketing

Consumer Tech