Jump to content

Archived

This topic is now archived and is closed to further replies.

stickman

Select with 2 tables

Recommended Posts

I dont know if this is possible, but I was wondering if there was a way to only select a user from table A if that user has at least one row in table B. Here is how I do it in PHP:

[code]
$query = "SELECT * FROM `table_a` ORDER BY `user`";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
    $query2 = "SELECT * FROM `table_b` WHERE `user_id` = '{$row['id']}'";
    $result2 = mysql_query($query2);
    if(mysql_num_rows($result2)==0) {continue;}
    echo $row['user'];
    echo "<br>";
}
[/code]

That will output every user that has at least one row in table B. I was wondering if there was any way to do that with 1 SELECT statement?

Thanks in advance for any help!!!

Share this post


Link to post
Share on other sites
I figured it out. Here is my query for people who are asking the same question. I'm not sure if this is the proper way or not, but it works:

[code]SELECT * FROM `table_a` a, `table_b` b WHERE a.id = b.user_id GROUP BY a.user ORDER BY a.user[/code]

Share this post


Link to post
Share on other sites
Well, first, proper JOIN syntax would be preferable; second, this doesn't satisfy your "at least" condition, since it will only retrieve those that match exactly.

Share this post


Link to post
Share on other sites
The GROUP BY satisfies the at least. I dont need to return those rows or know how many rows there are, I just need to know if there is at least one row, but the user can have more. I'm not sure how to use the JOIN syntax.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.