two mySQL queries in one
Moderator: General Moderators
two mySQL queries in one
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?
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?
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);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;;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;;With that I got an ambiguous error [1052] but this works:
What if I also want to grab the actual name from the field Posts.Programmer?
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'";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'";Ahh, sorry reading it too fast, that gets a little more complicated, try this:
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).
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;;