[SOLVED] How would I join these?

[tonyawelch]

    * your MySQL server version

 

5.0.75

 

    * the raw MySQL statement in question [in a CODE block, and without any PHP variables]

 

I'm trying to join these two queries:

 

"SELECT * FROM news WHERE news_id = $_GET[news_id]" (which will give me the user_id of the person who posted the news...)

 

to

 

"SELECT first_name, email FROM users WHERE user_id = $user_id" ($user_id being the value returned by the first query.)

 

What I tried was this:

 

"SELECT news.*, users.first_name, users.email FROM news, users WHERE news.news_id = ($_GET[news_id])"

 

Of course, at the time it didn't dawn on me that this query would return a random first_name and email because I don't specify anywhere that I want the first_name and email from the user who posted this particular news item.

 

 

 

    * any errors that MySQL returns to the client [from mysql_error()]

 

None - script runs fine, just returns incorrect data.

 

    * the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred]

 

Users Table:

 

CREATE TABLE `users` (

`user_id` int(10) unsigned NOT NULL auto_increment,

`first_name` varchar(30) NOT NULL,

`last_name` varchar(30) NOT NULL,

`dob` varchar(10) NOT NULL,

`email` varchar(40) NOT NULL,

`user_name` varchar(30) NOT NULL,

`password` varchar(41) NOT NULL,

`tandc` char(1) NOT NULL,

`r_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`confirmed` varchar(10) NOT NULL,

`access` tinyint(1) unsigned NOT NULL default '1',

PRIMARY KEY  (`user_id`)

) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

 

News Table

 

CREATE TABLE `news` (

`news_id` int(10) unsigned NOT NULL auto_increment,

`user_id` int(10) unsigned NOT NULL,

`newscat_id` int(10) unsigned NOT NULL,

`news_headline` varchar(80) NOT NULL,

`news_content` mediumtext NOT NULL,

`submitted` datetime NOT NULL,

`confirmed` char(1) NOT NULL,

PRIMARY KEY  (`news_id`)

) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

 

 

 

    * a clear and concise description of what you want this statement to achieve

 

I'm new at the JOIN queries - have always just done multiple queries to get the data I need.  I would like to be able to join queries to get all of the data for an entry in the news table and the first name and last name of the user who posted that entry.  I'm sure it's something easy and I just haven't seen it in a tutorial yet so can't wrap my head around the logic.

 

    * a description of what it's currently doing that's not to your liking

 

It currently returns information on the last user in the database.

 

    * a brief listing of the types of things you've attempted so far

 

I tried this:

 

"SELECT news.*, users.first_name, users.email FROM news, users WHERE news.news_id = ($_GET[news_id]) AND news.user_id = users.user_id"

 

But again, I'm not clarifying that I need the user associated with this particular news_id.

 

Thanks for all of your help!!

 

Tonya

 

[Daniel0]

SELECT news.*, users.firstname, users.email
  FROM news
  INNER JOIN users ON news.user_id = user.user_id
  WHERE news.news_id = 123;

[tonyawelch]

Wow, thanks so much

 

Can anyone recommend a tutorial somewhere that very clearly explains the JOIN queries?  I've read two now and don't seem to be getting it.  Neither included INNER JOIN as opposed to just JOIN, so I'm assuming that's something more advanced than the tutorials I've read.

 

Thanks again!

 

Tonya

[Daniel0]

We have this one: http://www.phpfreaks.com/tutorial/data-joins-unions

 

Otherwise there is the manual on JOINs: http://dev.mysql.com/doc/refman/5.0/en/join.html