Merging 2 queries!!!

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
[n00b]
Forum Commoner
Posts: 34
Joined: Sat Mar 20, 2004 7:06 pm

Merging 2 queries!!!

Post 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! :)
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post 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)
Radical
Forum Newbie
Posts: 3
Joined: Tue May 25, 2004 2:52 am
Location: Bucharest, Romania
Contact:

Re: Merging 2 queries!!!

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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 ї...]";
Post Reply