Page 1 of 1
two mySQL queries in one
Posted: Sat Jul 30, 2005 1:42 pm
by Todd_Z
I have a database of users with a unique id for each.
I have a database of projects with references to the users by id.
I want to query the db of projects but instead of getting back that the owner is #25, i want to query the user db for the name of user #25.... how is this done?
Posted: Sat Jul 30, 2005 1:46 pm
by nielsene
I don't know what your schema looks like, but this is the general idea.
Code: Select all
SELECT projectname, username
FROM users JOIN
projects ON (projects.owner=users.id);
Posted: Sat Jul 30, 2005 1:54 pm
by Todd_Z
Code: Select all
CREATE TABLE Users (
Name tinytext NOT NULL,
Password varchar(32) NOT NULL default '',
Days smallint(5) NOT NULL default '0',
Certified_Days smallint(6) NOT NULL default '0',
Projects smallint(6) NOT NULL default '0',
Email text NOT NULL,
Start timestamp(14) NOT NULL,
Skills tinytext NOT NULL,
Pricing double NOT NULL default '0',
Last_Load timestamp(14) NOT NULL,
ID int(10) unsigned NOT NULL auto_increment,
UNIQUE KEY ID (ID)
) TYPE=MyISAM;
Code: Select all
CREATE TABLE Posts (
Title tinytext NOT NULL,
Webmaster bigint(20) NOT NULL default '0',
Programmer bigint(20) NOT NULL default '0',
Skills tinytext NOT NULL,
Budget tinytext NOT NULL,
Limits tinytext NOT NULL,
Start timestamp(14) NOT NULL,
End timestamp(14) NOT NULL,
Length int(3) unsigned NOT NULL default '0',
Top_Priority char(1) NOT NULL default '0',
ID bigint(20) NOT NULL auto_increment,
UNIQUE KEY ID (ID)
) TYPE=MyISAM;
Code: Select all
$sql = "e;SELECT `Name`, `Title` FROM `Posts` JOIN `Posts` ON (Posts.Webmaster=Users.id); WHERE `Start` IS NOT NULL AND `END` = '0'"e;;
Posted: Sat Jul 30, 2005 1:55 pm
by nielsene
Remove the semi-colon in the middle and that should work.
Posted: Sat Jul 30, 2005 2:07 pm
by Todd_Z
1066: Not unique table/alias: 'Posts'
Posted: Sat Jul 30, 2005 2:10 pm
by nielsene
Code: Select all
$sql = "e;SELECT `Name`, `Title` FROM `Posts` JOIN `Users` ON (Posts.Webmaster=Users.id) WHERE `Start` IS NOT NULL AND `END` = '0'"e;;
Posted: Sun Jul 31, 2005 12:57 pm
by Todd_Z
With that I got an ambiguous error [1052] but this works:
Code: Select all
$sql = "SELECT `Name`, `Title` FROM `Posts` JOIN `Users` ON (Posts.Webmaster=Users.id) WHERE Posts.Start IS NOT NULL AND `END` = '0'";
What if I also want to grab the actual name from the field Posts.Programmer?
Posted: Sun Jul 31, 2005 1:01 pm
by nielsene
Code: Select all
$sql = "SELECT `Name`, `Title`, `Programmer` FROM `Posts` JOIN `Users` ON (Posts.Webmaster=Users.id) WHERE Posts.Start IS NOT NULL AND `END` = '0'";
Posted: Sun Jul 31, 2005 3:48 pm
by Todd_Z
I mean I want to get the name from the db too, don't i need it to be like Posts.Programmer=Users.id or something?
Posted: Sun Jul 31, 2005 3:58 pm
by nielsene
Ahh, sorry reading it too fast, that gets a little more complicated, try this:
Code: Select all
SELECT `Title`, `p.Name`, `w.Name`
FROM `Posts` JOIN
`Users` AS p ON (Posts.Webmaster=w.id) JOIN
`Users` AS w ON (Posts.Programmer=p.id)
WHERE Posts.Start IS NOT NULL AND `END` = '0'"e;;
We have to join to the User's table twice, once for each field. And to avoid the column-collisiosn we give eash on an alias (the AS p and AS w).
Posted: Mon Aug 01, 2005 6:01 am
by dreamline
I'd do it this way:
select * from posts pst, users usr where pst.id=usr.id
