multiple tables in where clause

[interviz]

I am having a problem with a search script I have written to search a database.

 

Each database record has its main record in the table Defect_Details, and has a number of records  associated with the record in Defect_Details in a table called Defect_Audit

 

I am trying to write a search facility that will search the either the title of a defect in the Defect_Details table, or both the Defect_Title field in Defect_Details and the Defect_Info field in the Defect_Audit table

The tables are linked by a field called Defect_ID, which is the primary key on the details table, and a secondary key on the audit table

 

Have the switch statement working to differentiate between the type of search, and have the sql working for the single table query

 

select * from defect_detail where (Defect_Title like '%$searchtext%')

 

but I am really struggling with the SQL syntax for the more complex search across both tables

 

I roughly want to do

 

select distinct * from defect_detail where ((Defect_Title like '%$searchtext%') or (defect_audit.Defect_Info like '%$searchtext%'))

 

suspect I may need some kind of join in here, but have not managed to get it working despite pulling my hair out for a good few hours

 

Any suggestions greatly appreciated

 

 

[fenway]

Actually, you'll need a union -- write each query separately, and then wrap them in parens, and add a UNION ALL.

[interviz]

Thanks - that did help. The query as it stood did not work with the * because the structure of the two tables is different, so it threw an error. If I used the common field Defect_ID it did quite happily return the correct numbers and the search is working - its just not populating the fields apart from the defect_id anymore.

 

Would the best way of dealing with this problem be to put the Defect_ID values in an array and then step through it, pulling in the details from each record as I go?

[fenway]

Actually, you can "merge" the two tables in a number of ways -- you can just ask for the matching fields back, or mark the ones from each table/query different and figure it out later.