Nested Selection with group
-
Hi there (again)
I need to understand how to make a nested selection with a group, and "deselect" records from the first table?
This one gives a syntax error with group,
SELECT dbo.TableA.Municipality_number, SUM(dbo.TableC.Numeric_value_1), SUM(dbo.TableC.Numeric_value_2), SUM(dbo.TableC.Numeric_value_3),
SUM(dbo.TableC.Numeric_value_4)
FROM dbo.TableA INNER JOIN
dbo.TableB ON dbo.TableA.Municipality_number = dbo.TableB.Municipality_number AND dbo.TableA.Building_number = dbo.TableB.Building_number INNER JOIN
dbo.TableC ON dbo.TableB.CRUD_ID = dbo.TableC.CRUD_ID
WHERE (dbo.TableB.ValueA = '1') AND (NOT (dbo.TableB.ValueB '60')) AND
(SELECT TOP 100 PERCENT Municipality_number, Building_number, COUNT(*) AS Number_of_buildings
FROM dbo.TableA
WHERE (ValueC = 1) OR
(ValueC = 2) OR
(ValueC = 3)
GROUP BY Municipality_number, Building_number
HAVING (COUNT(*) > 1))
GROUP BY dbo.TableA.Municipality_number
What I have is two tables, TableA and TableB, TableB is a small part of TableA.
I want TableA minus the records in TableB.
Now you probably think "Why the hek not make a select on TableA". I can't do it without a group and a nested selection. I am new to this, and I have allready tried reading some of the discussions about nested selection with group - it's too difficult for me (yet).
Please help my - but keep it simple
Regards Joejoe
-
OK I didn't read thru your syntax, but if you want records in table A that are not in table B then outer join is how I always do this...
SELECT a.*
FROM tablea a
LEFT OUTER JOIN tableb b ON a.key = b.key
WHERE b.key IS NULL
works for updates also
-
Thanks alot john!
It worked perfect.
(I had to make a new view and make a left outer join with that where key IS NULL.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply