--- In [email protected], Shahu <shaha...@...> wrote: > > On Thu, May 20, 2010 at 7:55 PM, pirate_lenny <piratele...@...> wrote: > > > > > > > I've got two tables that I'd like to search. Here is how I've got it set > > up: > > > > There is a property table. Each property can have multiple names, with one > > name designated as the main name. So there is a property name table as well. > > But I want the user to be able to search by property names and the result to > > display as: > > > > Main Property Name > > a.k.a. Non-Main Property Name 1 > > a.k.a. Non-Main Property Name 2 > > > > I'm having problems doing this without multiple queries. > > > > Right now, I've got one query that searches the names. Then another query > > that takes the found property ID and finds that property. The property is > > displayed with the main property name. Then there is another query to find > > the aka's. > > > > I have to repeat this type of thing on the search results page as well for > > people and then people with multiple names. That makes 8 queries to display > > the search results. Plus more in the future as we add different options for > > searching the site. > > > > Is there a better way to do this? Can someone point me in the right > > direction? > > > > do u mean this? -- > > tables - > CREATE TABLE `mainproperty` ( > `pid` int(11) NOT NULL AUTO_INCREMENT, > `pname` varchar(255) NOT NULL, > PRIMARY KEY (`pid`) > ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 > > CREATE TABLE `subproperty` ( > `subid` int(11) NOT NULL AUTO_INCREMENT, > `pid` int(11) NOT NULL, > `subname` varchar(255) NOT NULL, > PRIMARY KEY (`subid`) > ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 > > inserting data - > > INSERT INTO `mainproperty` (`pid`, `pname`) VALUES > (1, 'one'), > (2, 'two'), > (3, 'three'), > (4, 'four'); > > > INSERT INTO `subproperty` (`subid`, `pid`, `subname`) VALUES > (1, 1, 'one-one'), > (2, 1, 'one-two'), > (3, 1, 'one-three'), > (4, 2, 'two-one'), > (5, 2, 'two-two'), > (6, 2, 'two-three'), > (7, 3, 'three-one'), > (8, 3, 'three-two'), > (9, 4, 'four-one'); > > > query - > select m.pname,s.subname from mainproperty m inner join subproperty s on > m.pid=s.pid; > > regards, > shahu > > > > > > > > -- > http://phpinterviewanswers.blogspot.com/ > > > [Non-text portions of this message have been removed] >
Shahu - Thanks for the answer. Unfortunately, I don't think that that will work. My properties/names tables are set up like PROPERTIES propid (int) propname (int) PROP NAMES propnameid (int) propname (var char) propno (int) propid is the primary key in Properties. Propname is the main name assigned to that property. propnameid is the primary key for Prop Names (and is placed in propname to indicate which name is primary). It's then displayed using a join. propno is used to relate all of the names to the property. Are you suggesting that I should be placing the main property name in the properties table? That would be easier to search? Jeremy
