Search query from 2 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Search query from 2 different tables

Author

Topic

rdl
Starting Member

25 Posts

Posted - 2011-06-26 : 08:21:40

Hi, I have the following code:

$result = mysql_query("SELECT * FROM report
WHERE
ReportRef LIKE '%" . $search . "%'
or Town LIKE '%" . $Town . "%'
or Category LIKE '%" . $Category . "%'
or ReportStatus LIKE '%" . $ReportStatus . "%' ");

$result = mysql_query ("SELECT ID FROM reportperson WHERE ID LIKE '%" . $ID. "%');

Can someone tell me how I can do the search from different tables as i am having errors when coming to display above results.

Thanks,
rdl

rdl
Starting Member

25 Posts

Posted - 2011-06-26 : 08:22:57

Note that table 'reportperson' has ReportRef as a foreign key to connect it to table 'report'.

rdl
Starting Member

25 Posts

Posted - 2011-06-26 : 09:12:21

Problem solved :)
$result = mysql_query (" SELECT * from report r INNER JOIN reportperson rp
ON r.ReportRef = rp.ReportRef
INNER JOIN reportofficer ro
on rp.ReportRef = ro.ReportRef
LEFT JOIN reportvehicle rv
on r.ReportRef = rv.ReportRef
WHERE (
r.ReportRef LIKE '%" . $search . "%' or
r.Town LIKE '%" . $Town . "%' or
r.Category LIKE '%" . $Category . "%' or
r.ReportStatus LIKE '%" . $ReportStatus . "%' or
rp.ID LIKE '%" . $ID . "%'
)
");

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources