SQL Help - avoiding querying a query...

[chitownjohn69]

I'm working on a simple database that has 2 tables one called ITEMS, one called USERS. I'm tracking items being transfered between 2 users. in the ITEMS table I reference the sending user and receiving user by a numeric field ITEMUSERID and SENDTO that both refer to the USERID in the USERS table. I want to pull the description of both of these users in my result. I am able to do this using a stored query and then querying that query. I want to be able to do it in one step.

Here's what works..

Stored Query: ITEMS_LOCATION
(This pulls all the items and the "where they're coming from" description...)

SELECT USERS.DESCRIPTI ON, USERS.USERID, ITEMS.INFO, ITEMS.ITEMUSERI D, ITEMS.SENDTO
FROM ITEMS INNER JOIN USERS ON ITEMS.ITEMUSERI D = USERS.USERID;

(Then I execute the following query to get the "where the item is going" description in to the table)

SELECT ITEMS_LOCATIONS .DESCRIPTION, ITEMS_LOCATIONS .USERID, ITEMS_LOCATIONS .INFO, ITEMS_LOCATIONS .ITEMUSERID, ITEMS_LOCATIONS .SENDTO, USERS.DESCRIPTI ON
FROM ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS .SENDTO = USERS.USERID;

How can I acomplish this as one query?

[iburyak]

1.

in the ITEMS table I reference the sending user and receiving user by a numeric field ITEMUSERID and SENDTO that both refer to the USERID in the USERS table

According to statement above table ITEMS has both columns ITEMUSERID and SENDTO.

2.

SELECT ITEMS_LOCATIONS .DESCRIPTION, ITEMS_LOCATIONS .USERID, ITEMS_LOCATIONS .INFO, ITEMS_LOCATIONS .ITEMUSERID, ITEMS_LOCATIONS .SENDTO, USERS.DESCRIPTI ON
FROM ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS .SENDTO = USERS.USERID;

According to query provided it looks like SENDTO is in a different table ITEMS_LOCATIONS .


Which one is correct?

[chitownjohn69]

ITEMS_LOCATIONS is the name of the stored query...

[iburyak]

Try this:


[PHP]SELECT U1.DESCRIPTION, U1.USERID, ITEMS.INFO, ITEMS.ITEMUSERI D, ITEMS.SENDTO,
U2.DESCRIPTION
FROM ITEMS INNER JOIN USERS U1 ON ITEMS.ITEMUSERI D = USERS.USERID
INNER JOIN USERS U2 ON ITEMS.SENDTO = USERS.USERID[/PHP]


Good Luck

[chitownjohn69]

Got me pointed in the right direction.. This works..

SELECT ITEMS_LOCATIONS .DESCRIPTION, ITEMS_LOCATIONS .USERID, ITEMS_LOCATIONS .INFO, ITEMS_LOCATIONS .ITEMUSERID, ITEMS_LOCATIONS .SENDTO, USERS.DESCRIPTI ON
FROM [SELECT USERS.DESCRIPTI ON, USERS.USERID, ITEMS.INFO, ITEMS.ITEMUSERI D, ITEMS.SENDTO
FROM ITEMS INNER JOIN USERS ON ITEMS.ITEMUSERI D = USERS.USERID]. AS ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS .SENDTO = USERS.USERID;

Thanks!