Joining 3 tables

Results 1 to 2 of 2

Thread: Joining 3 tables

  1. #1
    Join Date
    Dec 1969

    Default Joining 3 tables

    Here is how I designed the MS SQL2000 database..<BR><BR>From the Project_master I removed Country_id and added into a new table Project_Country(Project_id,Country_id)<BR><BR>Now I am struck up with someother thing...How can u write a query for this..<BR>I have 3 tables is the design<BR><BR>COUNTRY_MASTER(COUNTRY_ID,COUNTRY_NA ME)<BR>PROJECT_MASTER(PROJECT_ID,PROJECT_TITLE,CLI ENT_TYPE)<BR>PROJECT_COUNTRY(PROJECT_ID,COUNTRY_ID )<BR><BR>Here are the sample records<BR><BR>Country_master<BR>------------------<BR>country_id Country_name<BR>---------------------------------<BR>1 India<BR>2 Taiwan<BR>3 Shanghai<BR>4 Singapore<BR><BR>Project_Master<BR>--------------------<BR>PROJECT_ID PROJECT_TITLE CLIENT_TYPE<BR>-------------------------------------------------------<BR>1 XP MICROSOFT<BR>2 PENTIUM 5 INTEL<BR>3 TEST INDIAINFO<BR><BR>PROJECT_COUNTRY<BR>---------------------<BR>PROJECT_ID COUNTRY_ID<BR>---------------------------------------------<BR>1 1<BR>1 3<BR>1 4<BR>2 1<BR>2 3<BR>3 2<BR><BR><BR>Now tell me how to write a query which will fetch records like this<BR>If in project_country if one project belongs to more than 1 country it should display Global and it should display only once..distinct(project_id) if it belogs to only one country like project_id 3 it should dislay as follows<BR><BR><BR>Project_id Country_name<BR>3 Shanghai <BR>1 Global<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Joining 3 tables

    select project_id, <BR>case when count(*) &#062; 1 then &#039;Global&#039; <BR> else max(country_name) end as Country_name<BR>from project_country pc join country_master cm<BR> on pc.country_id = cm.country_id<BR> group by country_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts