July 21, 2007 at 10:13 am
Hi all,
I am trying to find if i can do the following:
Table structure
table 1:
id int
table 2:
id int
lineitem int
price money
select * from
from table 1 t1 inner join table 2 t2
on t1.id = t2.id
where t2.lineitem = 1
here is where i am stuck:
In addition to the t2.lineitem = 1 i need to get
sum for all matched id's where line item > 1
It is possible to accomplish this without selecting from the table t2 2 times?
July 21, 2007 at 12:46 pm
You need to describe your problem a little better, .e.g. show us what your data looks like. Have you actually tried your query on a SQL Server database? As it stands, it will fail because table 1 needs to be written as
.
The other thing that worries me is that single table containing a single numeric column. As it stands, I always keep a table like that around that goes from 1 to whatever. But your problem does not seem to warrant the use of such a table. So what does your single column table represent?
July 21, 2007 at 1:10 pm
This seems like an Orders / OrdersDetails schema.
Based on that assumption you need to do something like this :
SELECT O.ID, ISNULL(Dtd.SPrice, 0) AS SPrice FROM dbo.Orders O LEFT OUTER JOIN (
SELECT Id, SUM(Price) AS SPrice FROM dbo.Details GROUP BY id WHERE LineNumber > 1) dtD
ON M.ID = Dtd.id ORDER BY Whatever
July 24, 2007 at 2:00 am
I hope this will solve you problem.
SELECT
T1
.ID,
SUM(T1.ID)
FROM
TABLE1 AS T1
JOIN
TABLE2 AS T2
ON
T1.ID = T2.ID
WHERE
T2.LineItem > 1
GROUP
BY T1.ID
Please clarify If I'm wrong.
Regards,
-Kiran
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply