SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Select from, inner join, where

    Hello,

    I am trying to select a row that matches a specific id but I'm having trouble where it pulls 29 results when there is only one, basically I have an Id and, based on that id, I would like to check against another table, if the id has a match pull the row, here is my query

    Code SQL:
    SELECT 
    wp_posts . * , 
    wp_p2p.p2p_to AS connected
    FROM wp_posts
    INNER JOIN 
    wp_p2p ON wp_p2p.p2p_to = wp_posts.ID
    WHERE
    wp_posts.post_type = 'post_ad'
    AND
    wp_p2p.p2p_to = wp_posts.ID

    The setup is like this

    I have a post with id 40 and a post with id 41 in the table wp_posts
    In the table wp_p2p I have a row with 2 fields from and to from has the value 41 and to has the value 40
    Given the fact that I know the value 40 I need to go to the table wp_p2p retrieve value 41 so I can select from the table wp_posts the row with id 41
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tlacaelelrl View Post
    ...I'm having trouble where it pulls 29 results when there is only one
    your query looks okay (except for the ANDed condition, which is redundant with the ON condition)

    the only thing that explains why 29 results instead of one, is that a given wp_posts row actually does have 29 related wp_p2p rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the table has 29 records total and there is only one with that id so I am quite sure there is something wrong with my query, for now I am doing two queries as I needed this done but I am still looking for a way to pull the record in only one query
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tlacaelelrl View Post
    ... still looking for a way to pull the record in only one query
    you say "the" record, but your WHERE clause selects ~all~ wp_posts rows with post_type = 'post_ad'

    and then each of those is joined with ~all~ wp_p2p rows where p2p_to = wp_posts.ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you say "the" record, but your WHERE clause selects ~all~ wp_posts rows with post_type = 'post_ad'

    and then each of those is joined with ~all~ wp_p2p rows where p2p_to = wp_posts.ID
    Well, actually I came here because I know I am doing it wrong, and have not been able to figure out how to do it so yes, you can point out what's wrong with my query but that does not really help me, thank you for trying to help anyway!
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if my comments did not help you, perhaps you could try explaining your problem in more detail

    for instance, which post were you trying to retrieve?

    what did 40 and 41 have to do with it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if my comments did not help you, perhaps you could try explaining your problem in more detail

    for instance, which post were you trying to retrieve?

    what did 40 and 41 have to do with it?

    table wp_posts
    post id=40 is a post with text and it is of type post
    post id=41 it's saved in the same table has an ad and it is type post_ad

    table wp_p2p
    has 1 row only with the following details
    id 1 which does not really matter
    p2p_from = 41
    p2p_to = 40

    I my script I have access to the id number of post 40 so that is what I have an int 40 then from there I have to create the query go to the the table wp_p2p.p2p_to that has a value of 40 select the table and grab the value of wp_p2p.p2p_from from that row which in this case would be 41 from there and having the 41 value go to the table wp_posts and grab the post with id 41 the ids are unique in wp_posts
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    obviously there are multiple rows being joined to provide you with 29 result rows

    i think your query must be working exactly as you wrote it

    if it's not doing what you want it to do, then you haven't correctly explained what you want

    perhaps you could create a test case (CREATE TABLE stattements, plus INSERT statements for the data) which would allow us to try to reproduce your results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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