Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slimt_slimt
Aged Yak Warrior
746 Posts |
 Posted - 2007-07-13 : 10:03:37
|
| hi,i have two tables generally:bB_posts - postid - title - body - posttime - commentcount bB_comments - commentid - postid (FK) - commenttext*/Select t1.title, t1.body, t1.posttime, t2.commenttext from bB_posts as t1 join bB_comments as t2 on t2.postid = t1.postid (or t1.commentcount = 0)This query prints out all the posts which have 1 or more complementary comments. But i would aslo like to print the posts which have 0 comments (seen in brackets).how should i do this?thank you |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-07-13 : 10:08:41
|
| try a left join instead of an inner join.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2007-07-13 : 10:21:51
|
| [code]Select t1.title, t1.body, t1.posttime, t2.commenttextfrom bB_posts as t1 left join bB_comments as t2 on t2.postid = t1.postid [/code] You are not specific enough on what output you need so this may or may not solve your problem.Basically the LEFT JOIN means List ALL the bB_posts records whether it has a foreign key record in bB_comments or not.In the case of posts with no comments the t2.commenttext will be NULL;-]... Quack WaddleBlog me: http://hollystyles.blogspot.com |
|
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2007-07-13 : 15:01:57
|
| thank you for the hint!Left join makes it!one thing that strikes me, was the problem with repetition of the fields, know as Cartesian product.postid | title | body | comment-------------------------------- 2 | title2 | body2 | comment1 2 | title2 | body2 | comment2 2 | title2 | body2 | comment3 3 | title3 | body3 | comment4etc...how can i omit that if there is more comments under same postid, title and body shold be displayed only once.any idea?thank you. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-13 : 15:40:40
|
quote:
Originally posted by slimt_slimt
thank you for the hint!Left join makes it!one thing that strikes me, was the problem with repetition of the fields, know as Cartesian product.postid | title | body | comment-------------------------------- 2 | title2 | body2 | comment1 2 | title2 | body2 | comment2 2 | title2 | body2 | comment3 3 | title3 | body3 | comment4etc...how can i omit that if there is more comments under same postid, title and body shold be displayed only once.any idea?thank you.
That's not a cartesian product .... that's just a regular join. The title is repeated because each comment has the same title. If you want to show the title just once with an indent or something, that is a formating/presentation issue and not something you do at the database layer. i.e., you would take care of that on a web page, report, or client application when outputting the data.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2007-07-13 : 16:00:20
|
quote:
one thing that strikes me, was the problem with repetition of the fields, know as Cartesian product.... ommited for brevityhow can i omit that if there is more comments under same postid, title and body shold be displayed only once.any idea?thank you.
It depends what you you want to do with the comments if (sucks in breath) you want to pivot them out horizontally (squeezes eyes regretting even mentioning it) Or just show the first one or not show them at all. The simplest is using GROUP BYSelect t1.title, t1.body, t1.posttime, min(t2.commenttext) as commenttextfrom bB_posts as t1 left join bB_comments as t2 on t2.postid = t1.postid group by t1.title, t1.body, t1.posttime For posts with more than one comment, this will display the minimum comment alphabetically for each post.jsmith8858 is right really though. This is best handled in the presentation layer in this situation (The report software, web page or whatever you are going to use to 'publish' your output). Also don't forget you should decide/think about how you want to order the records, don't just rely on them coming out in postid order, one day they wont, it's not guaranteed. You must specify or let the presentation layer specify an order. In the database for example use the ORDER BY <some column> at the end of the TSQL statement. ;-]... Quack Waddle |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-07-14 : 00:39:37
|
quote:
Originally posted by slimt_slimt
thank you for the hint!Left join makes it!one thing that strikes me, was the problem with repetition of the fields, know as Cartesian product.postid | title | body | comment-------------------------------- 2 | title2 | body2 | comment1 2 | title2 | body2 | comment2 2 | title2 | body2 | comment3 3 | title3 | body3 | comment4etc...how can i omit that if there is more comments under same postid, title and body shold be displayed only once.any idea?thank you.
Thats called Suppress if duplicated featureRefer page 2http://sqlteam.com/forums/topic.asp?TOPIC_ID=76862MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
two tables query