dot notations, joins, and more fun

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

dot notations, joins, and more fun

Post by bimo »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I am trying to do a join using dot notation and it isn't working out very well.  I am selecting from my videos table and then when a foreign key is in a row, such as person_id, I am trying to get the person's name from the person table.  Here's my query:

Code: Select all

$query = "select
v.videos_id,
p.name,
v.video_thumb,
l.location_id,
v.date_ed
from
videos as v,
people as p,
locations as l,
crises as c
where
v.crisis_id = $id
and
p.person_id = v.person_id
and
l.location_id = v.location_id
and
c.crisis_id = v.crisis_id
order by
year(v.date_rec),
month(v.date_rec),
day(v.date_rec)
limit 10";
If I remove

Code: Select all

and
p.person_id = v.person_id
and
l.location_id = v.location_id
and
c.crisis_id = v.crisis_id
It returns a three results and repeats them three times so I slao tried

Code: Select all

select distinct
v.videos_id..."  but then it returned no results.  

I'm sorry if this is a stupid question...

oh, and let me know if you would like the table schemas.

Thanks,

b

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

...sorry. I didn't notice the un closed code tag. the end of my last post should read:

there is one returned result set but they are repeated three times so I also tried

Code: Select all

select distinct 
v.videos_id...


but then it returned no results.

I'd really appreciate any advice that could be offered. Please let me know if you would like the table schemas.

Thanks
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

why do not you use joins which are more easy to understand??? I have not used where to replace join for more than one level....please use backticks for your SQL query...

please post tables schemas...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you try this...

Code: Select all

$query = "
SELECT 
	v.videos_id, 
	p.name, 
	v.video_thumb, 
	l.location_id, 
	v.date_ed 
FROM 
(
	(
		(
			people p 
			INNER JOIN videos v 
			ON p.person_id = v.person_id
		)
		INNER JOIN locations l 
		ON l.location_id = v.location_id
	)
)
INNER JOIN crises c
ON c.crisis_id = v.crisis_id
WHERE 
	v.crisis_id = $id
ORDER BY
	year(v.date_rec),
	month(v.date_rec),
	day(v.date_rec)
LIMIT 0, 10";
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

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)?
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

...er, it sort of works... it returns the city and state name twice...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

if you want to retrieve all videos for crisis_id = 1 and retrieve the name of the person and where he is living...here is the query

Code: Select all

select distinct v.`videos_id`, v.`vidoes_thumb`, p.`name` as `PersonName` , pr.`name` as `ProvinceName`, c.`name` as `CityName` 
from 
	`videos` v
left join `people` p on 
	p.`person_id` = v.`person_id`
left join `locations` l on 
	l.`location_id` = v.`location_id`
left join `provinces` pr on 
	pr.`state_province_id` = l.`state_province_id`
left join `cities` c on 
	c.`city_id` = l.`city_id`
where 
	v.`crisis_id` = 1
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`);
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the order by can be simplified to

Code: Select all

ORDER BY
  v.`date_rec` ASC
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

Thanks! Works like a charm.
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

What does the "as ProvinceName... as PersonName" do?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

aliasing.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

bimo wrote:What does the "as ProvinceName... as PersonName" do?
just provides more meaningful names for fields in tables....you might sometimes compulsarily need this when two tables have same field names and you to retrieve them and display in PHP.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

or in usage such as

Code: Select all

SELECT COUNT(*) as `rowCount` ..
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

Thanks for all your help.
Post Reply