title: Create SQL query that gets data from several tables WHEN ID is in one table
body: <p>I am trying to create a news feed and from previous questions have found the simplest way to execute it but do not know what kind of query I should be making in SQL.</p>
  
  <p>I have a table titled artists that has the members ID, artist name and url to their page, all need to be retrieved. If an ID is not in this table it should not be included in the output of the statement.</p>
  
  <p>I have several tables with similar structures such as tracks, status and news. There are more but we'll stick to those three for this question and I can add more late on. In these tables is the ID among other things and I shall provide these later.</p>
  
  <p>The third table is one titled events. Data is inserted in here via a trigger AFTER data is added to any of the other tables I have referenced. This table contains the ID, the action (so something like 'has uploaded a track.') and the timestamp.</p>
  
  <p>ARTIST TABLE;</p>
  
  <pre><code>CREATE TABLE `artists` (
  `ID` int(11) NOT NULL,
  `name` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `url` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `DP` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `url` (`url`),
  UNIQUE KEY `DP` (`DP`),
  KEY `ID` (`ID`),
  CONSTRAINT `artists_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `members` (`ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
  </code></pre>
  
  <p>TRACKS</p>
  
  <pre><code>CREATE TABLE `tracks` (
  `ID` int(11) NOT NULL,
  `url` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `name` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `track_ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`track_ID`),
  UNIQUE KEY `url` (`url`),
  UNIQUE KEY `track_ID` (`track_ID`),
  KEY `ID` (`ID`),
  CONSTRAINT `tracks_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `members` (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=111112 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
  </code></pre>
  
  <p>NEWS </p>
  
  <pre><code>CREATE TABLE `news` (
  `ID` int(11) NOT NULL,
  `article` text COLLATE latin1_general_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  KEY `ID` (`ID`),
  
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
  </code></pre>
  
  <p>STATUS</p>
  
  <pre><code>CREATE TABLE `status` (
  `ID` int(11) NOT NULL,
  `status` varchar(300) COLLATE latin1_general_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  KEY `ID` (`ID`),
  CONSTRAINT `status_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `artists` (`ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
  </code></pre>
  
  <p>EVENTS</p>
  
  <pre><code>CREATE TABLE `events` (
  `ID` int(11) NOT NULL,
  `action` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  KEY `ID` (`ID`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `members` (`ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='This table shows what the artist has done and is used feed'
  </code></pre>
  
  <p>The query I have tried and it failed. This one only references the tracks and events tables. I am not sure how to add the other tables to the query.</p>
  
  <pre><code>SELECT
  T.url,
  T.ID,
  
  E.action,
  E.ID,
  E.timestamp
  
  FROM tracks T
  JOIN events E ON T.ID = E.ID
  WHERE T.ID = E.ID AND E.action = 'has uploaded a track.'
  ORDER BY E.timestamp DESC
  </code></pre>
  
  <p>To answer extremely valid comment, the query is in an AJAX requested page for a news feed. The output will be something along the lines of;</p>
  
  <p>An artist has uploaded a track.<br>
  <strong>Track goes here</strong><br>
  <strong>Formatted timestamp here</strong>
  <hr>
  Another artist has some news.<br>
  <strong>News goes here</strong><br>
  <strong>Formatted timestamp here</strong></p>
  
  <hr>
  
  <p>Hopefully that will give you an idea. </p>
  
  <p>All data by the end of it should be ordered by timestamp. Thanks in advance.</p>