Page 1 of 1

JOIN query

Posted: Tue May 13, 2003 9:05 pm
by McGruff
In a search class, I need a JOIN to query a forum_posts table for the search terms at the same time as retrieving some column data from a forum_topics table with which to create the search results.

This works fine:

Code: Select all

$mysql = "SELECT * FROM forum_posts LEFT JOIN forum_topics USING (tid) WHERE forum_posts.text LIKE '%" . $search_term . "%'";
..but I don't need to SELECT * - just a handful of columns. Unfortunately, one of the columns I need is "tid" (topic ID) which I've used to join the tables (the only common field I can use).

If I specify some SELECT columns, including tid, I get the "ambiguous" error. If I just SELECT * tid is returned in the result set OK - but so is a whole pile of other unwanted stuff which is just wasting memory.

So, is there a way to get tid without SELECT'ing * ?

I could create another column "tid_copy" but that sounds like a bit of a hack.

Thanks in advance.

Posted: Wed May 14, 2003 2:07 am
by []InTeR[]
You can use:

SELECT forum_posts.* FROM .....

Posted: Wed May 14, 2003 2:52 am
by twigletmac
You can use an alias for the table name to get rid of the ambiguity, and for the fields to make it easier to extract them e.g.:

Code: Select all

SELECT t1.ID AS infoID, t2.ID AS testID FROM table1 AS t1, table2 AS t2...
Mac

Posted: Wed May 14, 2003 9:58 am
by McGruff
Thanks for your help just what I needed :)