Am I retrieving the correct records?

Databases
1

Could use some help making sure I am retrieving the correct records for my query.

I have these tables…

article

  • id (pk)

comment

  • article_id (pk)
  • member_id (pk)
  • created_on (pk)
  • body
  • status

member

  • id (pk)
  • first_name

My query is supposed to pull all Comments for the selected Article.

Here is my query in PHP…


 // Build query.
 $q2 = 'SELECT m.first_name, c.created_on, c.body, c.status
 FROM member AS m
 INNER JOIN comment AS c
 ON m.id = c.member_id
 WHERE c.article_id=?';

I think what is confusing me is that my Primary Key for the Comment table is:

[INDENT]- article_id (pk)

  • member_id (pk)
  • created_on (pk)
    [/INDENT]

…but I am joining on just on field?! :-/

(Sorry, I have written any queries in ages!!!)

Debbie

2

Your query will get all rows from the comments table that have articleid = the given id.
And each of those rows will be joined to a row from the members table through the joining condition (m.id = c.member_id)

“Primary key” and “join columns” are two separate concepts. You can join tables on any column(s) you want. They don’t have to be (primary) keys, although an index on them is recommended because it will speed up the query.

The only thing that I’m not certain of (but I’m sure @r937; can tell us more about it) is if you’d need an index on member_id (apart from it being part of the primary key) to speed up the join. If it was the first column of the composite PK there would be no need (if I remember correctly), but it’s the second column.

3

The query is deleted, so I cant see what exactly it is, but just to clear up some confusion.

If a column is marked as a Primary Key, either as a separate or composite, that/those columns has an index already due to it/they are the Primary Key. So you don’t want to add another index on a column who is the Primary Key. The exception here is if it is a Composite Primary Key, and you want to use one of columns without using the one(s) prior to it. In that case you will need an index on it.

An important part to keep in mind is that it does not matter how many indexes you add to your tables, as each query cant utilize more than one per join. In addition it is also important that you write your query according to your indexes.

Example:
If we have a table consisting of username, firstname, lastname and email and we got an index on firstname.

Wont use the index:
WHERE lastname=@lastname AND firstname=@firstname

Will use the index:
WHERE firstname=@firstname AND lastname=@lastname

This becomes very important when you use composite keys, as you need to make certain you write your query according to the order of your composite key.

As a side note, a composite key is the only way you can utilize an index on more than one column in your query.

Example:
If we think on the same table as above, if we add a separate index on both the firstname and lastname, then the query below will only use the index on the firstname

WHERE firstname=@firstname AND lastname=@lastname

While if we had made a composite key consisting of firstname and lastname (in that order), the query would use the composite key index to speed up the query on both columns.

NOTE
Please see post #8 for further clarification on this post.
Spike

4

Yes, I guess the OP changed her mind about asking that question…

If a column is marked as a Primary Key, either as a separate or composite, that/those columns has an index already due to it/they are the Primary Key. So you don’t want to add another index on a column who is the Primary Key. The exception here is if it is a Composite Primary Key, and you want to use one of columns without using the one(s) prior to it. In that case you will need an index on it.

Ok, that’s what I thought. In fact, that’s what I said in my answer, thanks for confirming.

And thanks for the rest of the explanation as well.

This becomes very important when you use composite keys, as you need to make certain you write your query according to the order of your composite key.

I learned something today (if I ever knew, I forgot…) :slight_smile:

5

whoa, that’s unusual – you’re saying that mysql is not able to figure out that these are actually the same?

i’ve gotta go test this, hang on…

6

Its the way MySQL works, so there is no need to test it:

They also explain this in the manual for the composite keys, its a little down on the page:
http://dev.mysql.com/doc/refman/5.1/en/multiple-column-indexes.html

Note, I used version 5.1 here since we are still forced to use that in enterprise setups, but if you check the manual for the other versions they will say the same.

7

i’m still not buying it

i read that page in the manual you linked to, and everything it says makes perfect sense

then i read your example again, and that still doesn’t make sense

maybe you could go back a few posts and re-read what you actually wrote?

:slight_smile:

8

Ah, yea I see what you mean. I had to re-read it several times before I caught it, in my head the example was all about composite keys, so I kept reading it that way.

Yes, your absolutely correct. The example is wrong regarding single indexes, Ill update the text so it reflect what I thought I was making an example about :slight_smile:

[Edit]
Crap, I see that I’m not able to edit the post. So anyone reading this, please make a note that the example is reflecting composite keys.

9

[ot]TRD, edited the post with a NOTE
[/ot]

10

Since this deleted thread was revived, I would like to get my original problem fixed…

The query in the Original Post is NOT working as expected.

Because this is an SQL and database issue - to which all of you don’t have access to the data - it is harder to trouble-shoot, yet not impossible.

I did an echo of my PHP query to see what MySQL was receiving…


SELECT m.first_name, c.created_on, c.body, c.status
FROM member AS m
INNER JOIN COMMENT AS c ON m.id = c.member_id
WHERE c.article_id =2

When I run that output SQL string in phpMyAdmin, I get…


first_name 	created_on 	body 	status
Debbie 	2012-01-08 14:09:27 	Jan 8 2:09pm 	Pending

…however, if I look in my Comments table, there are 5 records which have an article_id=2

I think the problem is with my INNER JOIN because I delete Members regularly as I am testing my account creating and activation code.

So out of the 5 records in the Comments table where article_id=2…

3 records have a member_id=24,
1 record has a member_id=34,
1 record has a member_id=42

And as of today, member_id=42 is the only record existing in the ones mentioned above.

And as I am typing, that Member was created today, and the only Comment that I am getting for article_id=2 is dated TODAY just like member_id=42 who was created today.

Can someone help me figure this out?!

Questions:

1.) Does it make sense to want to show Comments for which there is a deleted Member? (I know in production you shouldn’t delete records?!)

2.) Is there a way to show all Comments even for some of the Members that were deleted?

3.) If I used an Outer Join, could I show Comments whether or not they have a Member?

Thanks,

Debbie

11
  1. no !!
  2. yes, outer join
  3. yes, see 2.
12

On my own, I just ran a RIGHT JOIN and it solved my missing Comments issue.

Guess I should just delete those old Comments and know that my SQL and PHP apparently were working okay all along… (Right?)

Debbie

13

i dunno, i think the inner join is the better decision

i think you should employ ON DELETE CASCADE so that if you delete a member, that member’s comments also get deleted

14

Oh, no, I meant I fixed things because now I know where all of my Comments went?!

Yeah, I keep my INNER JOIN.

i think you should employ ON DELETE CASCADE so that if you delete a member, that member’s comments also get deleted

How do I do that?

Is that in SQL, or is that an attribute in MySQL?

Debbie

15

have you ever declared a foreign key before? are these innodb tables?

16

Gee, you know, no, I haven’t?! :-/

(Why don’t they make phpMyAdmin or some other tool that draws relationships between like fields in between tables?! When I used MS Access in the past, it would draw relationships between tables…)

How does my INNER JOIN work if I don’t have Foreign Keys?? :-/

are these innodb tables?

Good question!

How do I check what I have in phpMyAdmin??

Debbie

17

exactly the same way as it does now

run this query in the SQL window –

SHOW CREATE TABLE comment
18

So if I don’t have Foreign Keys, then it doesn’t impact data integrity?

run this query in the SQL window –

SHOW CREATE TABLE comment

It says MyISAM.

So what about back to your original comments about if I delete a User…

Debbie

19

ah, but it does

a salient example is this thread – see post #10

if you had implemented foreign keys, with ON DELETE CASCADE, you would’ve never had to figure out the data integrity problem of not returning the right number of comments

you’d need first to change the tables to innodb, and then declare the foreign keys…

20

So Foreign Keys and “referential integrity” are not built in to MySQL?

you’d need first to change the tables to innodb, and then declare the foreign keys…

How do I do that?

And how do I declare Foreign Keys?

Can it be done after I have data in my tables?

Are there different types of Foreign Keys, or are they all the same?

Thanks,

Debbie