Querying Two Tables At Once

Question

Hello,

I am trying to do what I believe is called a join query. First, in a MySQL table called "login," I want to look up what "loginid" is in the record where "username" equals $profile. (This will be just one record / row in the MySQL table).

Then, I want to take that "loginid" and look up all rows / records in a different MySQL table called "submission," and pull data that have that "loginid." This could possibly be more than one record / row. How do I do this?

The code below doesn't seem to work.

Thanks in advance,

John

  $profile = mysql_real_escape_string($_GET['profile']);

  $sqlStr = "SELECT l.username, l.loginid, s.loginid, s.submissionid, s.title, s.url, s.datesubmitted, s.displayurl
               FROM submission AS s,
           login AS l
              WHERE l.username = '$profile',
           s.loginid = l.loginid
           ORDER BY s.datesubmitted DESC";

Answer 1

SELECT l.username, l.loginid, s.loginid, s.submissionid,
  s.title, s.url, s.datesubmitted, s.displayurl
FROM submission AS s
INNER JOIN login AS l
  ON s.loginid = l.loginid
WHERE l.username = '$profile'
ORDER BY s.datesubmitted DESC