Joining two tables with Composite Key

Databases
1

How do you create an Inner Join when two tables both have Composite Keys?

Here are my tables…

article_comment


- id (PK)
- article_id (UK1)
- member_id (UK2)
- created_on (UK3)
- body

article_comment_review


- id (PK)
- article_id (UK1)(FK)
- commenter_id (UK2)(FK)
- created_on (UK3)
- reviewer_id (UK4)(FK)
- rating

This query returns zero records which leads me to believe I screwed something up?!


SELECT ac.id, ac.body, acr.reviewer_id, acr.rating
FROM article_comment AS ac
INNER JOIN article_comment_review AS acr
ON ac.article_id = acr.article_id
AND ac.member_id = acr.commenter_id
AND ac.created_on = acr.created_on

What am I doing wrong?? :-/

Sincerely,

Debbie

2

With an Inner join the results have to be in both tables, or you return nothing.

So in your case it has to match all 3 keys exactly on the same row in both tables, or you return nothing.

From looking at your SQL, this looks like it should be all you need:


SELECT ac.id, ac.body, acr.reviewer_id, acr.rating
FROM article_comment AS ac
JOIN article_comment_review AS acr ON ac.article_id = acr.article_id
3

Looks like this may be more complicated than I want… :frowning:

Here is what I want to do…

My website has Articles.

Beneath a given Article, Members can post Comments.

And for each Comment, other Members can rate a given Comment on a mini-Form below the Comment.

I have all of this working, but now I want to hide the Comment-Survey Form on Comments for which the logged-in Member has already voted. (Just a visual queue as to which Comments they have or have not reviewed.)

In my “ARTICLE_COMMENT_REVIEW” table, I have a “created_on” field which when populated means a given Comment was rated.

So, if I could add the “ARTICLE_COMMENT_REVIEW” table my current query which populates the Comments section beneath an Article, then that extra piece of information should give my PHP what it needs to show/hide the Comment Form.

Follow me so far?

So I need to join “ARTICLE_COMMENT_REVIEW” to this abridged query…


SELECT m.id AS member_id, m.username, an so on...
	ac.id AS comment_id, ac.created_on, ac.comment_no, ac.body
FROM member AS m
INNER JOIN article_comment AS ac
ON m.id = ac.member_id

The problem is that the simplified test query in my OP wasn’t returning any records, and the more that I think about it, I am not sure how to do an Inner Join where the Parent Table has a Composite Key and the Child Table also has a Composite Key?!

Does that make sense??

Sincerely,

Debbie

4

i’m guessing, but i don’t think the comment and the review(s) on that comment would be created at exactly the same time

5

Not sure where you are going with your question, but…

An Article would be published by me, the Admin.

Then as people read the Article, they would post Comments.

Then as Comments are posted, other members would see them, form an opinion, and Rate the Comments.

Here is an ERD…


member (Commenter) -||------0<- article_comment ->0---------||- article

article_comment -||----------0<- article_comment_review

member (Reviewer) -||----------0<- article_comment_review

Sincerely,

Debbie

6

Here is some sample data that I tried to test my query on…

article_comment


id	article_id	member_id	created_on		body
---	-----------	----------	-----------		-----
5	1		20		2012-02-26 21:36:08	Proin euismod faucibus...

article_comment_review


id	article_id	commenter_id	created_on		reviewer_id	rating
---	-----------	-------------	-----------		------------	-------
1	1		20		2012-02-26 21:36:08	19		5

The parent and child records match, so I’m not sure why the test query in my OP didn’t return a joined record… :-/

Sincerely,

Debbie

7

It should be…

“article_comment_review.comment_created_on”

Knew it was something simple… :wink:

Debbie

8

it wasn’t a question, it was a statement, and it was intended to get you to look at your posting with fresh eyes, and realize that you hadn’t explained enough about the join

it turns out that the “something simple” was a column that you hadn’t even mentioned

9

I did mention it in my original post.

Unfortunately, my paper ERD incorrectly labeled the column as “created_on” instead of the updated “comment_created_on”.

And that is why my test query above didn’t return anything - the timestamps weren’t matching.

Hindsight it 20/20…

Debbie

10

no you did not

in the original post, the article_comment_review table does ~not~ have a column called comment_created_on

have a nice evening

:smiley:

11

You’re not very quick today…

I said the entire problem was that I mistakenly typed in “created_on” when it was supposed to be “comment_created_on”.

If I knew I had a type-o, I would have never needed to post here.

What don’t you understand?

My type-o created the confusion…

Debbie

12

type-o, lol…

What he’s saying is, regardless of whether it was a typo, oversight, or divine intervention, he had to make guesses rather than educated answers due to the question not providing correct or complete information

13

thank you, arout77

this is not the first time, either

i don’t know why i even bother to read debbie’s posts, never mind try to help

14

Thanks everyone.

As the problem has been resolved there’s little point in continuing discussion

Thread Closed