How to Resolve Ambiguous Column Names When Retrieving Results

How to resolve ambiguous column names when retrieving results?

You can set aliases for the columns that you are selecting:

$query = 'SELECT news.id AS newsId, user.id AS userId, [OTHER FIELDS HERE] FROM news JOIN users ON news.user = user.id'

How to resolve Ambiguous column name error SQL

You have [area] in both the union area='000003' and you have it again in the select distinct area from xyzfirms201701. Which one do you actually want to use as "area"? Given that you appear to only want '000003' in the final result then use t0.area (see. second line below).

If you use a cross join in the cte, I presume you want all rows of that returned, so use a left join instead of inner join (? note, I am guessing this)

You are currently grouping by t2.area but don't include in the select clause. Either omit it from the grouping or include in in the select clause. Note because [area] it is part of the join it can only be whatever value you put into the CTE so I suggest you use sizeclasseptable.area

;with sizeclasseptable as (
    select t0.area,ownership,sizeclassep 
    from (
                  select '01' as sizeclassep, '50' as ownership, area='000003'
        union all select '02' as sizeclassep, '50' as ownership, area='000003'
        union all select '03' as sizeclassep, '50' as ownership, area='000003'
        union all select '04' as sizeclassep, '50' as ownership, area='000003'
        union all select '05' as sizeclassep, '50' as ownership, area='000003'
        union all select '06' as sizeclassep, '50' as ownership, area='000003'
        union all select '07' as sizeclassep, '50' as ownership, area='000003'
        union all select '08' as sizeclassep, '50' as ownership, area='000003'
        union all select '09' as sizeclassep, '50' as ownership, area='000003'
        ) t0
    /* cross join ( select distinct area from xyzfirms201701 ) t1 */
    )

SELECT
    sizeclasseptable.area  AS [area]
  , t2.SizeClassep
  , COUNT(*)               AS [Number of Worksites]
  , SUM(t2.Employment)     AS [Employment In Size Class]
FROM sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.area = sizeclasseptable.area
    AND t2.ownership = sizeclasseptable.ownership
    AND t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    sizeclasseptable.area
  , t2.SizeClassep
ORDER BY
    sizeclasseptable.area
  , t2.SizeClassep

edit

An alternative approach:

DECLARE @ownership varchar(20) = '50'
DECLARE @area varchare(20) = '000003'

WITH sizeclasseptable
AS (
    SELECT
        sizeclassep
    FROM (
                  select '01' as sizeclassep
        union all select '02' as sizeclassep
        union all select '03' as sizeclassep
        union all select '04' as sizeclassep
        union all select '05' as sizeclassep
        union all select '06' as sizeclassep
        union all select '07' as sizeclassep
        union all select '08' as sizeclassep
        union all select '09' as sizeclassep
    ) t0
)

SELECT
    t2.area
  , t2.SizeClassep
  , COUNT(*)           AS [Number of Worksites]
  , SUM(t2.Employment) AS [Employment In Size Class]
FROM sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.area = @area
AND t2.ownership = @ownership
AND t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    t2.area
  , t2.SizeClassep
ORDER BY
    t2.area
  , t2.SizeClassep

edit 2

Perhaps a method to reduce the number of query iterations would be to expand the grouping to all 3 of the columns used to select that data, and also broadening the way the where clause is defined. There is also a different method for produce the SizeClassep rows by using values

SELECT
    t2.area
  , t2.SizeClassep
  , t2.ownership
  , COUNT(*)           AS [Number of Worksites]
  , SUM(t2.Employment) AS [Employment In Size Class]
FROM (
    SELECT
        sizeclassep
    FROM (
    VALUES ('01'), ('01'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09')
    ) t0 (sizeclassep)
) sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.sizeclassep = sizeclasseptable.sizeclassep
AND t2.area IN ('0003','0004','0005','0006')  /* ALTER THE LIST TO SUIT YOUR NEEDS */
AND t2.ownership IN ('50','60','70')          /* ALTER THE LIST TO SUIT YOUR NEEDS */
AND 
GROUP BY
    t2.area
  , t2.SizeClassep
  , t2.ownership
ORDER BY
    t2.area
  , t2.SizeClassep
  , t2.ownership

PHP & MYSQL: How to resolve ambiguous column names in subquery

You need to prefix the alias to the table here:

WHERE relationship = 'analyzed' AND ur.userID IN

How to produce error msg on ambiguous column names?

This is possible. The reason that CI doesn't throw up SQL errors about ambiguous column names is because it appends the name of the table when selecting so for example

SELECT `table`.`name`, `table2`.`name` FROM `table`, `table2` ...

The reason you then don't get to see these columns is because the array key generated by mysqli_fetch_assoc() or whichever driver you're using is name for both and obviously gets overwritten. The following answer is suitable for those using CI2.x.

Extend the Database driver result class in a similar fashion used in my answer to CodeIgniter: SQL Audit of all $this->db->query() method calls?. Once you've extended the database driver result class you have two options. You can throw an error as you requested as follows:

/**
 * Result - associative array
 *
 * Returns the result set as an array
 *
 * @access  private
 * @return  array
 */
function _fetch_assoc()
{
    if (!($row = mysql_fetch_array($this->result_id)))
    {
        return null;
    }

    $return = array();
    $row_count = mysql_num_fields($this->result_id);

    for($i = 0; $i < $row_count; $i++)
    {
        $field = mysql_field_name($this->result_id, $i);

        if( array_key_exists( $field, $return ) ) {
            log_message('Error message about ambiguous columns here');
        } else {
            $return[$field] = $row[$i];
        }
    }

    return $return;

    //Standard CI implementation
    //return mysql_fetch_assoc($this->result_id);
}

Or alternatively you can further manipulate the output of the function to simulate the query behavior by prepending tablename_ to the column name resulting in an array like

SELECT * FROM `users` LEFT JOIN `companies` ON `users`.`id` = `companies`.`id`
Array (
    [users_id] => 1
    [users_name] => User1
    [companies_id] => 1
    [companies_name] => basdasd
    [companies_share_price] => 10.00
)

To achieve this you can simply modify the for loop in the above function to the following:

for($i = 0; $i < $row_count; $i++)
{
    $table = mysql_field_table($this->result_id, $i);
    $field = mysql_field_name($this->result_id, $i);
    $return["$table_$field"] = $row[$i];
}

Query error with ambiguous column name in SQL

We face this error when we are selecting data from more than one tables by joining tables and at least one of the selected columns (it will also happen when use * to select all columns) exist with same name in more than one tables (our selected/joined tables). In that case we must have to specify from which table we are selecting out column.

Following is a an example solution implementation of concept explained above

I think you have ambiguity only in InvoiceID that exists both in InvoiceLineItems and Invoices Other fields seem distinct. So try This

I just replace InvoiceID with Invoices.InvoiceID

   SELECT 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
    FROM Vendors 
    JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
    JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
    WHERE  
        Invoices.InvoiceID IN
            (SELECT InvoiceSequence 
             FROM InvoiceLineItems
             WHERE InvoiceSequence > 1)
    ORDER BY 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount

You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers

Spark Dataframe distinguish columns with duplicated name

I would recommend that you change the column names for your join.

df1.select(col("a") as "df1_a", col("f") as "df1_f")
   .join(df2.select(col("a") as "df2_a", col("f") as "df2_f"), col("df1_a" === col("df2_a"))

The resulting DataFrame will have schema

(df1_a, df1_f, df2_a, df2_f)

Getting ResultSet column names

The Creole drivers for MySQL use the "classic" MySQL functions, and you can do this too. With mysql_fetch_field() you can get information about a specific field, even if no rows were returned. You only need to pass the correct result resource, and you can get that via the getResource() method of your ResultSet.