title: Get data from multiple tables in one result set in MySQL
body: <p>In a small library/research database application I am working on, I have a page where a user can view all of the resources they have submitted. </p>
  
  <p>I have three tables for different resources - Books, Journals and Conferences. The tables look like this:</p>
  
  <pre><code>mysql&gt; desc book;
  +---------------+------------------+------+-----+---------+----------------+
  | Field         | Type             | Null | Key | Default | Extra          |
  +---------------+------------------+------+-----+---------+----------------+
  | id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | author        | varchar(255)     | YES  |     | NULL    |                |
  | publishedyear | char(4)          | YES  |     | NULL    |                |
  | title         | varchar(255)     | YES  |     | NULL    |                |
  | edition       | int(3) unsigned  | YES  |     | NULL    |                |
  | publisher     | varchar(100)     | YES  |     | NULL    |                |
  | place         | varchar(50)      | YES  |     | NULL    |                |
  | image         | varchar(100)     | YES  |     | NULL    |                |
  | isbn          | varchar(20)      | YES  |     | NULL    |                |
  | callnumber    | varchar(30)      | YES  |     | NULL    |                |
  | status        | int(1) unsigned  | YES  |     | NULL    |                |
  | abstract      | text             | YES  |     | NULL    |                |
  | toc           | text             | YES  |     | NULL    |                |
  | problems      | text             | YES  |     | NULL    |                |
  | futurework    | text             | YES  |     | NULL    |                |
  | registered    | datetime         | YES  |     | NULL    |                |
  | mid           | int(10) unsigned | NO   | MUL | NULL    |                |
  | iid           | int(10) unsigned | NO   | MUL | NULL    |                |
  +---------------+------------------+------+-----+---------+----------------+
  18 rows in set (0.00 sec)
  </code></pre>
  
  <hr>
  
  <pre><code>mysql&gt; desc journal;
  +---------------+------------------+------+-----+---------+----------------+
  | Field         | Type             | Null | Key | Default | Extra          |
  +---------------+------------------+------+-----+---------+----------------+
  | id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | author        | varchar(255)     | YES  |     | NULL    |                |
  | publishedyear | char(4)          | YES  |     | NULL    |                |
  | title         | varchar(255)     | YES  |     | NULL    |                |
  | journaltitle  | varchar(255)     | YES  |     | NULL    |                |
  | volume        | int(3) unsigned  | YES  |     | NULL    |                |
  | issue         | int(5) unsigned  | YES  |     | NULL    |                |
  | pagenumbers   | varchar(15)      | YES  |     | NULL    |                |
  | image         | varchar(100)     | YES  |     | NULL    |                |
  | isbn          | varchar(20)      | YES  |     | NULL    |                |
  | callnumber    | varchar(30)      | YES  |     | NULL    |                |
  | status        | int(1) unsigned  | YES  |     | NULL    |                |
  | abstract      | text             | YES  |     | NULL    |                |
  | toc           | text             | YES  |     | NULL    |                |
  | problems      | text             | YES  |     | NULL    |                |
  | futurework    | text             | YES  |     | NULL    |                |
  | registered    | datetime         | YES  |     | NULL    |                |
  | mid           | int(10) unsigned | NO   | MUL | NULL    |                |
  | iid           | int(10) unsigned | NO   | MUL | NULL    |                |
  +---------------+------------------+------+-----+---------+----------------+
  19 rows in set (0.00 sec)
  </code></pre>
  
  <hr>
  
  <pre><code>mysql&gt; desc conference;
  +----------------+------------------+------+-----+---------+----------------+
  | Field          | Type             | Null | Key | Default | Extra          |
  +----------------+------------------+------+-----+---------+----------------+
  | id             | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | author         | varchar(255)     | YES  |     | NULL    |                |
  | publishedyear  | char(4)          | YES  |     | NULL    |                |
  | title          | varchar(255)     | YES  |     | NULL    |                |
  | conferencename | varchar(255)     | YES  |     | NULL    |                |
  | location       | varchar(100)     | YES  |     | NULL    |                |
  | conferencedate | varchar(15)      | YES  |     | NULL    |                |
  | pagenumbers    | varchar(15)      | YES  |     | NULL    |                |
  | image          | varchar(100)     | YES  |     | NULL    |                |
  | isbn           | varchar(20)      | YES  |     | NULL    |                |
  | callnumber     | varchar(30)      | YES  |     | NULL    |                |
  | status         | int(1) unsigned  | YES  |     | NULL    |                |
  | abstract       | text             | YES  |     | NULL    |                |
  | toc            | text             | YES  |     | NULL    |                |
  | problems       | text             | YES  |     | NULL    |                |
  | futurework     | text             | YES  |     | NULL    |                |
  | registered     | datetime         | YES  |     | NULL    |                |
  | mid            | int(10) unsigned | NO   | MUL | NULL    |                |
  | iid            | int(10) unsigned | NO   | MUL | NULL    |                |
  +----------------+------------------+------+-----+---------+----------------+
  19 rows in set (0.00 sec)
  </code></pre>
  
  <p>What I am trying to do is very simple, but I can't seem to find a simple SQL command for it. Searching on stack overflow and google for "selecting data from multiple tables" and other similar searches returned a lot of results for ridiculously complex joins, unions, etc for databases and operations that are much more complicated than what I am trying to do here.  </p>
  
  <p>Is there not something like:</p>
  
  <pre><code>Select * FROM book, journal, conference WHERE mid = 4'
  </code></pre>
  
  <p>This returns an error: "field mid is ambiguous".</p>
  
  <p>Thanks! </p>
  
  <p>**EDIT - As martijn pointed out, a lot of my problem is coming from the design of my database. </p>
  
  <p>Two solutions seem to be to either redesign the database and combine <code>book</code>, <code>journal</code> and <code>conference</code> into one table <code>resources</code> </p>
  
  <p>or, as a very hackish workaround</p>
  
  <p>To do three separate queries: </p>
  
  <pre><code>SELECT * FROM book where mid = 4
  
  SELECT * FROM journal WHERE mid = 4
  
  SELECT * FROM conference WHERE mid =4
  </code></pre>
  
  <p>and then combine those result sets using PHP. </p>