I tried your query, Everah, and unfortunately, it didn't work but I thought about what raghavan20 said about the query being difficult to read and have been trying to simplify it. I don't think what I'm trying to do should be difficult. I have a table, videos, that I want to get records where crisis_id =
x. (create-tables all shortened to only the relevant columns)
Code: Select all
DROP TABLE IF EXISTS `videos`;
CREATE TABLE `videos` (
`videos_id` int(10) unsigned NOT NULL auto_increment,
`crisis_id` int(10) unsigned default '0',
`location_id` int(10) unsigned default NULL,
`person_id` int(10) unsigned default NULL,
`date_rec` datetime NOT NULL default '0000-00-00 00:00:00',
`date_ed` datetime default NULL,
`video_thumb` varchar(45) default NULL,
PRIMARY KEY (`videos_id`),
KEY `video_FKIndex1` (`location_id`),
KEY `video_FKIndex2` (`person_id`),
CONSTRAINT `videos_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `videos_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `people` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
but for person I want to show the names, not the id
Code: Select all
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`person_id` int(10) unsigned NOT NULL auto_increment,
`conditions_id` int(10) unsigned default NULL,
`name` varchar(60) default NULL,
PRIMARY KEY (`person_id`),
KEY `person_FKIndex1` (`conditions_id`),
KEY `person_FKIndex2` (`location_id`),
CONSTRAINT `people_ibfk_1` FOREIGN KEY (`conditions_id`) REFERENCES `conditions` (`conditions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `people_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and same with locations but the locations table references a cities and states_provinces table so I want to return name from cities and name from states:
Code: Select all
DROP TABLE IF EXISTS `cities`;
CREATE TABLE `cities` (
`city_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) NOT NULL default '',
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `states_provinces`;
CREATE TABLE `states_provinces` (
`state_province_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) NOT NULL default '',
`abbrev` varchar(4) default NULL,
PRIMARY KEY (`state_province_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(255) unsigned NOT NULL auto_increment,
`areas_id` int(10) unsigned default NULL,
`country_id` int(10) unsigned default NULL,
`state_province_id` int(10) unsigned default NULL,
`city_id` int(10) unsigned default NULL,
`address` varchar(255) NOT NULL default '',
PRIMARY KEY (`location_id`),
KEY `location_FKIndex2` (`city_id`),
KEY `location_FKIndex3` (`state_province_id`),
KEY `location_FKIndex4` (`country_id`),
KEY `areas_id` (`areas_id`),
CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`areas_id`) REFERENCES `areas` (`areas_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `locations_ibfk_2` FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `locations_ibfk_3` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `locations_ibfk_4` FOREIGN KEY (`state_province_id`) REFERENCES `states_provinces` (`state_province_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I rewrote my original query like this:
Code: Select all
select
distinct
v.videos_id,
(select p.name from people as p where p.person_id = v.person_id),
v.video_thumb,
l.location_id,
(select c.name from cities as c where c.city_id = l.city_id),
(select s.name from states_provinces as s where s.state_province_id = l.state_province_id)
from
videos as v,
people as p,
locations as l,
cities as c,
states_provinces as s
where
v.crisis_id = 1
and
l.location_id = v.location_id
order by
year(v.date_rec),
month(v.date_rec),
day(v.date_rec),
hour(v.date_rec),
minute(v.date_rec),
second(v.date_rec)
and it finally works but is there a better way of writing it without using those subqueries (select distinct, blah, (select blah), blah... etc)?