another table to the query

[ajoo]

Hi all !

 

The query below works great. However I have moved the table_1.city field into a new table, namely, table_3.

$query = "SELECT xid, bb.fname, bb.lname, bb.city, bb.cell, bb.email, bb.fid, bb.cno, bb.total, bb.actic
from tmp_table 
INNER JOIN 
(
	SELECT table_1.fname, table_1.lname, table_1.free_id, table_1.city, table_1.cell, table_1.email, table_2.fid, table_2.cenno, count(fid) total, sum(case when scr = 'R' then 1 else 0 end) actic 
	From table_2, table_1 
	WHERE table_2.fid = table_1.free_id
	GROUP by table_2.fid, table_2.cenno
	ORDER by actic DESC
	LIMIT ?, ?		
) bb 
	ON bb.free_id = tmp_admin.xid";

How can I change the query to incorporate this change ? i.e. I need to draw the value of city from table_3.

 

Thanks & a very happy new year to all !

 

 

 

[Barand]

Add table_3 with a JOIN.

 

I have difficulty believing that query works. You have a reference to table "temp_admin" in your query but there is no such table in the FROM.

[ajoo]

Hi Guru Barand !

 

A very happy new year !

 

Thanks for the response.  Sir I have been trying to use the JOIN but I keep getting and 'ambiguous field' error.Could you please show me how it can be done? 

 

You are right about the tmp_admin table which I left out since I thought that would not be required for the answer. I do generate the tmp_admin table before this query is executed and yes the query works just fine. If you think that is relevant, I can post the query that generates the tmp_admin temporary table. 

 

Thank you and regards.

[Barand]

You will get "ambiguous field" error when you reference fields with the same name in two different tables. Prefix the column names with the table names (tablename1.colA , tablename2.colA) or aliases to avoid the ambiguity.

 

If you still have a problem, post the query.

 

Best wishes for 2018.

[ajoo]

Hi Guru Barand !!

 

Thanks, I got it  working !!

 

Regards.