Link to home
Create AccountLog in
Avatar of Caesar2k
Caesar2kFlag for Brazil

asked on

MYSQL SELECT Query on 5 tables

Hello experts, I have a big problem on my hands.
I currently have 5 tables, that are interconnected. I'm selecting from a table that I'm using for fulltext search, that is the table "veiculos". This table is the following:

CREATE TABLE `veiculos` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `owner` int(10) unsigned default NULL,
  `modelo` varchar(64) collate utf8_unicode_ci default NULL,
  `marca` varchar(64) collate utf8_unicode_ci default NULL,
  `capacidade` tinyint(3) unsigned default NULL,
  `condutor` int(10) unsigned default NULL,
  `valor` varchar(20) collate utf8_unicode_ci default '0;0;0;',
  `forma_pagamento` tinyint(3) unsigned default NULL,
  `bairros` text collate utf8_unicode_ci,
  `locais` text collate utf8_unicode_ci,
  `license` varchar(20) collate utf8_unicode_ci default NULL,
  `turnos` varchar(20) collate utf8_unicode_ci default NULL,
  `extra` text collate utf8_unicode_ci,
  `cidade` int(11) default NULL,
  `estado` int(11) default NULL,
  `tipo` tinyint(4) default NULL,
  PRIMARY KEY  (`id`),
  KEY `motorista` (`condutor`),
  KEY `owner` (`owner`),
  KEY `cidade` (`cidade`),
  KEY `estado` (`estado`),
  KEY `tipo` (`tipo`),
  FULLTEXT KEY `bairros` (`bairros`,`locais`,`extra`,`turnos`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The key fields (motorista, owner, cidade, estado, tipo) are matched against other 4 tables, to retrieve the actual text for the corresponding row. An example row from the table "veiculos":

1      1      Sprinter      Mercedes      16      1      0;0;0;      0      Anchieta, Barragem Santa Lucia, Cidade Jardim, Cruzeiro, Funcionários, Luxemburgo, Mangabeiras, Santo Antônio, São Bento, São Pedro, Serra      PUC Coração Eucarístico      Liminar      Manhã Tarde Noite NULL 24100      13      1

Notice how the "cidade" row is an integer, and the "estado" is an integer too, that doing a join to the other tables, would return the values there. Now the part that is driving me crazy. I'm using Fulltext search to search on this table, BUT MATCH() AGAINST() asks for column names, I can't use an ALIAS there, I can't use a CONCAT(), not even subqueries using SELECT. So how an I supposed to search the name of "states" (estados) and "cities" (cidades) from the other tables? I was using CONCAT_WS on every field, and for a search engine, it's toooo slow (and has no relevance matching)
SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account