By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

two questions: 1) Joins 2) Selects

P: 47
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.LastPostedBy -> Users.ID
Topics.User_ID -> Users.ID

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 14 '09 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
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
Aug 16 '09 #2

P: 47
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
Aug 17 '09 #3

Delerna
Expert 100+
P: 1,134
Do you mean "How do I get the user name for both the starter and the lastposter" ?

Like this ?
Expand|Select|Wrap|Line Numbers
  1. SELECT a.UserID, b.Username as Starter,
  2.        a.LastPostedBy,c.Username as LastPoster
  3. FROM Topics 
  4. JOIN Users b ON a.UserID= b.ID
  5. LEFT JOIN Users c ON a.LastPostedBy = c.ID
  6.  
Aug 17 '09 #4

P: 47
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.
Aug 18 '09 #5

Delerna
Expert 100+
P: 1,134
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.userid 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
Expand|Select|Wrap|Line Numbers
  1. SELECT a.UserID, b.Username as Starter, 
  2.        a.LastPostedBy,c.Username as LastPoster 
  3. FROM Topics  a
  4. JOIN Users b ON a.UserID= b.ID 
  5. LEFT JOIN Users c ON a.LastPostedBy = c.ID 
  6.  
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. :)
Aug 18 '09 #6

P: 47
Thank you for your explanation, i think i understand now.
Aug 19 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.