Jump to content

another table to the query


ajoo
 Share

Go to solution Solved by Barand,

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

  • Solution

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • 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.