advanced code snippet search

Posted By

karthi on 02/22/12


Tagged

images player - agent


Versions (?)

(Player - Player Agent) Images

 / Published in: MySQL
 

(Player - Player Agent) Images

Expand | Embed | Plain Text 
  1. SELECT asub.*,pr.type,
  2. CASE 
  3. WHEN pr.type='player' THEN (SELECT image FROM football_player_list WHERE user_id = pr.id)
  4. WHEN pr.type='player_agent' THEN (SELECT image FROM player_agent_list WHERE user_id = pr.id)
  5. ELSE '-' END as user_images
  6. FROM `agent_subscribe` as asub INNER JOIN profile as pr ON asub.member_id = pr.id WHERE player_id = 1

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: karthi on February 28, 2012

SELECT compownerid,userimages, IF(compownerid !=0,(SELECT compname from playeragentlist WHERE userid = compownerid),compname) as CompanyName, IF(compCountry !=0,(SELECT countryname from country WHERE countryid = compCountry), (SELECT countryname from country WHERE countryid = (SELECT Country from playeragentlist WHERE userid = compownerid)) ) as CountryName, IF(compState !=0,(SELECT statename from state WHERE stateid = compState), (SELECT statename from state WHERE stateid = (SELECT State from playeragentlist WHERE userid = compownerid)) ) as StateName, IF(compCity !=0,(SELECT cityname from city WHERE cityid = compCity), (SELECT cityname from city WHERE cityid = (SELECT City from playeragentlist WHERE userid = compownerid)) )
as CityName FROM ( SELECT asub.*,pr.type, CASE WHEN pr.type='player' THEN (SELECT image FROM footballplayerlist WHERE userid = pr.id) WHEN pr.type='playeragent' THEN (SELECT image FROM playeragentlist WHERE userid = pr.id) ELSE '-' END as userimages, CASE WHEN pr.type='playeragent' THEN (SELECT COALESCE(ownerid,(SELECT ownerid FROM playeragentlist WHERE userid = ownerid)) from playeragentlist WHERE userid = pr.id )
ELSE '-' END as compownerid, CASE WHEN pr.type='playeragent' THEN (SELECT COALESCE(Country,(SELECT Country FROM playeragentlist WHERE userid = ownerid)) from playeragentlist WHERE userid = pr.id ) ELSE '-' END as compCountry, CASE WHEN pr.type='playeragent' THEN (SELECT COALESCE(State,(SELECT State FROM playeragentlist WHERE userid = ownerid)) from playeragentlist WHERE userid = pr.id )
ELSE '-' END as compState, CASE WHEN pr.type='playeragent' THEN (SELECT COALESCE(City,(SELECT City FROM playeragentlist WHERE userid = ownerid)) from playeragentlist WHERE userid = pr.id )
ELSE '-' END as compCity, CASE WHEN pr.type='playeragent' THEN (SELECT compname from playeragentlist WHERE userid = pr.id ) ELSE '-' END as compname

FROM `agent_subscribe` as asub INNER JOIN profile as pr ON asub.member_id = pr.id  WHERE player_id = 1
)

as derived_res

You need to login to post a comment.