Help Creating a Query

[computermax2328]

Hi All,

 

I have been trying to figure out this query for a couple of days now. I have been coming back to it over and over again and I still can't wrap my head around it. Care to give some help or ideas?

 

I did not design this database so I am not stumping myself. I have a table of posts which have an ID associated with them. When I associate an image with this post the URL of that image gets put into a separate table along with the post's ID. The image URL has it's own ID as well, which is different than the post's. So it looks like this.

 

Table 1: Post id

 

Table 2: Post id, image URL id, image URL

 

These images are going to be in a loop of posts so I can't pull the id as a variable into the query. It is almost like I need to say SELECT * FROM table WHERE id="This specific post's id";

 

Does that make sense?

 

Thanks in advance,

[Barand]

why don't you try that

[computermax2328]

I don't understand how to get the "specific post id" variable into the where clause. My first idea was to loop the query after another query, but I was told not to do that ever.

[Barand]

what is the "other query"?

[computermax2328]

So I was gonna do something like "SELECT title, content, id FROM posts," then loop it through, and assign variables to the values that come out. Then run a query through like "SELECT * FROM other_table WHERE id=$id."

 

Does that make sense. I know that isnt right. I just don't know know to unique variable into the first query. 

[DavidAM]

JOIN

 

Select all the data you need from both tables in a single query:

 

 

SELECT title, content, p.id, imageUrl
FROM posts AS p LEFT JOIN postImages as i ON p.id = i.postid
-- YADA YADA YADA

The LEFT JOIN will ensure you get POSTS even if they do not have an associated image. Leave LEFT out of the JOIN if you only want posts that have images.

 

The problem with this approach is that any POST that has more than one image associated with it, will be in the resultset multiple times (once for each image).