Databases
StackOverflow2013
Postsdbo
POMySQL left join not working with AS keyword
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL left join not working with AS keyword
    primarykey
    data
    text
    <p>I like to use self explaining names for associative selects, and sometimes it's even mandatory to avoid duplicates, so I use the AS keyword alot. But it's giving me some trouble with left joins.</p> <p>This works:</p> <pre><code>$sql = "SELECT *, projects.id as projects_id FROM projects"; $sql .= " LEFT JOIN". " (SELECT projectfiles.id as projectfiles_id, projectfiles.fileID as projectfiles_fileID, projectfiles.projectID as projectfiles_projectID FROM projectfiles ) AS projectfiles". " ON projects.id = projectfiles_projectID"; </code></pre> <p>However now I end up with useless data from <code>projects</code>, because it also picks up the fields <code>userID</code> and <code>name</code>, which I don't need. It's also picking up the id twice.</p> <p>So I tried changing it to;</p> <pre><code>$sql = "SELECT projects.id as projects_id FROM projects"; </code></pre> <p>With the ON line becoming</p> <pre><code>" ON projects_id = projectfiles_projectID"; </code></pre> <p>But that gave the error <code>Unknown column projects_id</code></p> <p>So I tried</p> <pre><code>" ON projects.projects_id = projectfiles_projectID"; </code></pre> <p>But still the same error</p> <p>I then started experimenting, and tried (as a test)</p> <pre><code>$sql = "SELECT id,name,userID FROM projects"; $sql .= " LEFT JOIN". " (SELECT projectfiles.id as projectfiles_id, projectfiles.fileID as projectfiles_fileID, projectfiles.projectID as projectfiles_projectID FROM projectfiles ) AS projectfiles". " ON projects.id = projectfiles_projectID"; </code></pre> <p>And to my surprise, the LEFT JOIN didn't seem to pick up anything at all.</p> <p>Code:</p> <pre><code>$sql = "SELECT id,name,userID FROM projects"; $sql .= " LEFT JOIN". " (SELECT projectfiles.id as projectfiles_id, projectfiles.fileID as projectfiles_fileID, projectfiles.projectID as projectfiles_projectID FROM projectfiles ) AS projectfiles". " ON projects.id = projectfiles_projectID"; $res = mysql_query($sql); if(!$res) die(mysql_error()); if(mysql_num_rows($res) &gt; 0) { $rownum = 0; while($row = mysql_fetch_assoc($res)) { print_r($row); echo "&lt;br/&gt;&lt;br/&gt;"; $rownum++; } } </code></pre> <p>Output:</p> <p><img src="https://i.stack.imgur.com/gcnSp.png" alt="problem"></p> <p>Which is weird because there is only one row in <code>projects</code> but 3 in <code>projectfiles</code> with that projectID... what am I doing wrong?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USnatli
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL left join not working with AS keyword
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL left join not working with AS keyword
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL left join not working with AS keyword
      1. This table or related slice is empty.
      1. VTUpMod
    1. COUse instance of column name in ON condition and while selecting also, it will give you correct solution. Don't need to complicate by using AS. Show some your sample data and required o/p.
      singulars
      1. POMySQL left join not working with AS keyword
      1. USSomnath Muluk
    2. COWhat's your expected output?
      singulars
      1. POMySQL left join not working with AS keyword
      1. USJa͢ck
    3. CODo you want single record with Projects table then use DISTINCT.
      singulars
      1. POMySQL left join not working with AS keyword
      1. USSomnath Muluk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload