Three Table Join

jkruer01

Hey everyone, I am trying to use PHP/PostgreSQL to create a three table join and I keep getting parser errors. Here is my code does anyone have any suggestions?

SELECT Topic.TopicID, Topic.Topic, Topic.UserName, Topic.Time, Topic.Message, Reply.TopicID, Reply.Time, Reply.Message, Reply.UserName, Members.JoinDate
FROM ( Topic LEFT JOIN Reply ON Topic.TopicID = Reply.TopicID ) LEFT JOIN Members ON ( Reply.UserName = Members.UserName ) AND ( Topic.UserName = Members.UserName )
WHERE Topic.TopicID = $TopicID
ORDER BY Topic.Time, Reply.Time;

When I run it with the "("'s I get
"parser: parse error at or near "("

When I run it without the "("'s I get
"parser: parse error at or near "topic"

I would appreciate anyone's help.

Thanks!

volleytotal_ch

Can you send us the table structures and how they are linked to each other?
Does a topic need to have an answer or not?

kubla

try it this way:

SELECT Topic.TopicID, Topic.Topic, Topic.UserName, Topic.Time, Topic.Message, Reply.TopicID, Reply.Time, Reply.Message, Reply.UserName, Members.JoinDate
FROM Topic LEFT JOIN Reply ON Topic.TopicID = Reply.TopicID LEFT JOIN Members ON Reply.UserName = Members.UserName
WHERE Topic.TopicID = $TopicID AND Topic.UserName = Members.UserName
ORDER BY Topic.Time, Reply.Time;

volleytotal_ch

@: guess this doesn't work because the topic and the reply got different members!

swon

Dont' know your tables but try that:

$querys ="SELECT Topic.TopicID, Topic.Topic, Topic.UserName, Topic.Time, Topic.Message,
Reply.TopicID, Reply.Time, Reply.Message, Reply.UserName,Members.JoinDate
FROM Topic LEFT JOIN Reply ON Reply.TopicID = Topic.TopicID
INNER JOIN Members ON Members.UserName = Reply.UserName
AND Members.UserName = Topic.UserName WHERE Topic.TopicID = $TopicID ORDER BY Topic.Time, Reply.Time";

jkruer01

Originally posted by kubla
try it this way:

SELECT Topic.TopicID, Topic.Topic, Topic.UserName, Topic.Time, Topic.Message, Reply.TopicID, Reply.Time, Reply.Message, Reply.UserName, Members.JoinDate
FROM Topic LEFT JOIN Reply ON Topic.TopicID = Reply.TopicID LEFT JOIN Members ON Reply.UserName = Members.UserName
WHERE Topic.TopicID = $TopicID AND Topic.UserName = Members.UserName
ORDER BY Topic.Time, Reply.Time;

I tried that and I got "ERROR: parser: parse error at or near "topic""

Thanks for the suggestion though.

jkruer01

Originally posted by swon
Dont' know your tables but try that:

<?

$querys ="SELECT Topic.TopicID, Topic.Topic, Topic.UserName, Topic.Time, Topic.Message,
Reply.TopicID, Reply.Time, Reply.Message, Reply.UserName,Members.JoinDate
FROM Topic LEFT JOIN Reply ON Reply.TopicID = Topic.TopicID
INNER JOIN Members ON Members.UserName = Reply.UserName
AND Members.UserName = Topic.UserName WHERE Topic.TopicID = $TopicID ORDER BY Topic.Time, Reply.Time";

?>

I tried that and I also got an error: "ERROR: parser: parse error at or near "topic""

I don't know what the problem is.

jkruer01

Originally posted by volleytotal.ch
1. Can you send us the table structures and how they are linked to each other?
2. Does a topic need to have an answer or not?

Ok, here are my three tables

Members

Topic

Reply

Just some more information. In case you don't know, I am trying to set up a forum. The Topic table is where the New Topics will be posted and the Reply table is where the replies to those topics will be stored.

Thanks,

volleytotal_ch

i'll try:

SELECT 
 t.TopicID // Add more, just use the field names
 // To select, use the letters t=topic, m=topicmember, r=reply, n=replymember!
FROM
 Topic t, // Topic Table
 Members m, // Member Table linked with the Topics
 Members n // Member Tabel linked with the REPLIES
LEFT JOIN
 Reply r
ON
 r.TopicID = t.TopicID // This links Replies with topics
WHERE
 m.UserName = t.UserName // This links Members with topics
 AND n.UserName = r.UserName // This links Members with replies
 AND t.TopicID = $TopicID // Only select one topic
ORDER BY
 t.Time DESC, // Doesen't make sense 'cause you just select 1 topic
 r.Time ASC // Answers first
;

Hope this works😉
Try it out with some entries in the table and select all 't., m., r., n.'
and see if it works correctly! it might be that the topic entry is NOT the first entry in your result row (but i didn't test it 😉 )

Just a short remark:
Why don't you use an ID for the users? This id can be used in the topic and reply table as foreign key and will help you to avoid redundacy in your database! (if a user changes his name, you have to change the value in 3 tables instead of just one row!)

hth

jkruer01

Thanks for the help. I got it working. Also, I took your advice and changed the tables to use UserID instead of UserName in the tables Topic and Reply. Thanks for all of your help.