Sql Query Help

[mtkr]

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

[mtkr]

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

[mtkr]

[quote name='SREEE' timestamp='1352927628' post='1302797024']
[size=6]select A.id, B.*,COALESCE(C.*) from tbl_a A RIGHTOUTERJOIN tbl_b B [/size]
[size=6]on A.emp_id = B.emp_id[/size]
[size=6]RIGHTOUTERJOIN tbl_c C[/size]
[size=6]on B.primary_key = C.foreign_key ;[/size]


[size=6]ila anukuntunna mama....wrk avudha.... ..[/size]


[size=6]SO NAAKU B table and C table data vasthundhi.....?[/size]
[/quote]

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????

[athadu]

dude.. last page lo query undi.. niku exact ga kavalsinatu vastundi.. try chei

[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]

[deals2buy]

[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]

[athadu]

here when you read the data it comes out in a single row.. the data format doesnt change in the table..

[quote name='deals2buy' timestamp='1352926630' post='1302796855']



data okadu esudu endi mama? neeku nuvvu load chesukovachu ga?


paina manodu cheppinattu COALESCE statement will combine everything and will get it in once column
[/quote]

[mtkr]

[quote name='athadu' timestamp='1352928492' post='1302797106']
dude.. last page lo query undi.. niku exact ga kavalsinatu vastundi.. try chei
[/quote]
ya aaaa query ni try chesa... nen vesina tables tho...
but its throwing me error

:::A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.::::

2 my knowledge ala ph nums ni oke column(with comma separation) or adjacent columns( one ph num in each column) lo chupaali ante partitions use cheyyaaliii....

[mtkr]

mama let us know ur exact(desired) output format(or result) so tht we can throw in more inputs

[mtkr]

mama frstly and mainly nuvvu aaskedhi PL-SQL aaaaa or T-SQL aaaa???

[athadu]

ni query post chei mama.. chusi cheptanu.. the one you used a left outer join query with coalesce

[quote name='mtkr' timestamp='1352928865' post='1302797164']
ya aaaa query ni try chesa... nen vesina tables tho...
but its throwing me error

:::A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.::::

2 my knowledge ala ph nums ni oke column(with comma separation) or adjacent columns( one ph num in each column) lo chupaali ante partitions use cheyyaaliii....
[/quote]

[mtkr]

neek res(o/p) osthe query ikkada paste cheiii...

will be bck soon

[deals2buy]

[quote name='athadu' timestamp='1352928673' post='1302797132']
here when you read the data it comes out in a single row.. the data format doesnt change in the table..
[/quote]

ok ok.. thanks

[mtkr]

[quote name='athadu' timestamp='1352929013' post='1302797198']
ni query post chei mama.. chusi cheptanu.. the one you used a left outer join query with coalesce
[/quote]
idi nen use chesina query


declare @empphonenumber varchar(100)

select A.emp_id, B.det_id, @empphonenumber = coalesce(@empphonenumber +',','')+ C.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

and tables paina mention chesinave

[athadu]

try this one


begin

declare @v_phnumbers VARCHAR(100)

select A.emp_id, A.emp_name, B.det_id, B.det_ph_id, B.emp_det, @v_phnumber =COALESCE( @v_phnumber + ',','')+ CAST( C.ph_num AS VARCHAR(15))
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


end

[quote name='athadu' timestamp='1352929013' post='1302797198']
ni query post chei mama.. chusi cheptanu.. the one you used a left outer join query with coalesce
[/quote]

[mtkr]

[quote name='athadu' timestamp='1352929192' post='1302797228']
try this one


begin

declare @v_phnumbers VARCHAR(100)

select A.emp_id, A.emp_name, B.det_id, B.det_ph_id, B.emp_det, @v_phnumber =COALESCE( @v_phnumber + ',','')+ CAST( C.ph_num AS VARCHAR(15))
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


end
[/quote]



throwing same error...

:::A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.:::

one frst thng 2 be changed....
u r declaring variable as phnumberS and using Phnumber