asked on

MySQL DISTINCT, GROUP BY on multipule mysql tables.

Hello,

I want to select distinct values from posts.

In the posts table there are:

pst_id - this is the post id for each posted item.
mem_id - this is the member id.
top_id - this is the topic id that the post (pst_id) is under.
posted this is the time the post (pst_id) was posted.

I have no problem with this part (However i suspect i need to GROUP BY rather then DISTINCT)

$query = "SELECT DISTINCT pst_id, mem_id, posted FROM posts ORDER BY posts.posted DESC LIMIT 0, 10";

It outputs:

Latest Forum Posts
Author - Title - Time
100002 100003 8:14 am
100003 100002 5:12 am
100003 100001 5:04 am
100000 100000 8:59 pm

Now heres the part I cant seem to do. I want the Author values (10002 etc&) to look in the members table and output the persons name rather than their 'mem_id'.

The members table looks like this:

mem_id: 100002
username: Joe
mem_id: 100003
username: Jill
etc&

and I want the Title value (10003 etc&) to look in the topics table and output the topic subject title.

The topics table looks like this:

top_id: 100003
mem_id: 100002
subject: Do you like the color blue?
top_id: 100002
mem_id: 100003
subject: Do you like the color red?
etc&

I am able to output this from the database without using using DISTINCT. But I only want one subject to show.

If I forgot any info please let me know as this is my first posting.

Thanks for your help!

jambla

ASKER

Although this question is probably not hard for you experts out there I am increasing this question to 500 points because I need a response as soon as possible.

Thanks for your help!

Guy Hengel [angelIII / a3]

$query = "SELECT p.pst_id, p.mem_id, m.username, p.posted FROM posts p LEFT JOIN members m on m.mem_id = p.mem_id GROUP BY p.pst_id ORDER BY p.posted DESC LIMIT 0, 10";

jambla

ASKER

Everything works perfect except I dont see where the 'topics' table was queried to get my 't.subject'. My results look like this:

MemID: 100003
UserName: chrisghent
PstID: 100002
TopID:
Subject:

MemID: 100003
UserName: chrisghent
PstID: 100001
TopID:
Subject:

======================================

Here is my code in case you need to see it.

+++++++++++++++++++++++++++++++
===============================
+++++++++++++++++++++++++++++++

//connect to db
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//query bd
$sql = "SELECT p.pst_id, p.mem_id, m.username, p.posted FROM posts p LEFT JOIN members m on m.mem_id = p.mem_id GROUP BY p.pst_id ORDER BY p.posted DESC LIMIT 0, 10";
$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

$mem_id = $row["mem_id"];
$username = $row["username"];
$pst_id = $row["pst_id"];
$top_id = $row["top_id"];
$subject = $row["subject"];

echo "MemID: $mem_id ";
echo "UserName: $username ";
echo "PstID: $pst_id ";
echo "TopID: $top_id ";
echo "Subject: $subject ";
echo " ";

}

+++++++++++++++++++++++++++++++
===============================
+++++++++++++++++++++++++++++++

jambla

ASKER

Any one have any ideas?

Guy Hengel [angelIII / a3]

$query = "SELECT p.pst_id, p.mem_id, m.username, p.posted, t.subject FROM posts p LEFT JOIN members m on m.mem_id = p.mem_id LEFT JOIN topics t ON t.top_id = p.top_id GROUP BY p.pst_id ORDER BY p.posted DESC LIMIT 0, 10";

jambla

ASKER

Thanks for the quick response again.
Its about 95% working but there is still a problem that the same subject shows twice within the LIMIT 10.

Query Results:

===================================================

MemID: 100002
UserName: jim-noi
PstID: 100003
TopID:
Subject: What's the last movie you watched?

MemID: 100003
UserName: chrisghent
PstID: 100002
TopID:
Subject: How Good Is Tiger Woods?

MemID: 100003
UserName: chrisghent
PstID: 100001
TopID:
Subject: What's the last movie you watched?

MemID: 100000
UserName: admin
PstID: 100000
TopID:
Subject: Test Post

===================================================

Within the query LIMIT 0, 10 i don't want the same topic to show twice. In the output it shows "Subject: What's the last movie you watched?" twice. I would like the Subject (topics -> subject or posts -> top_id) to be unique (Thats where i tried the DISTINCT option, but could not get it working).

Any ideas?

Sorry for the trouble but thanks so much for your help!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jambla

ASKER

Fantastic!!! Thanks so much for your help! This is my first time to this site and I'm very very happy with it!!