April 18, 2003 at 6:31 pm
This is my first experiment with subqueries and I seem to be having trouble wrapping my brain around the logic...
My problem is that I have a select that returns a bunch of Columns' values (including ColumnA) where ColumnB has a value = 0. However, I also need the value for just ColumnA where ColumnB has a value <> 0.
I was thinking that a subquery is the way to go here, but I must be missing something basic in structuring the WHERE clause because all I ever get is errors. 🙂
Thanks,
Loyal
April 18, 2003 at 7:24 pm
Can you post what you have so far?
Andy
April 21, 2003 at 11:03 am
Here ya go:
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'SMC_Test')
DROP VIEW SMC_Test
GO
CREATE VIEW SMC_Test
AS
SELECT
dbo.TableA.SiteID,
dbo.TableA.DateTime,
SUM(dbo.TableB.Cash) AS '4TotalCash',
--Start Subquery
(SELECT
dbo.TableB.Cash AS '4aTotalCash'
FROM
dbo.TableA LEFT OUTER JOIN
dbo.TableB ON dbo.TableA.Site = dbo.TableB.Site AND dbo.TableA.DateTime = dbo.TableB.Date
WHERE dbo.TableA.Site = dbo.TableB.Site AND dbo.TableA.DateTime = dbo.TableB.Date AND dbo.TableA.Transaction <> 0)
--End Subquery
FROM
dbo.TableA LEFT OUTER JOIN
dbo.TableB ON dbo.TableA.Site = dbo.TableB.Site AND dbo.TableA.DateTime = dbo.TableB.Date
WHERE dbo.TableA.Transaction = 0
GROUP BY
dbo.TableA.SiteID,
dbo.TableA.DateTime WITH ROLLUP
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
April 22, 2003 at 2:40 am
Try this
SELECT a.SiteID,
a.[DateTime],
b.Cash AS '4aTotalCash',
--Start Subquery
(SELECT SUM(b2.Cash) AS '4TotalCash'
FROM TableA a2 LEFT OUTER JOIN
TableB b2 ON a2.SiteID = b2.SiteID AND a2.[DateTime] = b2.[Date]
WHERE a2.SiteID = a.SiteID AND a2.[DateTime] = a.[DateTime] AND a2.[Transaction] = 0)
--End Subquery
FROM
TableA a LEFT OUTER JOIN
TableB b ON a.SiteID = b.SiteID AND a.[DateTime] = b.[Date]
WHERE a.[Transaction] <> 0
GROUP BY a.SiteID, a.[DateTime],b.Cash WITH ROLLUP
Far away is close at hand in the images of elsewhere.
Anon.
April 22, 2003 at 9:26 am
Ah, thanks.
That example straightened out the logic of aliasing the tables a bit better in my head.
The problem I have now run into is that it wants me to add the column name at the end of the subquery, like this:
SELECT a.SiteID,
a.[DateTime],
b.Cash AS '4aTotalCash',
--Start Subquery
(SELECT SUM(b2.Cash) AS '4TotalCash'
FROM TableA a2 LEFT OUTER JOIN
TableB b2 ON a2.SiteID = b2.SiteID AND a2.[DateTime] = b2.[Date]
WHERE a2.SiteID = a.SiteID AND a2.[DateTime] = a.[DateTime] AND a2.[Transaction] = 0)
AS 'TotalCash'
--End Subquery
FROM
TableA a LEFT OUTER JOIN
TableB b ON a.SiteID = b.SiteID AND a.[DateTime] = b.[Date]
WHERE a.[Transaction] <> 0
GROUP BY a.SiteID, a.[DateTime],b.Cash WITH ROLLUP
I'm not following why the column name is not being used from the sub-query's first line.
April 22, 2003 at 10:02 am
Sorry missed that one. It's the way subqueries work. If you include a subquery as part of the select then the subquery must only return one value (thats why I switched your around) and the result effectively becomes a column and will never have a column heading.
If you have a subquery as part of the from or join then it may return more than one value and you can reference columns within the subquery by name as it is referenced as the equivalent of a resultset.
Hope this helps.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply