Jump to content

Archived

This topic is now archived and is closed to further replies.

ronnie482

need help displaying records from 2 tables

Recommended Posts

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

eg: homes
[code]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;
[/code]
and

[code]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 ;[/code]
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

[code]"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"[/code]
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.jaggijaswal.com/admin/display.php it displays two house with mls c32268781 while there should be one and heres the code http://www.jaggijaswal.com/admin/display.txt

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

Share this post


Link to post
Share on other sites
Add... (at the end of your query)

[code] GROUP BY h.mls[/code]


me!

Share this post


Link to post
Share on other sites
GROUP BY masks the actual problem -- you just want any image, right? You should probably use a LIMIT clause instead

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.