CODEWITHSUNDEEP
phpmysqljoin
ksdst1
ksdst1

Reputation: 35

SQL Query JOIN syntax

I have the following tables and would like to query one more element from them. 

categories table ->idcat(int), cat(varchar); 
topics table     ->idtopic(int), topic(varchar), idcat(int-fk), iduser(int-fk); 
replies table    ->idreply(int), reply(varchar) iduser(int-fk), idtopic(int-fk)
users table      ->iduser(int), username(varchar).

My current query is;

$query = "SELECT t.topic, t.idtopic, u.username
    FROM topics t
    LEFT JOIN categories c ON t.idcat = c.idcat
    LEFT JOIN users u ON t.iduser = u.iduser
    WHERE c.idcat = '" . $idcat . "'";

Which presents 'Topic' and 'Username'. I'd like to show 'idReply' as well but don't know the proper JOIN syntax.

Upvotes: 2

Views: 58

Answers (2)

Henry
Henry

Reputation: 7881

SELECT
    t.topic, 
    t.idtopic, 
    u.username
FROM 
    topics t 
LEFT JOIN 
    categories c ON t.idcat = c.idcat
LEFT JOIN
    users u ON t.iduser = u.iduser
LEFT JOIN  // new
    replies r ON r.iduser = u.iduser AND r.idtopic = t.idtopic // new
WHERE c.idcat = '" . $idcat . "'";

This will generate a row for every reply to every topic in the specified category. Which could be a lot of records.

You may also want to experiment with the exact type of join on the replies table to get the result you want. LEFT JOIN is probably correct as you'll still get a result if there's no reply to a given topic. This may depend on your flavour of SQL.

OUTER JOIN & LEFT OUTER JOIN are possibilities.

Using INNER JOIN will ensure only topics with replies are returned.

Upvotes: 1

RNK
RNK

Reputation: 5792

You must have reply_id (foreign key) in your topics table. After that you can use this query.

$query = "SELECT t.topic, t.idtopic, u.username, r.id as reply_id
          FROM topics t
          LEFT JOIN categories c ON t.idcat = c.idcat
          LEFT JOIN users u ON t.iduser = u.iduser
          LEFT JOIN replies r ON t.reply_id = r.id
          WHERE c.idcat = '" . $idcat . "'";

Upvotes: 0

Related Questions