JOIN query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

JOIN query

Post 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.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

You can use:

SELECT forum_posts.* FROM .....
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Thanks for your help just what I needed :)
Post Reply