Sql Query Help

[mtkr]

@@@ athadu


mama nuv cheppina coalesce ki function create chesi aaa functn ni select lo use chesukunte result ostundhiii........
RES: oke row lo multiple ph nums with comma separated



create function dbo.fn_phone (@det_ph_id int)
returns varchar(50)
as
begin
declare @out varchar(50)
select @out = coalesce(@out + ', ' + ph_num, ph_num)
from tbl_emp_ph
where det_ph_id = @det_ph_id
return @out
end



select distinct A.emp_id, B.det_id, dbo.fn_phone(c.det_ph_id) as ph_num
FROM tbl_employe a inner join tbl_emp_details B
on A.emp_id = B.emp_id
LEFT OUTER JOIN tbl_emp_ph C
on B.det_ph_id = C.det_ph_id



RES:::

emp_id det_id ph_num

1 96 111111, 111122
2 97 444444, 444455

[deals2buy]

COALESCE vaadataniki functions em separate ga create cheyyakkarledu naaku telisi.dont know what made u create that?

[kotha thread]

[quote name='mtkr' timestamp='1352928299' post='1302797085']

n
eeku result ela kaavaaliii???

table C lo multiple ph nums ( for one employee) unte avi result lo oke row lo side by side raavaalaaa
or every ph num individual row ga display avvalaaaa????
[/quote]
Mama nuvvu terrific...sry mama....nenu ippude office nunchi vachanu so cudnt able to reply.....naaku multiple vasthene...progam lo veesiga untundhi mama...so please...alane cheyali..kabatti...[color=#282828][font=helvetica, arial, sans-serif] coalesce fun use cheydam waste....adhi use chesthe...malli nenu Null values kosam vethakali....but...ronnddu untte oka post lo vesey mama...clear ga..intha mandhi vesthunnaru..edhi correct ardham kavadam ledhu....but THANK YOU ALL.....ippudu cheppe anni queries jus nenu mind lo compile cheydame..anthe........ee thread use chesukuntta...naku data loada avagane...ty[/font][/color]

[ChampakDas]

for all those who helped and posted

[vissu]

[quote name='mtkr' timestamp='1352931754' post='1302797454']
@@@ athadu


mama nuv cheppina coalesce ki function create chesi aaa functn ni select lo use chesukunte result ostundhiii........
RES: oke row lo multiple ph nums with comma separated



create function dbo.fn_phone (@det_ph_id int)
returns varchar(50)
as
begin
declare @out varchar(50)
select @out = coalesce(@out + ', ' + ph_num, ph_num)
from tbl_emp_ph
where det_ph_id = @det_ph_id
return @out
end



select distinct A.emp_id, B.det_id, dbo.fn_phone(c.det_ph_id) as ph_num
FROM tbl_employe a inner join tbl_emp_details B
on A.emp_id = B.emp_id
LEFT OUTER JOIN tbl_emp_ph C
on B.det_ph_id = C.det_ph_id



RES:::

emp_id det_id ph_num

1 96 111111, 111122
2 97 444444, 444455
[/quote]

Good good

[_poori]

[quote name='mtkr' timestamp='1352931754' post='1302797454']
@@@ athadu


mama nuv cheppina coalesce ki function create chesi aaa functn ni select lo use chesukunte result ostundhiii........
RES: oke row lo multiple ph nums with comma separated



create function dbo.fn_phone (@det_ph_id int)
returns varchar(50)
as
begin
declare @out varchar(50)
select @out = coalesce(@out + ', ' + ph_num, ph_num)
from tbl_emp_ph
where det_ph_id = @det_ph_id
return @out
end



select distinct A.emp_id, B.det_id, dbo.fn_phone(c.det_ph_id) as ph_num
FROM tbl_employe a inner join tbl_emp_details B
on A.emp_id = B.emp_id
LEFT OUTER JOIN tbl_emp_ph C
on B.det_ph_id = C.det_ph_id



RES:::

emp_id det_id ph_num

1 96 111111, 111122
2 97 444444, 444455
[/quote]
[img]http://www.desigifs.com/sites/default/files/2mplbuu.gif?1336243797[/img]

[kotha thread]

[quote name='mtkr' timestamp='1352928098' post='1302797062'] create table tbl_employe ( emp_id int, emp_name varchar(100) ) create table tbl_emp_details ( det_id int, emp_id int, det_ph_id int, emp_det varchar(100) ) create table tbl_emp_ph ( ph_id int, det_ph_id int, ph_num varchar(100) ) insert into tbl_employe values(1,'raj') insert into tbl_employe values(2,'shyam') insert into tbl_emp_details values (96,1,196,'contract') insert into tbl_emp_details values (97,2,297,'fulltime') insert into tbl_emp_ph values (100,196,'111111') insert into tbl_emp_ph values (101,196,'111122') insert into tbl_emp_ph values (102,297,'444444') insert into tbl_emp_ph values (103,297,'444455') select A.emp_id, A.emp_name, B.det_id, B.det_ph_id, B.emp_det, C.ph_num from tbl_employe A inner join tbl_emp_details B on A.emp_id = B.emp_id inner join tbl_emp_ph C on B.det_ph_id = C.det_ph_id [/quote] [quote name='mtkr' timestamp='1352928144' post='1302797067'] Result::: 1 raj 96 196 contract 111111 1 raj 96 196 contract 111122 2 shyam 97 297 fulltime 444444 2 shyam 97 297 fulltime 444455 [/quote]



perfect mama...idhi saripothundhi.....naaku thelsi....last okka dbt mama....primary key and uniquie combine chesi last table lo pettam kadaa.....so okey query lo ...B lo unna primary key theesukoni...C lo unna combination key tho ela validate chestha...
i mean

B table lo

Bid(PRIMARYKEY) Bname Baddress

1 sdfsd sgdsgsgg
2 sdfsd sgdsgsgg
3 sdfsd sgdsgsgg

C table

CID(UNIQUE KEY) BID(FORIEGNKEY) PH1 EXTENTION
11 1 34141 121
12 1 34141 121
13 2 34141 121
14 3 34141 121


NOTE PRIMARY IN TABLE C IS (CID+BID)

SRY MAMA...KONCHAM CONFUSION GA UNDHI..MALLI ADUGUTUNNA...

[mtkr]

[quote name='SREEE' timestamp='1352946681' post='1302798147']
perfect mama...idhi saripothundhi.....naaku thelsi....last okka dbt mama....primary key and uniquie combine chesi last table lo pettam kadaa.....so okey query lo ...B lo unna primary key theesukoni...C lo unna combination key tho ela validate chestha...
i mean

B table lo

Bid(PRIMARYKEY) Bname Baddress

1 sdfsd sgdsgsgg
2 sdfsd sgdsgsgg
3 sdfsd sgdsgsgg

C table

CID(UNIQUE KEY) BID(FORIEGNKEY) PH1 EXTENTION
11 1 34141 121
12 1 34141 121
13 2 34141 121
14 3 34141 121


NOTE PRIMARY IN TABLE C IS (CID+BID)

SRY MAMA...KONCHAM CONFUSION GA UNDHI..MALLI ADUGUTUNNA...
[/quote]

i guess LEFT function use chesukovochuu....
ippudu cheeerzzzz lo unnaaaa.... mrng try chesi chusthaaaaaaa....

[kotha thread]

[quote name='mtkr' timestamp='1352952413' post='1302798440']
i guess LEFT function use chesukovochuu....
ippudu cheeerzzzz lo unnaaaa.... mrng try chesi chusthaaaaaaa....
[/quote]
kaniyyi repu Office lo unnappudu leptha...ee thread.....

[deals2buy]

[quote name='mtkr' timestamp='1352952413' post='1302798440']
i guess LEFT function use chesukovochuu....
ippudu cheeerzzzz lo unnaaaa.... mrng try chesi chusthaaaaaaa....
[/quote]
roju mandu endi vayya?

[mtkr]

[quote name='deals2buy' timestamp='1352933302' post='1302797537']
COALESCE vaadataniki functions em separate ga create cheyyakkarledu naaku telisi.dont know what made u create that?
[/quote]
ante variable declare chesi coalesce vaadithe error ostundhi kada so crtd fnctn....
approach tappu aithe manninchuuu... asalu method e tappu aithe kshaminchuuuu...

[mtkr]

[quote name='SREEE' timestamp='1352952484' post='1302798449']
kaniyyi repu Office lo unnappudu leptha...ee thread.....
[/quote]

ok mama....

[deals2buy]

[quote name='mtkr' timestamp='1352952576' post='1302798456']
ante variable declare chesi coalesce vaadithe error ostundhi kada so crtd fnctn....
approach tappu aithe manninchuuu... asalu method e tappu aithe kshaminchuuuu...
[/quote]
neeku mandhu ekkuvaindi

[mtkr]

[quote name='deals2buy' timestamp='1352952638' post='1302798459']
neeku mandhu ekkuvaindi
[/quote]
inka ctrl lone unnna maayaa... 3rd de...

[deals2buy]

[quote name='mtkr' timestamp='1352952826' post='1302798474']
inka ctrl lone unnna maayaa... 3rd de...
[/quote]
beer or hard?