Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1136
  • Last Modified:

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!
0
jambla
Asked:
jambla
  • 5
  • 3
1 Solution
 
jamblaAuthor Commented:
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!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
$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";
0
 
jamblaAuthor Commented:
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 <br>";
echo "UserName: $username <br>";
echo "PstID: $pst_id <br>";
echo "TopID: $top_id <br>";
echo "Subject: $subject <br>";
echo "<br>";

}

+++++++++++++++++++++++++++++++
===============================
+++++++++++++++++++++++++++++++
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
jamblaAuthor Commented:
Any one have any ideas?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
$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";
0
 
jamblaAuthor Commented:
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!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, try this version instead:

$query = "SELECT p.pst_id, p.mem_id, m.username, p.posted, t.subject FROM posts p JOIN topics t ON t.top_id = p.top_id LEFT JOIN members m on m.mem_id = p.mem_id GROUP BY p.top_id ORDER BY p.posted DESC LIMIT 0, 10";
0
 
jamblaAuthor Commented:
Fantastic!!!  Thanks so much for your help!  This is my first time to this site and I'm very very happy with it!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now