使用MySQL进行多次查询

Question

Good Day! I am unfamiliar with multiple queries with PHP and MySQL and I haven't found an answer to my question (maybe it's not possible to do?). I have a database with several tables, and I try to build a multiple query (in PHP) which relies to the result of the previous query. Here is an example of my database:

Table1

id:1, first_name: Sally, last_name:Meyers,
id:2, first_name: George, last_name:Smith,
id:3, first_name: Peter, last_name:Gabriel,
id:4, first_name: John, last_name:Doe

Table2

type: hystory, number: 123456789, last_name:Meyers,
type: suspense, number: 1321465447, last_name: Smith,
type: sci-fi, number: 931213246, last_name: Gabriel,
type: suspense, number: 555221321, last_name: Doe

Table3

title: War and Peace, year: 1997, author: 3, type: hystory,
title: Justice for all!, year: 2014, author: 1, type: suspense,
title: Time lost, year: 2001, author: 2, type: sci-fi,
title: The cold war, year: 1981, author: 3, type: suspense,

My query would be something like this:

SELECT first_name, last_name FROM Table1 WHERE id=3 AND SELECT sci-fi FROM Table2 WHERE last_name = [result of the first query] AND SELECT title FROM Table3 WHERE type = [result of the second query];

All I want to do, at the end, is to return (via AJAX) a single JSON object to Javascript composed of the selected elements from the database. Can you help building this multiple query, please ?

Answer 1

Could be you need a join with the 3 table eg:

  SELECT first_name
      , last_name 
  FROM Table1 
  INNER JOIN Table2 ON Table2.last_name = Table1.last_name
  INNER JOIN Table3 ON Table3.type = Table2.type 
  WHERE Table1.id=3