Getting one record from one table and multiple records from another table

[Stefany93]

Howdy,

 

OK, I have been struggling with this problem all evening yesterday and this morning. Please help!

 

I have a table subscribers that holds the emails of subscribed users. Then I have the table posts that holds the blog posts.

I want to make it so that with one query I pull out all the emails from the subscribers table and only ONE record from the posts table namely the post_id and the post title so that I can that way construct a link to where the user can click on their email that will take them to the latest blog post. I can make it with two queries, but I really want to do it with one. I tried everything, sub-queries, joins, crosses, prayers with no good. 

 

I wrote this:

SELECT DISTINCT (SELECT DISTINCT MAX(post_id) FROM posts ) AS last_post ,  subscribers_email
FROM subscribers 
 

But it always pulls out duplicate post_id rows like this:

post_id subscriber_email
66 [email protected]
66 [email protected]

Here is my PHP code:

		function send_mass_email($db){
			$sql = 'SELECT subscribers_email, post_id, title
					FROM subscribers, posts
					WHERE CONDITION';
			$query = $db->prepare($sql);
			$query->execute();
				$suject = 'New blog post in www.dyulgerova.info';
			$message = '
			New blog post have been posted in www.dyulgerova.info/blog		Link - *LINK HERE*	';
			while($row = $query->fetch(PDO::FETCH_NUM)){
				mail($row[0], $subject, $message);
			}
		}

Thank you very much!

[Barand]

So you have two tables which are unrelated (so a join is out) and different structures (so a union is out).

 

I would stick with two queries or prayer.

[Stefany93]

So you have two tables which are unrelated (so a join is out) and different structures (so a union is out).

 

I would stick with two queries or prayer.

 

Damn it... Spend so much time on an un-solvable issue. So smart of me...

 

Thanks man.