MySQL - Getting search results from table and referencing tables

Question

Hey everyone,

(Using MySQL and PHP)

I have a search form that will allow my users to type in a string, and search that string on a particular criteria.

My problem is that a user needs to be able to search for information that is "spread" across multiple tables. For example:

-Table "users" contains fname, lname, role, username (PK)

-Table "resident assistant" contains username (FK to users), building, room, region

-Table "area coordinator" contains username (FK to users), office_bldg, office_num

And I am allowing my users to search by First Name, Last Name, Building, Region, Office # - So I will need to show results that span across multiple tables (i.e. matching records from "users" and "resident assistant")

I've been experimenting with Joins and Unions, but haven't quite gotten anything useful. I am looking for the most "Universal" SQL statment to handle any search, if that's possible.

Right now, the only way I can think of doing these searches is by a lot of processing in the PHP, for example, to find a First Name, have a query that returns username, role from "users", and then have a bunch of if statements saying, "if role is this, then search this table where username equals that..."

Is there a better way to do this? Thanks!

---

Vinko-

I am actually not getting an error, the query (with multiple joins) is just returning 0 rows.

Here is an example query that I am using:

select u.fname, u.lname, u.role, u.username, r.building, r.room, r.region, 
a.office, a.office_num
from 
users u 
join `ra_ca` r on (u.username = r.username) 
join `area_coord` a  on (u.username = a.username)
where
u.username = 'behrk2' and r.region = '4'

And here are my table structures:

CREATE TABLE `users` (
 `fname` varchar(50) NOT NULL,
 `lname` varchar(50) NOT NULL,
 `role` varchar(75) NOT NULL,
 `extension` int(4) default '6226',
 `username` varchar(25) NOT NULL,
 `password` varchar(75) NOT NULL,
 `new_pass` varchar(5) default NULL,
 PRIMARY KEY  (`username`),
 KEY `role` (`role`),
 CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`role`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `ra_ca` (
 `username` varchar(25) NOT NULL,
 `region` tinyint(4) NOT NULL,
 `building` varchar(75) NOT NULL,
 `room` varchar(10) NOT NULL,
 PRIMARY KEY  (`username`),
 KEY `region` (`region`),
 KEY `building` (`building`),
 CONSTRAINT `ra_ca_ibfk_9` FOREIGN KEY (`building`) REFERENCES `building` (`building`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `ra_ca_ibfk_7` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `ra_ca_ibfk_8` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `area_coord` (
 `username` varchar(25) NOT NULL,
 `region` tinyint(4) NOT NULL,
 `building` varchar(75) NOT NULL,
 `room` varchar(10) NOT NULL,
 `office` varchar(75) NOT NULL,
 `office_num` varchar(10) NOT NULL,
 PRIMARY KEY  (`username`),
 KEY `region` (`region`),
 KEY `building` (`building`),
 CONSTRAINT `area_coord_ibfk_9` FOREIGN KEY (`building`) REFERENCES `building` (`building`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `area_coord_ibfk_7` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `area_coord_ibfk_8` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And I do have values in the DB...

Thanks!

Answer 1

With something like the following you'll only need to build in code the where clause. This is, the last line of the query.

select u.fname, u.lname, u.role, u.username, r.building, r.room, r.region, 
a.office_bldg, a.office_num
from 
users u 
join `resident assistant` r on (u.username = r.username) 
join `area coordinator` a  on (u.username = a.username)
where
u.username = 'foo' and r.region = 'China'

EDIT:

It seems to me that you want all results no matter if there are values in all joined tables. So try left joins instead of inner joins. Try reading up on SQL to know WHAT are these queries doing.

select u.fname, u.lname, u.role, u.username, r.building, r.room, r.region, 
a.office_bldg, a.office_num
from 
users u 
left join `resident assistant` r on (u.username = r.username) 
left join `area coordinator` a  on (u.username = a.username)
where
u.username = 'foo' and r.region = 'China'