mysql select: 2 fields from to tables

notset

Hello,

I'm creating login system, which is based by batch number.

I know how to do this in 2 queries, but i think it's possible to get the same result from one query.

$sql = "SELECT `dep_id` FROM `deposits` WHERE `dep_batch` = ".$batch;
$result = mysql_query($sql) OR exit(mysql_error());
$row = mysql_fetch_assoc($result);
if ($row != NULL) {
  $sql = "SELECT `inv_id` FROM `investments` WHERE `dep_id` = ".$row['dep_id'];
  $result = mysql_query($sql) OR exit(mysql_error());
  $row2 = mysql_fetch_assoc($result);
  if ($row != NULL) {
    $_SESSION['inv_id'] = $row2['inv_id'];
  }
}
if (!isset($_SESSION['inv_id'])) exit('Invalid batch!');

In the others words, i want to get inv_id from table investments where dep_id is equal to dep_id from deposits where batch = $batch.

How to do this in one mysql query?

Thank's

Piranha

$sql = "SELECT deposits.`dep_id`, inv_id from investments FROM `deposits`, investments WHERE deposits`dep_batch` = ".$batch." and deposits.dep_id = investments.dep_id;";

I think that you can figure out the rest from this.

Queops

Piranha wrote:

$sql = "SELECT deposits.`dep_id`, inv_id from investments FROM `deposits`, investments WHERE deposits`dep_batch` = ".$batch." and deposits.dep_id = investments.dep_id;";

I think that you can figure out the rest from this.

And clean some code too 🙂

$sql = "SELECT deposits. dep_id, inv_id from investments FROM deposits, investments WHERE deposits dep_batch = ".$batch." and deposits.dep_id = investments.dep_id;";

notset

Error

SQL query: Documentation

SELECT deposits.dep_id, inv_id
FROM investments
FROM deposits, investments
WHERE deposits dep_batch =12345
AND deposits.dep_id = investments.dep_id
LIMIT 0 , 30;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM deposits , investments WHERE deposits dep_batch = 12345 and deposits . dep_' at line 1

rupertbj

You have 2x from statements and there's a missing dot

notset

I think that i created the query which i needed.

$sql = "SELECT inv_id FROM investments WHERE dep_id = (SELECT dep_id FROM deposits WHERE dep_batch = ".$batch.")";