Jump to content

another table to the query


ajoo

Recommended Posts

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 !

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.