Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-03-23 : 15:48:05
|
| Hi,Following are the sample tables and data:create table trans (id_trans int, template int, instance int)create table base (id_base int, n_base int,name varchar(100))create table des (id_des int, language varchar(2))insert into trans values(1,10,20)insert into trans values(2,11,21)insert into base values(10,100,'temp1')insert into base values(20,200,'inst1')insert into base values(11,110,'temp2')insert into base values(21,210,'inst2')insert into des values(100,'us')insert into des values(100,'GR')insert into des values(200,'us')insert into des values(200,'FR')insert into des values(200,'GR')insert into des values(110,'us')insert into des values(210,'us')insert into des values(110,'FR')--drop table trans--drop table base--drop table desWe need to show only 1 row for each language per transaction so in our case trans 1 have 3 languages so we want to show only 3 rows…Result should look like:Id_trans template instance template_name instance_name language1 10 20 temp1 Inst1 us1 10 20 temp1 inst1 gr1 null 20 null inst1 fr2 11 21 temp2 inst2 us2 11 null temp2 null fr How to modify the following query to get the required result:select id_trans,template,instance,isnull(des1.language,des.language),base.name,base1.name from transinner join base on trans.template = base.id_baseinner join des on base.n_base = des.id_desinner join base base1 on trans.instance = base1.id_baseinner join des des1 on base1.n_base = des1.id_desThanks--Harvinder |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-23 : 20:10:32
|
| Did you ever figure this one out?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
|
|
Query problem