Page 1 of 1

Query: Inner Join Bug

Posted: Tue May 08, 2007 12:27 am
by ripcurlksm
What the HECK is wrong with this query? I cant get it to search/match the "company" field and it is throwing an error... this query works fine if i do "MATCH(date_year, market1, market2, market3, market4, market5, market6)" , but if I put "MATCH(company)" it breaks.... the company column is set exactly the same as the other fields ie- market1, market2, etc.

EDIT:
I turned on error reporting and I am getting this:
Column: 'company' in where clause is ambiguous
I am totally stumped. The company field in the database is set to FULL TEXT and it keeps breaking the MySQL result query throwing the error "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource" as if the company field isn't there or isn't returning any results.

Code: Select all

$query = "SELECT u.id
     , u.username
     , p.user_id
     , p.report_id
     , r.id
     , r.company 
     , r.description
     , r.market1
     , r.market2
     , r.market3
     , r.market4
     , r.market5
     , r.market6
     , r.location
     , r.date_year
     , r.date_month
     , r.source
     , r.video
     , r.audio
     , r.pp
     , r.execsum
     , r.report_url
     , r.exec_url	  
  FROM user as u
INNER
  JOIN user_reports as p
    ON p.user_id = u.username
INNER
  JOIN emt_report as r
    ON r.id = p.report_id
WHERE username = '$username' AND MATCH(company) AGAINST ('$P_search' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC";
Here is my database schema:

Code: Select all

=================
user_reports
=================
user_id  
report_id

=================
user
=================
id
username

=================
emt_report
=================
id 
company 
description
market1
market2
market3 
market4 	
market5 	
market6 	
location 	
date_year 	
date_month 	
source 	
video 	
audio 	
pp 	
execsum 	
report_url 	
exec_url

Posted: Tue May 08, 2007 7:00 am
by volka
try

Code: Select all

MATCH(r.company)