CODEWITHSUNDEEP
phpmysql
yuk
yuk

Reputation: 933

Get foreign id in relational mysql tables

I have two mysql tables like this:

table 'names':
{   
    name varchar(25),
    date_id,
    FOREIGN KEY (date_id) REFERENCES dates(date_id) 
}

table 'dates'
{
    date datetime,
    date_id INT PRIMARY KEY AUTO_INCREMENT
}

I need to SELECT all names with specific date. Currently i make two requests. First one to get the id of date. And second one to get names. Can i make only one request somehow?

$sql = "SELECT date_id WHERE date='$date'";  // $date is safe variable
$sql = "SELECT name FROM names WHERE date_id='$id'";

Upvotes: 2

Views: 70

Answers (4)

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

You can use simple JOIN to select the data as:

SELECT `name`, `date_id`
FROM `dates`
INNER JOIN `names`
ON `names`.`date_id` = `dates`.`date_id`
WHERE `dates`.`date` = 'your_date_variable';

Upvotes: 0

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

select p1.* from names p1 inner join dates p2 on p1.date_id =p2.date_id

Upvotes: 1

Daniel Waghorn
Daniel Waghorn

Reputation: 2985

Try:

SELECT `name`, `date_id`
FROM `dates`
INNER JOIN `names`
ON `names`.`date_id` = `dates`.`date_id`
WHERE `dates`.`date` = '$date';

Upvotes: 2

Glorfindel
Glorfindel

Reputation: 22631

Yes, you can with an INNER JOIN:

$sql = "SELECT names.name" .
       "  FROM names INNER JOIN dates ON names.date_id = dates.date_id" .
       "  WHERE dates.date='$date'";

Upvotes: 1

Related Questions