(RESOLVED) Nested SELECT statements

[-=XQ=-]

Does anybody know if it is possible to share data between nested SQL statements. I.E.

SELECT SomeFields...,(SELECT SUM(OtherField) FROM table WHERE OtherFieldForiegnKey=SomeFieldPrimaryKey) FROM OTHERTable

So from the above OtherFieldID would match the ID of the record from the first table. I don't seem to be able to figure it out. Can anybody help?

Thanks,

-=XQ=-

[kleinma]

Originally posted by -=XQ=-
Does anybody know if it is possible to share data between nested SQL statements. I.E.

SELECT SomeFields...,(SELECT SUM(OtherField) FROM table WHERE OtherFieldForiegnKey=SomeFieldPrimaryKey) FROM OTHERTable

So from the above OtherFieldID would match the ID of the record from the first table. I don't seem to be able to figure it out. Can anybody help?

Thanks,

-=XQ=-

you want to use a join statement for this

something like

Select table1.field1, table1.field2, table2.field1 from table1 inner join table2 on table1.UniqueID = table2.UniqueID

not sure if that syntax is 100% but its close...

what you shoudl do is build the query in ACCESS (thats what you are using right?) and then look at the code that it generates... you could then use that sql syntax in your VB app

[-=XQ=-]

I cannot do it this way due to the way the tables hold the information. Inner joins will duplicate totals values from the primary table. I probably should have wrote the example nearer to what it should be which is:

SELECT SUM(tbl1.fld1) AS SomeTotal, (SELECT COUNT(tbl2.fld2) 
FROM tbl2 WHERE tbl2.ForiegnKey=tbl1.PrimaryKey) AS SomeOtherTotal FROM tbl1 
WHERE .... GROUP BY tbl1.OtherField

The problem only occurs when I add the GROUP BY. It starts complaining that the tbl1.PrimaryKey is not an aggregate function. Even if I enclose it in MAX() it complains that it should not be used.

Any ideas?

[Ecniv]

You can only group by a field if you return it..?

At least I think so - try adding that field to the Select fields bit and see if it runs.


Vince
(getting brain dead )

[swatty]

You have to add each field you select in the group by clause

[-=XQ=-]

Thanks for all your help i think I have sorted it