[Resolvido] Select 2 Tabelas

[Bruno Batista 3]

Olá mestres, precis de uma pequena ajuda.

 

Preciso pegar o nome da cidade na tabela jos_iproperty_city.title sendo que tem que ta publicado na tabela iproperty_city.published

 

Minha query:

SELECT DISTINCT(city) AS value, city AS text FROM #__iproperty WHERE published = 1 AND city != '' ORDER BY city ASC

 

CREATE TABLE `jos_iproperty` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `mls_id` varchar(25) NOT NULL,
 `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `stype` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `stype_freq` varchar(200) NOT NULL,
 `listing_office` int(10) unsigned NOT NULL DEFAULT '0',
 `street_num` varchar(20) NOT NULL,
 `street` varchar(255) NOT NULL,
 `street2` varchar(255) NOT NULL,
 `apt` varchar(12) NOT NULL,
 `title` varchar(100) NOT NULL,
 `hide_address` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `short_description` varchar(500) NOT NULL,
 `description` text NOT NULL,
 `terms` text NOT NULL,
 `agent_notes` text NOT NULL,
 `city` varchar(55) NOT NULL,
 `locstate` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `province` varchar(255) NOT NULL,
 `postcode` varchar(20) NOT NULL,
 `region` varchar(255) NOT NULL,
 `county` varchar(55) NOT NULL,
 `country` smallint(5) unsigned NOT NULL DEFAULT '0',
 `latitude` varchar(255) NOT NULL DEFAULT '0',
 `longitude` varchar(255) NOT NULL DEFAULT '0',
 `gbase_address` varchar(255) NOT NULL,
 `concat_address` varchar(255) NOT NULL,
 `price` decimal(12,2) unsigned NOT NULL,
 `price2` decimal(12,2) unsigned NOT NULL,
 `call_for_price` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `show_address` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `beds` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `baths` decimal(4,2) unsigned NOT NULL DEFAULT '0.00',
 `reception` varchar(255) NOT NULL,
 `tax` varchar(100) NOT NULL,
 `income` varchar(100) NOT NULL,
 `sqft` int(10) unsigned NOT NULL DEFAULT '0',
 `lotsize` varchar(100) NOT NULL,
 `lot_acres` varchar(100) NOT NULL,
 `yearbuilt` varchar(20) NOT NULL DEFAULT '0',
 `heat` varchar(100) NOT NULL DEFAULT '0',
 `cool` varchar(100) NOT NULL,
 `fuel` varchar(100) NOT NULL DEFAULT '0',
 `garage_type` varchar(100) NOT NULL DEFAULT '0',
 `garage_size` varchar(100) NOT NULL DEFAULT '0',
 `zoning` varchar(100) NOT NULL DEFAULT '0',
 `frontage` smallint(5) unsigned NOT NULL DEFAULT '0',
 `siding` varchar(100) NOT NULL DEFAULT '0',
 `roof` varchar(100) NOT NULL DEFAULT '0',
 `propview` varchar(100) NOT NULL DEFAULT '0',
 `school_district` varchar(100) NOT NULL DEFAULT '0',
 `lot_type` varchar(100) NOT NULL DEFAULT '0',
 `style` varchar(100) NOT NULL,
 `hoa` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `reo` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `vtour` varchar(125) NOT NULL,
 `video` text NOT NULL,
 `gbase_url` varchar(100) NOT NULL DEFAULT '0',
 `gbase_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `hits` int(10) unsigned NOT NULL DEFAULT '0',
 `featured` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `metadesc` varchar(255) NOT NULL,
 `metakey` varchar(255) NOT NULL,
 `created` date NOT NULL DEFAULT '0000-00-00',
 `created_by` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `modified_by` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `access` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `publish_up` date NOT NULL DEFAULT '0000-00-00',
 `publish_down` date NOT NULL DEFAULT '0000-00-00',
 `published` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `checked_out` int(11) NOT NULL DEFAULT '0',
 `checked_out_time` time DEFAULT NULL,
 `ip_source` char(30) DEFAULT NULL,
 `listing_info` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `ip_source` (`ip_source`),
 KEY `listing_office` (`listing_office`),
 KEY `stype` (`stype`),
 KEY `city` (`city`),
 KEY `locstate` (`locstate`),
 KEY `postcode` (`postcode`),
 KEY `price` (`price`,`sqft`,`beds`,`baths`,`city`,`stype`)
) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

 

CREATE TABLE `jos_iproperty_city` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `state_id` tinyint(5) DEFAULT NULL,
 `mc_name` char(2) NOT NULL DEFAULT '',
 `title` varchar(255) NOT NULL DEFAULT '',
 `zip` varchar(255) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12422 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Default list of cities';

 

Desde ja agradeço!

[Bruno Batista 3]

Bom consegui resolver e ter o reseultado desejado:

 

$database->setQuery("SELECT DISTINCT(jos_iproperty_city.title) AS text, city AS value FROM jos_iproperty_city INNER JOIN jos_iproperty ON jos_iproperty.city = jos_iproperty_city.id WHERE jos_iproperty.published = 1 AND city != '' ORDER BY jos_iproperty_city.title ASC");

 

Agora se alguem tiver um jeito melhor agradeço.

Clique aqui e veja aonde achei a solução!

[Bruno Batista 3]

Melhorei ainda mais minha query:

 

$database->setQuery("SELECT DISTINCT(c.id), c.id AS value, c.title AS text FROM jos_iproperty_city AS c INNER JOIN jos_iproperty AS i ON c.id = i.city WHERE i.published = 1 AND i.city != '' ORDER BY c.title ASC");