Mo
|
| Posted: 01/12/2005, 4:57 PM |
|
Hi All!
i am having a big problem doing following query?
question is "List the titles and creation dates of all blogs, as well as the total number of blog entries associated with that blog?"
the query should look at the "Subject_Title" in the blog_Entries table and "PostComment" field from PostReply table. the count should count the Subject_title and how many post reply it has for that subject.
this is my code
"select BLOG_ENTRIES_ID,Creation_DATE, count(PostComment)
from Blog_Entries, PostReply
where Subject_Title=PostComment
group by BLOG_ENTRIES_ID,Creation_DATE;"
i have try everything i don what to do now? please help me
i have below list the two tables;
thanking u in advance
bye
Mo
==================================================
CREATE TABLE Blog_Entries
(BLOG_ENTRIES_ID VARCHAR2(20) PRIMARY KEY,
EMAIL VARCHAR2(20), CONSTRAINT Blog_Entries_FOREIGN_KEY FOREIGN KEY (EMAIL) REFERENCES Bloggers (EMAIL),
Creation_DATE DATE DEFAULT SYSDATE,
Subject_Title VARCHAR2(40),
Entries_Text VARCHAR2(160));
==================================================
CREATE TABLE PostReply
(POST_REPLY_ID VARCHAR2(8) PRIMARY KEY,
BLOG_ID VARCHAR2(10), CONSTRAINT PostReply_FOREIGN_KEY FOREIGN KEY (BLOG_ID) REFERENCES Blog (BLOG_ID),
Subject VARCHAR2(40),
PostComment VarChar2(1000),
REPLY_DATE DATE DEFAULT SYSDATE);
==================================================
|
|
|
 |
bmaclean
Posts: 7
|
| Posted: 01/14/2005, 7:37 PM |
|
I think your join condition is the problem. Your join between the tables is "WHERE Subject_Title=PostComment"
Subject_Title has a length of 40, while PostComment has a length of 1000. You didn't provide any sample data, but I wouldn't expect these two columns to contain values that would join.
The join columns in your database should be the same datatype and length, and should generally be related via foreign keys.
Thanks,
Mac
|
 |
|
Justman
|
| Posted: 01/15/2005, 4:02 AM |
|
I think there is problem with your database design. Base on the current structure of the database, you might run into performance problems later.
I suggest that you make the primary keys of the tables INTEGER data type.
Relate Blog_Enteries with PostReply (one Blog_Entries -< Many PostReply) make sure Blog_Entries id in PostReply table is of INTEGER data type.
Use Blog_Entries ID in the query criteria to get related replies (if any) from the PostReply table.
Example:
"select BLOG_ENTRIES_ID,Creation_DATE, count(PostComment)
from Blog_Entries A, PostReply B
where A.BLOG_ENTRIES_ID = B.BLOG_ENTRIES_ID //A and B are aliases for readability only.
group by BLOG_ENTRIES_ID,Creation_DATE;"
I hope this helps.
Cheers,
Justman
|
|
|
|
|
