Page 1 of 1

Merging 2 queries!!!

Posted: Thu May 20, 2004 4:44 pm
by [n00b]
Hello

I have troubles making a mySQL query (which I`m going to execute on a phpbb forum db)

I have 3 tables

1. topics
2. posts
3. users

What I want to get is the username of the first and last poster of a topic.

So "posts" table contains IDs, posts and their authors/posters` ID. "topics" table contains the id of the last and first poster`s IDs of each topic. Finally "users" table contains usernames and their IDs.

So, if I want to get the username of the first poster on a topic I use:

SELECT u.username FROM users as u, topics as t, posts as p
WHERE t.first_post_id=p.post_id AND p.poster_id=u.user_id

And when I want to get the username of the last poster, I use this:

SELECT u.username FROM users as u, topics as t, posts as p
WHERE t.last_post_id=p.post_id AND p.poster_id=u.user_id

Now, how can I get BOTH last poster and first poster in a topic? I mean, how to merge these 2 queries?

Any help appreciated! :)

Posted: Thu May 20, 2004 7:38 pm
by launchcode
Un-tested, but something like this:

Code: Select all

SELECT
u.username
FROM 
users as u, topics as t, posts as p
WHERE 
(t.first_post_id=p.post_id AND p.poster_id=u.user_id)
OR
(t.last_post_id=p.post_id AND p.poster_id=u.user_id)

Re: Merging 2 queries!!!

Posted: Tue May 25, 2004 2:58 am
by Radical
[n00b] wrote:I have 3 tables
1. topics
2. posts
3. users
You should somehow JOIN the tables.
I will give you the JOIN syntax if you'll provide me with:
1. every table structure
2. result structure

Posted: Fri May 28, 2004 9:18 am
by kendall
Radical,

dont mean to hijack the post or anything but i myself am having a similar predicament of wanting to do 2 queries on two table.

Im really havin a hard time understanding JOINS however i think that my situation doesnt require it.

my problem is viewtopic.php?t=21991

Now the end result should be that i get results from the "product" table based on resuls from the "features table" however its the order of the information is what makes the results come out incorrect. I think i may be using the tables in the wrong sequence for the query but im not quite sure what my approach should be.

Any thoughts on this would be appreciated.

Kendall

Posted: Fri May 28, 2004 3:07 pm
by McGruff
For info on JOIN queries et al check out the free chapter 2 pdf of Java Database Best Practiceshttp://www.oreilly.com/catalog/javadtabp/.

There are different types of JOINs. Usually you want an INNER JOIN (see mysql manual for more info).

In this example, table1 and table2 both have an "aid" col; table2 and table3 share a "bid" col. The aid and bid cols are used for the JOIN. You can string as many tables together as you like.

Code: Select all

$mysql =  "SELECT table1.col1, table2.col1, table3.col1, ..etc
                  FROM table1 
                  INNER JOIN table2 USING(aid)
                  INNER JOIN table3 USING(bid)
                  WHERE ї...]";