two mySQL queries in one

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
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

two mySQL queries in one

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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);
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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 = &quote;SELECT `Name`, `Title` FROM `Posts` JOIN `Posts` ON (Posts.Webmaster=Users.id); WHERE `Start` IS NOT NULL AND `END` = '0'&quote;;
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Remove the semi-colon in the middle and that should work.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

1066: Not unique table/alias: 'Posts'
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Code: Select all

$sql = &quote;SELECT `Name`, `Title` FROM `Posts` JOIN `Users` ON (Posts.Webmaster=Users.id) WHERE `Start` IS NOT NULL AND `END` = '0'&quote;;
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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'";
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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'&quote;;
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).
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post by dreamline »

I'd do it this way:

select * from posts pst, users usr where pst.id=usr.id

:)
Post Reply