Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    9

    Unanswered: Could someone help me with my query?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by johnnyt74 View Post
    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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    9
    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. ;-)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by johnnyt74 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    9
    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....


  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by johnnyt74 View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2010
    Posts
    9
    Quote Originally Posted by r937 View Post
    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.

    ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •