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


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

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

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

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!

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?

:slight_smile:

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

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