mySQL query question

What's the best way to turn two queries into one?

[mek2600]

Hello everyone. I'm new to these boards so thanks for all you help in advance.

My question is both about mySQL and PHP. I have two tables and the first one contains 2 main fields- 'user_id' and 'username'. The second table contains 2 main fields- 'poster_id' and 'message'.

And what I need to do is lookup the user_id from the first table with the username, then pull * from the message_table if the poster_id matches the value pulled from the first table.

Now, I got this working without a problem at all but I had to use 2 queries to do it (1- pull the user_id from the first table by matching it with the username, then 2- using the first id, pull everything from the second table that matches it). Is there an easy way to do those 2 steps in 1 query? I know there must be some way since this seems like a fairly simple and routine problem, but I can't find it online or in any books I have here.

Any help would be appreciated. Thanks again.

Michael Kennedy

[coopster]

Welcome to WebmasterWorld, mek2600!

$sql =  
 "SELECT * from message_table  
 INNER JOIN [mysql.com] user_table ON message_table.poster_id = user_table.user_id  
 WHERE username = '$username'"; 

[mek2600]

I looked at your example and looked at the information from mysql.com and couldn't figure out how to structure my slightly more complicated version of that query. It looks to me like subqueries is really what I might be wwanting since I also like the way they're laid out.

What's wrong with this quesry which should do the same thing?

$query = "SELECT * FROM $message_table WHERE `section` = '$section' AND `poster_id` = (SELECT `user_id` FROM '$user_table' WHERE `username` = '$member' LIMIT 1) SORT BY date DESC";

That seems to follow the syntax correctly, but I can't figure out why it isn't returning any values.

The two queries that I'd like to combine are:

$query = "SELECT * FROM $user_table WHERE `username` = '$member' LIMIT 1";

$query = "SELECT * FROM $message_table WHERE `poster_id` = '$user_data[user_id]' AND `section` = '$section' ORDER BY date DESC";

I hope that's understandable. Any more help is appreciated since I'm wanting to make things as efficient as possible.

-Michael Kennedy

[mek2600]

[edit]

Even an upgrade to 4.1.1-alpha-standa doesn't seem to help things. Any ideas on why that query isnt getting me anywhere?

MK

[edited by: mek2600 at 7:36 pm (utc) on Dec. 23, 2003]

[coopster]

If you aren't retrieving any fields from the first table, why even read from it? I'm a bit unclear on what you are trying to accomplish. Also, you could use a LEFT JOIN as opposed to an INNER JOIN if you want but if there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table, which in your case would be the data you want returned. Therefore, I figured if there wasn't anything, don't return anything -- go with an INNER JOIN.

Don't surround your table columns with quotation marks, you'll get a syntax error and your query will not be executed successfully. Assuming your

user_table

has unique

username

entries, you won't use the LIMIT clause:

$sql =  
 "SELECT * FROM message_table  
 INNER JOIN user_table ON message_table.poster_id = user_table.user_id  
 WHERE username = '$member' AND section = '$section' ORDER BY date DESC";  

If you only want a couple of fields from one table, but all fields from another:

SELECT table1.field1, table1.field2, table2.* FROM ... 

This says, return field1 and field2 from the first table and all the fields from the second table.

[mek2600]

That worked perfectly and I'll study inner joins a bit so I don't have to ask the same question again. :)

Thanks a bunch. From what I've read, inner joins can be replaced with subqueries and I kinda like the syntax of subqueries better (nevermind that fact that I havent yet gotten one to work- that's just a minor detail). Besides the fact that subqueries are only supported in versions newer than 4.1, is there a reason to not use subqueries? They seem to be a lot more intuitive and simpler (more simple?) to understand.

MK

[coopster]

Can't learn if we don't ask, right? You're welcome. Personally, I prefer subqueries/subselects. I think that whoever wrote the MySQL Manual regarding Subquery Syntax [mysql.com] put it best:

The main advantages of subqueries are:

• They allow queries which are structured so that it's possible to isolate each part of a statement.
  
• They provide alternative ways to perform operations which would otherwise require complex joins and unions.
  
• They are, in many people's opinion, readable. Indeed, it was the innovation of subqueries which gave people the original idea of calling the early

  SQL

  "Structured Query Language".

[mek2600]

OK, now I'm getting somewhere... The following query works perfectly:

$query = "SELECT * FROM $message_table WHERE section = '$section' AND poster_id = (SELECT user_id FROM $user_table WHERE username = '$member' LIMIT 1) ORDER BY date DESC";

My problem was stemming from me using "SORT BY" instead of "ORDER BY". I'll just have to look up the difference since "SORT BY" has always worked for me in every case except this one.

Thanks a lot for the help, coopster. I can finally move on to another problem now. :)

MK