StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Simplest method is this:</p> <pre><code>select *, (select count(*) from tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt from tbl1 t1 </code></pre> <p>here is a workable version (using table variables):</p> <pre><code>declare @tbl1 table ( tbl1ID int, prop1 varchar(1), prop2 varchar(2) ) declare @tbl2 table ( tbl2ID int, tbl1ID int ) select *, (select count(*) from @tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt from @tbl1 t1 </code></pre> <p>Obviously this is just a raw example - standard rules apply like don't select *, etc ...</p> <hr> <p><strong>UPDATE from Aug 21 '08 at 21:27:</strong><br> @AlexCuse - Yes, totally agree on the performance.</p> <p>I started to write it with the outer join, but then saw in his sample output the count and thought that was what he wanted, and the count would not return correctly if the tables are outer joined. Not to mention that joins can cause your records to be multiplied (1 entry from tbl1 that matches 2 entries in tbl2 = 2 returns) which can be unintended.</p> <p>So I guess it really boils down to the specifics on what your query needs to return.</p> <hr> <p><strong>UPDATE from Aug 21 '08 at 22:07:</strong><br> To answer the other parts of your question - is a VB function the way to go? No. Absolutely not. Not for something this simple.</p> <p>Functions are very bad on performance, each row in the return set executes the function.</p> <p>If you want to "compartmentalize" the different parts of the query you have to approach it more like a stored procedure. Build a temp table, do part of the query and insert the results into the table, then do any further queries you need and update the original temp table (or insert into more temp tables).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload