Could someone help me with my query?

johnnyt74 · #1 (**permalink**) 07-17-10, 12:19

Hi,

I'm trying to query 2 tables but I'm having a problem.

Here's my query:

Code:

SELECT * FROM jtfriends friends, jtmemorials memorials WHERE friends.memorial_ID = '.$their_ID.' AND memorials.post_author = '.$their_ID.' AND memorials.ID = '.$memorial_ID.''

The problem is that in my "memorials" table there are several records with the same ID and so the query is (understandably) counting them multiple times against the "friends" table.

I want to really say...

Code:

SELECT * FROM jtfriends friends, jtmemorials memorials (LIMIT THE RESULT FROM THIS TABLE TO 1) WHERE friends.memorial_ID = '.$their_ID.' AND memorials.post_author = '.$their_ID.' AND memorials.ID = '.$memorial_ID.' '

Could anyone help me out with this?

How do I set a 'limit' up that only applies to one of the tables in the query?

Many thanks for any help with this

Cheers

John

r937 · #2 (**permalink**) 07-17-10, 17:11

Quote:

Originally Posted by johnnyt74

Could anyone help me out with this?

sure

but first, you need to do one thing: which columns do you really want?

i can't help you if you say "all of them"

johnnyt74 · #3 (**permalink**) 07-17-10, 20:33

Hi r937,

Thanks (once again) for helping me out!!

I basically want to count the instances of the 'friends' table that marry up with the 'memorials' table. Unfortunately, there are several instances in the 'memorials' table and I only want to count one of them.

So I want to say:
SELECT * FROM jtfriends friends, jtmemorials memorials (LIMIT THE RESULT FROM MEMORIALS TABLE TO 1) WHERE friends.memorial_ID = '.$their_ID.' AND memorials.post_author = '.$their_ID.' AND memorials.ID = '.$memorial_ID.' '

I hope that makes sense and, if it doesn't, please let me know and I'll try and be a bit clearer.

Thanks again for all your help. That's the third time you've helped me out on these forums and you really are a credit. I don't know how many people you help out on a day to day basis but you really need to know that it is appreciated (although I'm sure you roll your eyes at some of the questions!!).

Thank you

John

PS. After re-reading your question. I'm basically just looking for a count so I'm happy to use the ID of the friends table to use as the 'target' column. ;-)

r937 · #4 (**permalink**) 07-17-10, 21:03

Quote:

Originally Posted by johnnyt74

I basically want to count the instances of the 'friends' table that marry up with the 'memorials' table.

but you're still writing that dreaded, evil "select star" -- i was hoping you would identify the actual columns you needed in the result set

also, what are $their_ID and $memorial_ID? where do these values come from?

you mentioned that there is a one-to-many relationship between friends and memorials, but i'm not sure what those tables are for or which one of them is the "many" side of the relationship

also, you appear to have a particular memorial in mind -- what's up with that? you say you want to "limit the result from the memorials table to one" buy that appears to be what memorials.ID = '.$memorial_ID.' already does for you

johnnyt74 · #5 (**permalink**) 07-18-10, 09:14

r937,

Cracked it!!

I got rid of the 'dreaded' * as you suggested and concentrated on just the columns that I wanted and BINGO! it's working a treat now.

Thanks for pointing the way....

r937 · #6 (**permalink**) 07-18-10, 09:46

Quote:

Originally Posted by johnnyt74

Thanks for pointing the way....

i'm wondering if we got there safely

if you don't mind, can i see the query that's working?

johnnyt74 · #7 (**permalink**) 07-18-10, 10:30

Quote:

Originally Posted by r937

if you don't mind, can i see the query that's working?

Certainly... here it is:

Code:

SELECT friends.ID, friends.message, friends.user_ID, friends.memorial_ID, friends.status, memorials.post_title as memorial_name, memorials.post_author as author_ID, memorials.ID as mem_ID, 
users.display_name as display_name 
FROM jtfriends friends, jtmemorials memorials, jtusers users
WHERE friends.memorial_ID = '.$their_ID.' AND memorials.ID = '.$memorial_ID.' AND memorials.post_author = ' . $their_ID . ' AND friends.user_ID = users.ID

The key was in identifying the columns I needed and comparing my $memorial_ID to the memorials.ID column (a unique column) in the memorials table.

This then gave me the result I was after.

Thanks again for all your help.

;-)

Internet Services

Web Development

Digital Marketing

Consumer Tech