Database

21-Oct-09 6:02

Just to get rid of another useless, no-brainer of a post... here you go:

// Perfectly workable solution removed, as the OP is an ungrateful gimp.

Gives the exact output you needed, with the test code you provided. No warranties or guarantees. Change the names to your actual table names yourself.

edit: After reading the above comments, this is the very last time you'll get help from me.

edit2: And after reading the below comment, you'll not even get the benefit of this answer.

modified on Wednesday, October 21, 2009 2:21 PM

khan201021-Oct-09 7:20

21-Oct-09 7:20

see ..!!!
Now u are angry..You have to feel this. You have to think twise to tell some one like this
" If you're incapable, I suggest you go back to your boss, hand him your keycard and tell him you are not qualified to be a developer. " OK.

1 - and by the way this is wrong answer,
what kind of developer you are,
each time how can user punch single product name to Check stock, There is million Productname in database.

2 - I just Give you crystal example from my first post. what is in database and what type of output i want, But you didnt understand (No Brain).

Lol...I know what type of developer u are i dont need your wrong answer anymore just keep it with you.

I will post correct one 2morrow.
''''''''''''

dan!sh 21-Oct-09 7:46

dan!sh

21-Oct-09 7:46

You do not deserve to be helped. Seriously. A person who wrote code that YOU were supposed to write and instead of being grateful, you are abusing the guy.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

J4amieC21-Oct-09 8:20

21-Oct-09 8:20

You, sir, are an anus.

Your data model makes this query neigh on impossible to write any other way than that posted above. It is quite simple to adjust what I have given you to pull out the data for one particular product, but i dont remember you requesting this at all.

I seriously suggest you seek employment in a less brain-intensive industry. Get used to this phrase "Big mac and fries please".

khan201028-Oct-09 21:38

28-Oct-09 21:38

J4amieC

This is the Correct one Still I am working On it to get some more Correct result, You Brain Less Idiot Look and think your self how u can say you are a developer, and by the way what are you doing here" "U think you can helping people" C ur self in the mirro U Idiot. and learn the manner how to speak with other.
'''''''''''''''''''''''''''''''''''''''''''
Just to save time i asked question here.
''''''''''''''''''''''''''''''''''''''''''

SELECT     Item_Name,<br />
<br />
                          (SELECT     SUM(Piece_No) AS Quantity FROM     Purchase_Detail  WHERE      (Product.Item_Name = Item)) AS QuantityB,<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity  FROM     Detail_Description    WHERE      (Product.Item_Name = Item)) AS QuantityC,<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity  FROM     Refund_Detail  WHERE      (Product.Item_Name = Item_Name)) AS QuantityD,<br />
<br />
                          (SELECT     SUM(Piece_No) AS Quantity FROM     Purchase_Detail   AS tblB_1   WHERE      (Product.Item_Name = Item)) -<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity FROM      Detail_Description AS tblC_1  WHERE      (Product.Item_Name = Item)) -<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity FROM      Refund_Detail  AS tblD_1 WHERE      (Product.Item_Name = Item_Name)) AS Total<br />
<br />
FROM   Product GROUP BY Item_Name

J4amieC29-Oct-09 4:15

29-Oct-09 4:15

Voted & reported as Abuse

khan201028-Oct-09 23:38

28-Oct-09 23:38

J4amieC Moron

Rob Graham29-Oct-09 4:37

Rob Graham

29-Oct-09 4:37

You sir, are a first class jackass.

Niladri_Biswas24-Oct-09 20:28

Niladri_Biswas

24-Oct-09 20:28

Try this

declare @tbl1 table(productname varchar(20))
insert into @tbl1 values('abc_123a')
insert into @tbl1 values('cfl_523j')
insert into @tbl1 values('jpm_111k')

declare @tbl2 table(productname varchar(20),purqty int)
insert into @tbl2 values('abc_123a',50)
insert into @tbl2 values('abc_123a',30)
insert into @tbl2 values('cfl_523j',100)
insert into @tbl2 values('cfl_523j',20)
insert into @tbl2 values('cfl_523j',10)
insert into @tbl2 values('jpm_111k',105)
insert into @tbl2 values('jpm_111k',10)
insert into @tbl2 values('jpm_111k',25)

declare @tbl3 table(productname varchar(20),soldqty int)
insert into @tbl3 values('abc_123a',10)
insert into @tbl3 values('abc_123a',05)
insert into @tbl3 values('cfl_523j',20)
insert into @tbl3 values('cfl_523j',05)
insert into @tbl3 values('cfl_523j',10)
insert into @tbl3 values('jpm_111k',15)
insert into @tbl3 values('jpm_111k',05)
insert into @tbl3 values('jpm_111k',10)

