Results 1 to 7 of 7

Thread: Gary

  1. #1
    JOINS Question Guest

    Default Gary

    I have two tables, one which contains the users details and another that contains message details.<BR><BR>The message table has a &#039;ToUserID&#039; field and a &#039;FromUserID&#039; field. I want to join the Messages table to the Users so that I can extract the usernames of both &#039;ToUserID&#039; and &#039;FromUserID&#039;<BR><BR>Any help would be appreciated.<BR>Cheers

  2. #2
    Medieval Dude Guest

    Default RE: Gary

    Hmm... seems to me you may need to UNION two SELECT statements together because as I&#039;m sure you figured out rather quickly, you can&#039;t really JOIN those two tables together on the ToUserID field and the FromUserID since they both refer to the same field in the user details table. So, what I would do is create a SELECT statement that gets the information from the ToUserID from message details and JOIN it to the user details table ON ToUserID. Then I would UNION that to a second SELECT statement that grabs the FromUserID field from the message details (and anything else you want from message details) and JOIN that to the user details table ON FromUserID. After the Union, you should have the information on both users as well as information from the message details table.

  3. #3
    Nils Bilhorn Guest

    Default Join.

    select uFrom.username,uTo.username,m.*<BR> from userdetails uFrom,<BR> messagedetails m,<BR> userdetails uTo<BR> where = m.fromUserid<BR> and m.toUserid =<BR>

  4. #4
    Medieval Dude Guest

    Default Where's the join?

    You say Join, yet there is no JOIN anywhere in that SELECT statement. You can&#039;t use a WHERE clause to JOIN tables together. It doesn&#039;t work like that. I challenge the validity of that query as a whole, even if you used the proper JOIN syntax. How would you join two instances of a table to one other table? I just don&#039;t see that happening.

  5. #5
    Medieval Dude Guest

    Default Ah... I have it now

    I was able to try something similar to what&#039;s going on here. Your idea made me wonder if such a thing could be done. If you use this syntax (an improvement upon yer earlier post), it is possible to use the same table twice by using two JOIN statements:<BR><BR>SELECT uFrom.username,uTo.username,m.*<BR>FROM messagedetails m JOIN userdetails uFrom ON m.userFrom = uFrom.username JOIN userdetails uTo ON m.userTo = UTo.username<BR>

  6. #6
    Nils Bilhorn Guest

    Default Try it out Dude

    Yes, you can join tables by using conditions in the where clause.<BR>And yes you can have multiple instances of the same table in the from clause. Have you tested my code at all?<BR>The reason why I do not use the join syntax is that not all dbms support that syntax. Both of these join methods are defined in the ANSI SQL standard though.

  7. #7
    Medieval Dude Guest

    Default Well, paint me blue and call me Papa Smurf...

    I didn&#039;t think you could do joins in the WHERE clause. I like the way the JOIN syntax looks better, though, because that&#039;s how I was taught. Plus, if you do things other than INNER JOIN, I doubt it would be so simple as to throw it into the WHERE clause, so as good practice I will still keep JOIN syntax as I have it. It&#039;s good to know the other method is there, though, so that I can better check existing code that I did not create.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts