Jump to content
ajoo

another table to the query

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 !

 

 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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.

Share this post


Link to post
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.

  • Like 1

Share this post


Link to post
Share on other sites

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.


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