two questions: 1) Joins 2) Selects

[Meganutter]

Hello.

I would like to ask the following questions, i have tried searching but didnt get any more info on them. I am building a forum from scratch and am stuck with these points.

1) I have a Join as shown here, is it possible to get another value from the Users table using another collumn from the Topics table?
I tried another INNER JOIN but it just duplicated the results which i dont want

Topics.LastPost edBy -> Users.ID
Topics.User_ID -> Users.ID

SELECT Topics.*, Users.Username FROM Topics INNER JOIN Users ON Topics.LastPostedBy = Users.ID

2) When inserting a post i want to retreive the ID created with it, i currently have another select which gets the most recent ID in the table made but that is error prone if two people post at exactly the same time. Is there any other way to achieve this?

I am Using ASP.NET 3.5 SP1 in C#.

Thanks in advance

[Delerna]

1)
Yes its more than possible, it is absolutely essential to be able to do that.
Duplicated results means
a) You don't have enough conditions in the where clause
b) One of the tables has duplicate records (possibly the users table)
..........
At first glance I cant see any problem with your query which leads me to suspect b

2) Change the select so that it gets the most recent id for that particular user

[Meganutter]

Thank you for your reply,
i did notice that nr. 1 got duplicates when the collums LastPostedBy and User_ID (the starter) were different but both duplicates showed the same results.

How would a Query look if i wanted to join those two together? i just got used to writing simple joins so this one is a little over my head

[Delerna]

Do you mean "How do I get the user name for both the starter and the lastposter" ?

Like this ?
[code=sql]
SELECT a.UserID, b.Username as Starter,
a.LastPostedBy, c.Username as LastPoster
FROM Topics
JOIN Users b ON a.UserID= b.ID
LEFT JOIN Users c ON a.LastPostedBy = c.ID
[/code]

[Meganutter]

Yes thank you, it works.

can you explain me why the b.Username and c.Username should be used instead of the normal Users.Username. I managed to adapt the query to work for me but i would like to know why.

[Delerna]

Yes, long story.
The short answer is
a b and c in those queries are called table alias's.
I use them because I am a lazy typist and it is quicker to type
a.Username than Users.username

The long answer is
I think it makes the query more readable
(as does putting each clause of the query on seperate lines)

The way I see it is 'a' is the first table in my join list.
Usually 'a' is the table that I want every record that exists in it (that match the where clause)
reguardless of whether matching records exist in the other tables or not.

The rest I tend to put in order of the prominence of the fields used from it within that particular query, or the likelyhood that that table will not have matching records, or something else that seems important to me at the time.


For example there is no likelyhood that a.userid=b.user id will not have a matching record so it came second in the join list
There is a strong likeleyhood that a.LastPostedBy = c.ID will have no matching records so it comes third in the join list and that is why it uses a left join.

Finally, the query joins to the users table twice so you must use an alias because the query wont know which "Users" table is being referenced in the join conditions.

Actually, rereading my query I noticed that I forgot to add the "a" alias
[code=sql]
SELECT a.UserID, b.Username as Starter,
a.LastPostedBy, c.Username as LastPoster
FROM Topics a
JOIN Users b ON a.UserID= b.ID
LEFT JOIN Users c ON a.LastPostedBy = c.ID
[/code]

These a just little guidelines that I myself have developed over time as my coding style
I have a preference for them because they make sence to me and I can read and understand my queries a little better because of it. Anything that achieves that is a good thing, in my book. :)

[Meganutter]

Thank you for your explanation, i think i understand now.