| Author |
Topic |
|
lewiska1
Starting Member
12 Posts |
 Posted - 2004-06-11 : 21:00:02
|
| Hi,I have 2 tables, one with fields ID,Date_Added,Narrative and the other with ID,Narrative,Date_Added,Narrative2.Narrative field on table2 contains ID values from the table1.When I am selecting records from table1, for each record I select, I would also like my results to include the number of related records in table2.How would I achieve this?Thanks for your help. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-11 : 23:41:30
|
| Something like this:Select *, related = (Select count(*) from table2 where Narrative like convert(nvarchar,table1.id)) From table1better info = better answer |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-06-12 : 14:40:52
|
Here's another query that should give the same result, but it may be faster.SELECT T1.ID, T1.Date_Added, T1.Narrative, SUM(CASE WHEN T2.Narrative IS NOT NULL THEN 1 ELSE 0 END) As Total FROM table1 T1 LEFT OUTER JOIN table2 T2 ON T2.Narrative = T1.ID WHERE <any condition you have on Table1> GROUP BY T1.ID, T1.Date_Added, T1.Narrative ORDER BY T1.Date_Added, T1.Narrative |
|
|
|
lewiska1
Starting Member
12 Posts |
Posted - 2004-06-14 : 10:14:43
|
| Thanks guys, I applied SeventhKnights solution and it works perfectly. The DB is small at the moment, so when it gets large I will apply SamC's query and compare the performance. |
|
|
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
Posted - 2004-06-14 : 11:33:25
|
| While modesty is a good quality, it's not that important when it comes to writing good SQL. Solution 2 (SamC) definitely works much faster and is the one to choose to begin with.helena |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-14 : 13:33:27
|
| Maybe I mis-read the question, but it seemed to me that one of the conditions was that t2.Narrative contains ID values from table1. I took that to mean: Narrative may include 1 or more of the IDs. If that is the case then SamC's solution wouldn't work as he is joining T1.Id and T2.narrative.On the other hand, if it is only one value, SamC's would definitely be faster. I would really have to question the table structure if you were only keeping on Id in the narrative.Corey |
|
|
|
lewiska1
Starting Member
12 Posts |
Posted - 2004-06-15 : 12:13:31
|
| SeventhKnight, there's only one id in the Narrative column of table2 and that field is numeric. I simply changed your LIKE CONVERT portion to T1.ID = T2.Narrative. I am a bit ignorant and thought that was good design. How could I have done it better?(It's supposed to be similar to the blog concept, where somebody makes an entry [T1], then others comment [T2]. ) |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-15 : 12:35:45
|
Ok... couple of things that I might have done differently.1) It is confusing to name a column narrative and then put a single numeric value normally known as id in it.Maybe use parentId since it is a blog concept.2) I would have to agree with the others that though my original suggestion may have been easier to read it was directed to the multi-id field. SamC's solution will definitely be faster with a numeric field.3) For a blog concept why would you need two tables? You only need to identify the topic and replies. The original entry could be treated as a reply narrative and the topic would be the original subject. Table might look likeentryId int,entryDate datetime,topicId int,isTopic bit,Comments nvarchar(1000),UserId intthen examples would be:242|6/15/2004 12:13:31|34|1|This is a topic|1243|6/15/2004 12:13:32|34|0|This is the original text|1244|6/15/2004 15:48:02|34|0|This is a second reply|2My biggest issue would have to be your naming... hope that helps  Corey |
|
|
|
lewiska1
Starting Member
12 Posts |
Posted - 2004-06-15 : 13:53:21
|
| Thanks Seventhnight,The reason I separated them was because the relationships were actually 3 deep (not 2) and I thought it was clearer, but your example works so much better. Your topicID could really just be parentID and work so much better than what I've done. I'll redo the table structure and apply SamC's solution.Thanks guys! |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-15 : 14:09:42
|
| For a full tree structure there is an article somewhere around here about table structure... I also wrote an example of what i use which can be found at http://www.seventhnight.com/treestructs.aspI'm glad I was of some help!Corey |
|
|
|
|
SELECT and JOIN with a COUNT ?