Jump to content


Photo

Select with 2 tables


  • Please log in to reply
3 replies to this topic

#1 stickman

stickman
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 23 July 2006 - 09:12 PM

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:

$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>";
}

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!!!

..:: FREE NINTENDO WII OR $250 CASH ::..
..:: Click here for more information ::..


#2 stickman

stickman
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 24 July 2006 - 02:39 AM

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:

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

..:: FREE NINTENDO WII OR $250 CASH ::..
..:: Click here for more information ::..


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 July 2006 - 03:37 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 stickman

stickman
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 24 July 2006 - 03:53 AM

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.

..:: FREE NINTENDO WII OR $250 CASH ::..
..:: Click here for more information ::..





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users