473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL Help - avoiding querying a query...

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.DESCRIPTION, USERS.USERID, ITEMS.INFO, ITEMS.ITEMUSERID, ITEMS.SENDTO
FROM ITEMS INNER JOIN USERS ON ITEMS.ITEMUSERID = 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.DESCRIPTION
FROM ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS.SENDTO = USERS.USERID;

How can I acomplish this as one query?
Apr 9 '07 #1
4 1307
iburyak
1,017 Expert 512MB
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.DESCRIPTION
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?
Apr 9 '07 #2
ITEMS_LOCATIONS is the name of the stored query...
Apr 9 '07 #3
iburyak
1,017 Expert 512MB
Try this:


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


Good Luck
Apr 9 '07 #4
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.DESCRIPTION
FROM [SELECT USERS.DESCRIPTION, USERS.USERID, ITEMS.INFO, ITEMS.ITEMUSERID, ITEMS.SENDTO
FROM ITEMS INNER JOIN USERS ON ITEMS.ITEMUSERID = USERS.USERID]. AS ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS.SENDTO = USERS.USERID;

Thanks!
Apr 9 '07 #5

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

Similar topics

6
by: Greg | last post by:
I am working on a project that will have about 500,000 records in an XML document. This document will need to be queried with XPath, and records will need to be updated. I was thinking about...
5
by: Shane | last post by:
I wonder if someone has any ideas about the following. I am currently producing some reports for a manufacturing company who work with metal. A finished part can contain multiple sub-parts to...
0
by: roiavidan | last post by:
Hi, I'm having a bit of a problem with a small application I wrote in C#, which uses an Access database (mdb file) for storing financial data. After looking for a similiar topic and failing to...
3
by: MDB | last post by:
I'd normally Google for a question like this, and hope to snag a few examples along with the answer, but this time I can't see to get the keywords specific enough. Or I'd ask coworkers, but...
4
by: paulb | last post by:
I was wondering if it is possible to continuously query a real-time datastream using SQL Server. Does anyone have any experience of this? I have found LINUX based systems such as Borealis and...
5
by: sql_er | last post by:
Guys, I have an XML file which is 233MB in size. It was created by loading 6 tables from an sql server database into a dataset object and then writing out the contents from this dataset into an...
3
by: RJN | last post by:
Hi I've written a code that queries Windows LDAP server and works fine, but the same doesn't work when querying Solaris LDAP server. DirectoryEntry de = new...
2
by: RajSharma | last post by:
Hi, I am facing a problem regarding querying thru a large table having millions of rows....... Its hanging in between while querying for all those rows Can anybody suggest me a query regarding :...
2
by: iheartvba | last post by:
Hi, I usually use access query builder to create complex queries. Especially to create Sub Queries I.E. a querying another query. In my current situation I need to provide the criteria to the query...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.