need help referncing a table twice

[otuatail]

Hi SQL is not my hot spot. I am trying to create a news board similar to this one. I have a user table with each user id stored as an MD5() value to make it more cryptic.

 

I have a topics table with 2 fields

Owner_id which is the owner of the topic and

Replied which is the person last replying.

 

My problem is I need to reference the User table twice to get what I am calling the ‘Friendly name’ to display. I can’t get my head around this second reference. Please help. SQL and table fields supplied

 

Users Table
-----------
  `UserF` varchar(15) default NULL,
  `UserS` varchar(15) default NULL,
  `Friendly` varchar(15) default NULL,
  `Email` varchar(40) default NULL,

Topics table
------------
  `owner_id` varchar(32) default NULL,
  `Replied` varchar(32) default NULL,


SELECT Threads.Subject,
Threads.Content,
Threads.Thread,
Threads.CreateDate,
Threads.UpdateDate,
Threads.Writable,
Threads.Pinable,
Threads.View,
Threads.Reply,
Threads.owner_id, 
Threads.ReplyDate,
Threads.Replied, 

SAYusersX.Friendly,
SAYusersX.Userid 

FROM Threads, SAYusersX 

WHERE Threads.owner_id = SAYusersX.User and 
Threads.Visible = 1 
order by Pinable DESC,
Threads.ReplyDate DESC

[kickstart]

Hi

 

You can alias table names.

 

SELECT *
FROM Topics
INNER JOIN Users Users1 ON Topics.owner_id = Users1.UserId
INNER JOIN Users Users2 ON Topics.Replied = Users2.UserId

 

In this case the Users table in joined twice, but referred to as Users1 or Users2 depending on which one you are referencing.

 

All the best

 

Keith

[otuatail]

Ok Thanks for that. It does work except that any topics without a reply is not included. Will have to add the Author in the reply and check for zero replies. Thnks again.

 

Desmond.

 

[kickstart]

Hi

 

Easier than that.

 

An INNER JOIN will find a row where there is a matching row on both tables. So in your example there is a record on Topics but as the replied id is empty there is no matching record on the users table. Hence no record is returned.

 

Another type of join is an OUTER JOIN. This will return a record from one table whether there is a matching record on the other table or not. If there is a matching record then the columns from that table will have the appropriate values, but if not they will just be NULL. There are a few different types of OUTER JOIN, but 99% of the time you will use a LEFT OUTER JOIN, which will return the record from the table on the left whether or not there is a record on the table on the right.

 

In the SQL I posted earlier change INNER JOIN to LEFT OUTER JOIN and it should do what you want.

 

All the best

 

Keith