जब मैं इस sql क्वेरी का उपयोग कर रहा हूँ तो इसका मूल्य डुप्लिकेट हो जाता है, कि तालिका में केवल एक ही रिकॉर्ड है और इसके 8 रिकॉर्ड हैं

Question

SELECT 
    users . *,
    el.elo_name,
    ed.ede_name,
    ent.ent_name,
    c1.chl_label AS country,
    c2.chl_label AS state,
    c3.chl_label AS city
FROM

( SELECT  *, 
CASE users.usr_active 
WHEN "1" THEN "YES" 
WHEN "0" THEN "No" 
END AS users_active 
FROM users 
) as  users
        LEFT JOIN
    choicelist c1 ON users.usr_country = c1.chl_value
        AND c1.chl_element = 'country'
        LEFT JOIN
    choicelist c2 ON users.usr_state = c2.chl_value
        AND c2.chl_element = 'state'
        LEFT JOIN
    choicelist c3 ON users.usr_city = c3.chl_value
        AND c3.chl_element = 'city',
    entity ent,
    entity_locations el,
    entity_departments ed



WHERE
    users.usr_entity_location_id = el.id
        and users.usr_entity_department_id = ed.id

बस मुझे बताओ कि मैं गलत कहाँ जा रहा हूँ

mysql

1

mahes 26 नवम्बर 2015, 10:49

Answer 1

आइए इसकी शुरुआत करते हैं ...

SELECT
el.elo_name,
ed.ede_name,
ent.ent_name,
c1.chl_label AS country,
c2.chl_label AS state,
c3.chl_label AS city,
CASE u.usr_active 
  WHEN "1" THEN "YES" 
  WHEN "0" THEN "No" 
  END AS users_active 
FROM users u
    LEFT JOIN
choicelist c1 ON u.usr_country = c1.chl_value
    AND c1.chl_element = 'country'
    LEFT JOIN
choicelist c2 ON u.usr_state = c2.chl_value
    AND c2.chl_element = 'state'
    LEFT JOIN
choicelist c3 ON u.usr_city = c3.chl_value
    AND c3.chl_element = 'city'
Join entity ent
On SOME CRITERION REQUIRED HERE
Join entity_locations el
 On u.usr_entity_location_id = el.id
Join entity_departments ed
 on u.usr_entity_department_id = ed.id

0

Strawberry 26 नवम्बर 2015, 08:23

Answer 2

कोशिश करें कि यह किसी भी मदद का हो

SELECT 
    users . *,
    el.elo_name,
    ed.ede_name,
    ent.ent_name,
    (select chl_label from choicelist where chl_value = users.usr_country and chl_element = 'country') as country,
    (select chl_label from choicelist where chl_value = users.usr_country and chl_element = 'state') AS state,
    (select chl_label from choicelist where chl_value = users.usr_country and chl_element = 'city') AS city
FROM
   ( SELECT  *, 
       CASE users.usr_active WHEN "1" THEN "YES" WHEN "0" THEN "No" END AS users_active FROM users ) as  users,
    entity ent,
    entity_locations el,
    entity_departments ed
WHERE
    users.usr_entity_location_id = el.id
        and users.usr_entity_department_id = ed.id

0

PK20 26 नवम्बर 2015, 07:59

2 जवाब

संबंधित सवाल

नए सवाल

mysql