Jump to content


Photo

need help displaying records from 2 tables


  • Please log in to reply
4 replies to this topic

#1 ronnie482

ronnie482
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 02 October 2006 - 10:46 PM

I've two tables "homes" and "images"

eg: homes
CREATE TABLE `homes` (
  `mls` varchar(11) collate latin1_general_ci NOT NULL,
  `address` varchar(255) collate latin1_general_ci NOT NULL,
  `property_type` tinytext collate latin1_general_ci,
  `house_style` tinytext collate latin1_general_ci,
  `garage` varchar(255) collate latin1_general_ci default NULL,
  `age` int(11) default NULL,
  `bedrooms` tinyint(4) default NULL,
  `washrooms` varchar(255) collate latin1_general_ci default NULL,
  `features` text collate latin1_general_ci,
  `house_size` int(11) default NULL,
  `price` varchar(11) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`mls`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
and

CREATE TABLE `images` (
  `id` int(11) NOT NULL auto_increment,
  `mls` varchar(11) collate latin1_general_ci NOT NULL,
  `name` varchar(50) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;
there are more than one images for each mls. i just want to display all the house in "homes" table with one image for each home from "images" table. im trying this query

"SELECT h.mls, h.address, h.property_type, h.house_style, h.garage, h.age, h.bedrooms, h.washrooms, h.features, h.house_size, h.price, i.name FROM homes AS h, images AS i WHERE h.mls = i.mls"
with this im getting descripton for all the images in "images" table while I want just one image for each house.

u can also take a look here on code

u take a look here http://www.jaggijasw...min/display.php it displays two house with mls c32268781 while there should be one and heres the code http://www.jaggijasw...min/display.txt

it displays 2 homes with mls c32268781 while I want just one. please help

#2 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 02 October 2006 - 11:37 PM

Add... (at the end of your query)

GROUP BY h.mls


me!

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 12:05 AM

GROUP BY masks the actual problem -- you just want any image, right? You should probably use a LIMIT clause instead
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 03 October 2006 - 12:38 AM

Why would GROUP BY mask the problem, GROUP BY selects (1) row, LIMIT selects ALL rows then LIMITS the selection! Both use a reference based on what is in the WHERE clause, so there no difference what so ever!

me!

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 02:42 PM

First, limit does _not_ select all rows... only as many rows as specified are returned.

Second, with GROUP BY, you're going to get an arbitrary row from the images table, which is not what the poster means, even it will suffice.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users