declare @tbl4 table(productname varchar(20),refqty int)
insert into @tbl4 values('abc_123a',2)
insert into @tbl4 values('abc_123a',6)
insert into @tbl4 values('cfl_523j',4)
insert into @tbl4 values('cfl_523j',3)
insert into @tbl4 values('cfl_523j',9)
insert into @tbl4 values('jpm_111k',3)
insert into @tbl4 values('jpm_111k',8)
insert into @tbl4 values('jpm_111k',2)

;with cte1 as(
select productname,sum(purqty)as purqty  from @tbl2 
group by productname)
,cte2 as(
select productname,sum(soldqty)as soldqty  from @tbl3 
group by productname)
,cte3 as(
select productname,sum(refqty)as refqty  from @tbl4 
group by productname)

select c1.productname,c1.purqty,c2.soldqty,c3.refqty,c1.purqty- c2.soldqty-c3.refqty as STOCK
from cte1 c1 inner join cte2 c2 on c1.productname = c2.productname 
inner join cte3 c3
on c1.productname = c3.productname

Let me know in case of any concern
Smile | :)

Niladri Biswas

khan201028-Oct-09 21:42

Joining tables and get column with comma seperated values out of the join result

28-Oct-09 21:42

Thanks Nildari Biswas for Reply
I appreciate for your answer. Thank you very much.
But here i solved my problem please check.

SELECT     Item_Name,<br />
<br />
                          (SELECT     SUM(Piece_No) AS Quantity FROM     Purchase_Detail  WHERE      (Product.Item_Name = Item)) AS QuantityB,<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity  FROM     Detail_Description    WHERE      (Product.Item_Name = Item)) AS QuantityC,<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity  FROM     Refund_Detail  WHERE      (Product.Item_Name = Item_Name)) AS QuantityD,<br />
<br />
                          (SELECT     SUM(Piece_No) AS Quantity FROM     Purchase_Detail   AS tblB_1   WHERE      (Product.Item_Name = Item)) -<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity FROM      Detail_Description AS tblC_1  WHERE      (Product.Item_Name = Item)) -<br />
<br />
                          (SELECT     SUM(Itm_Qty) AS Quantity FROM      Refund_Detail  AS tblD_1 WHERE      (Product.Item_Name = Item_Name)) AS Total<br />
<br />
FROM   Product GROUP BY Item_Name<br />
<br />
 <br />

again Thanks for reply.

nainakarri21-Oct-09 0:21

nainakarri

21-Oct-09 0:21

Hi
I have 2 tables table1 and table2

table1 has primary key column a which is joined with table2 column foriegnkey column b. on joining the two tables i get the result as below

table1.a table2.b table2.c
1 1 x
1 1 y
1 1 z

but i want the output as below

table1.a table2.b table2.c
1 1 x,y,z

can anyone help me how i can do this?

want to avoid duplicates

Regards
Usha

Naina

Re: Joining tables and get column with comma seperated values out of the join result [modified]

J4amieC21-Oct-09 1:06

Re: Joining tables and get column with comma seperated values out of the join result

21-Oct-09 1:06

Although probably possible, there is no easy way to do what you want - and it runs counter to the way database queries should work.

You'd be far better off pulling your data out in the original way you posted and handling any weird aggregation of strings in code.

FWIW here's your test code

create table #myTable (a INT, b INT, c VARCHAR(10))

INSERT INTO #myTable VALUES(1,1,'x')
INSERT INTO #myTable VALUES(1,1,'y')
INSERT INTO #myTable VALUES(1,1,'z')

INSERT INTO #myTable VALUES(2,2,'j')
INSERT INTO #myTable VALUES(2,2,'k')
INSERT INTO #myTable VALUES(2,2,'l')


declare @list VARCHAR(MAX), @a INT, @b INT
SELECT @a=1,@b=1

select @list = coalesce(@list + ',' + c ,c) from #myTable where a=@a AND b=@b
select a,b,@list as c
FROM #myTable
WHERE a=@a AND b=@b
group by a,b

drop table #myTable

i.j.russell21-Oct-09 1:51

i.j.russell

21-Oct-09 1:51

If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data.

Re: Joining tables and get column with comma seperated values out of the join result

Mycroft Holmes21-Oct-09 13:10

Mycroft Holmes

21-Oct-09 13:10

i.j.russell wrote:
If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data

Do you have a link or an article for this or should I chase through BOL

Never underestimate the power of human stupidity
RAH

Re: Joining tables and get column with comma seperated values out of the join result

i.j.russell21-Oct-09 21:41

i.j.russell

21-Oct-09 21:41

http://www.mahipalreddy.com/blog/?p=79[^]

Re: Joining tables and get column with comma seperated values out of the join result

Mycroft Holmes21-Oct-09 22:08

Mycroft Holmes

21-Oct-09 22:08

Thanks i.j

Never underestimate the power of human stupidity
RAH

Re: Joining tables and get column with comma seperated values out of the join result

Niladri_Biswas24-Oct-09 23:08

Niladri_Biswas

24-Oct-09 23:08

Try this

declare @pk_table table (id int)
insert into @pk_table select 1 union all select 2 union all select 3
union all select 4
declare @fk_table table (id int, string varchar(2))
insert into @fk_table
select 1,'x' union all select 1,'y' union all select 1,'z' union all
select 2,'a' union all select 2,'b' union all select 2,'c' union all
select 3,'d' union all select 3,'e' union all select 3,'f' union all
select 4,'g' union all select 4,'h' union all select 4,'i'

select cast(id as varchar(20)) + space(2) + cast(id as varchar(20)) + space(2) +
(select string + ','
	from @fk_table fkt
	where fkt.id = pkt.id
	FOR xml path('')) as OutputCol
	from @pk_table pkt
	group by pkt.id

Niladri Biswas

Re: Joining tables and get column with comma seperated values out of the join result

nainakarri25-Oct-09 19:12

nainakarri

25-Oct-09 19:12

Hi thanks for all the replies. I finally approached by calling a function from select query.

Select col1, col2, func() as col3,col4 from table1 a inner join table2 b
on a.col1 = b.col2

Regards
Naina

Naina

Re: Joining tables and get column with comma seperated values out of the join result

Niladri_Biswas26-Oct-09 15:54

Niladri_Biswas

26-Oct-09 15:54

Hello,
I suggestion.. if possible try to avoid using function as it causes performance bottleneck.
Smile | :)

Niladri Biswas

An Extraordinarily Dumb Question

Roger Wright20-Oct-09 20:15

Roger Wright

20-Oct-09 20:15

But one that has me baffled. Yes, I've Googled, I've checked the online help, but I can't find an answer to this stupid question. Be kind - I'm old...

I have SQL Server Express 2008 installed on my server and, for various reasons, I want to develop a db app on a WinXP client machine using the server instance. How do I connect to it? The local copy of the SQL Server Management tool only "sees" the local instance of SQL Server Ex 2005 on the XP client. I know a better solution would be to install the more recent version on the client, but it's a problem to do so. The client has a ton of RAM, but almost no drive space and no way to add more, while the server has little RAM and over a TB of drive space available. I really want to work from the client, but host everything on the server.

Any tips? Confused | :confused:

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

SeMartens20-Oct-09 20:41

SeMartens

20-Oct-09 20:41

Hi,

I guess the problem is not that you are using an SQL Server 2005 Management Studio. To "see" the server on your client machine you need to start the SQL Server-Browser service on your server machine. This service propagates the name of the local sql server to remote machines.

Let me know if this helps you.

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Roger Wright21-Oct-09 3:12

Roger Wright

21-Oct-09 3:12

Thanks, Sebastion - it worked, at least partially. I can "see" the database engine on the server, but I can't connect. I'll need to configure it to accept remote connections first. Back to the books to find out how...

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

i.j.russell21-Oct-09 3:09

i.j.russell

21-Oct-09 3:09

By default, Sql Server Express is local only. You need to allow remote connections through the Surface Area Configuration tool accessed via your Start menu. I can't remeber if it is installed by default or if you need to install Management Studio Express to get access to it.

Roger Wright25-Oct-09 21:40

Roger Wright

25-Oct-09 21:40

Thanks!

The 2008 version doesn't have a Surface Area Configuration tool; it's built in to the Management Studio. All protocols are enabled ( my doing, not the default ), but when I try to connect from the client I receive error 10061 - the target server actively refused the connection. I'm logged in as an Admin on both machines, and the SQL Server Browser service is running on the server. Perhaps I have to install the 2008 version of the management studio on the client... Any thoughts on that?

"A Journey of a Thousand Rest Stops Begins with a Single Movement"