selecting from 2 tables. MySql

Question

How do i do select p.Quota without writing the long full name? right now i am doing

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
  FROM CUSTOMERS AS c, 
       PRODUCTS AS p 
 LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
 LIMIT 1 ;

I get the error:

ERROR 1054 (42S22): Unknown column 'c.StatusId' in 'on clause'

However the column does exit and this code works:

SELECT c.id, 
       c.UserName, 
       cs.StatusName 
  FROM CUSTOMERS AS c
  JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId = cs.CustomerStatusId 
 LIMIT 1 ;

Answer 1

SELECT c.id, c.UserName, p.Quota, cs.StatusName FROM (CUSTOMERS AS c, PRODUCTS AS p) LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId LIMIT 1 ;

Answer 2

You're mixing ANSI and non ANSI JOIN syntax with:

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
  FROM CUSTOMERS AS c, 
       PRODUCTS AS p 
  LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
  LIMIT 1 ;

Written using ANSI joins:

     SELECT c.id, 
            c.UserName, 
            p.Quota, 
            cs.StatusName 
       FROM CUSTOMERS AS c 
       JOIN PRODUCTS AS p ON --JOIN criteria goes here
  LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId = cs.CustomerStatusId 
      LIMIT 1;

...but I don't know what criteria you are using to join PRODUCTS to the CUSTOMERS table.

Answer 3

The problem is your implicit inner join followed by a left join. MySQL is trying to join PRODUCTS p on CUSTOMERSTATUSTYPES cs, without consideration for CUSTOMERS c.

Try this:

SELECT 
 c.id, c.UserName, p.Quota, cs.StatusName 
FROM 
 CUSTOMERS AS c 
 INNER JOIN PRODUCTS AS p 
 LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
LIMIT 1 

Also, right now you have no clause relating records in CUSTOMERS to those in PRODUCTS... you're just doing a full join. Is this what you want to be doing?