query question 1

[MikeNL]

Hi,

I have a little issue creating a query, I have the following setup:

tbl_Page:
-PageID
-PageTitle
-PageInsertUserIDFK
-PageEditUserIDFK

tbl_User:
-UserID
-UserName

I want to join those tables and display the username who created the page and the username of the user who has been editing the page. Can someone give me some help setting this query up?? I'm using Access btw, I should use MySQL or MSSQL, but the client wants Access

Hope someone can help!

My blog:

http://www.wantonline.com/blog

 

[mansii]

Something like

SELECT InsertUser.PageID, InsertUser.PageTitle, InsertUser.UserName as InsertUser, EditUser.UserName as EditUser
FROM (SELECT tbl_User.UserName, tbl_Page.PageID, tbl_Page.PageTitle
FROM tbl_User RIGHT JOIN tbl_Page ON tbl_User.UserID = tbl_Page.PageEditUserIDFK) As EditUser 
INNER JOIN (SELECT tbl_User.UserName, tbl_Page.PageID, tbl_Page.PageTitle
FROM tbl_User INNER JOIN tbl_Page ON tbl_User.UserID = tbl_Page.PageInsertUserIDFK) As InsertUser 
ON EditUser.PageID = InsertUser.PageID;

?

Hope this helps

 

[MikeNL]

Thanks man! That was what I was looking for...
I need to work on my SQL skills, I've done too much Flex stuff lately

Thanks again!

My blog:

http://www.wantonline.com/blog

 

[MikeNL]

Sorry about asking again, but what if I also want to include the pageActiveUserIDFK and the pageDeleteUserIDFK??

I tried to modify it, but I think I'm using the wrong syntax for it....

If anyone could show me how to add some more JOIN stuff for this, that would be great!

Thanks in advance!

My blog:

http://www.wantonline.com/blog

 

[mansii]

Actually, you can play arround with single queries to get one value for each specific user from tbl_user, then create another query based on those queries, in this case 4 queries.

SELECT tbl_Page.PageID, tbl_Page.PageTitle, InsertUser.UserName, EditUser.UserName, ActiveUser.UserName, DeleteUser.UserName
FROM DeleteUser 
INNER JOIN (ActiveUser 
     INNER JOIN (EditUser 
          INNER JOIN (InsertUser 
               INNER JOIN tbl_Page 
               ON InsertUser.PageID = tbl_Page.PageID) 
          ON EditUser.PageID = tbl_Page.PageID) 
     ON ActiveUser.PageID = tbl_Page.PageID) 
ON DeleteUser.PageID = tbl_Page.PageID;

Please note that unnecessary fields have been removed from the selection.

Hope this helps,
mansii

 

[MikeNL]

Ok cool I hadn't cleared the sleep out of my eyes this morning I think...

Thanks for helping out!!

My blog:

http://www.wantonline.com/blog