asked on

SQL Query Creation Assistance

I need assistance querying two seperate tables to return data in one using a field from the other. This is a doc mgmt system, the first table i'm obtaining info from is "dochistory" here i'm isolating the "docnum" field whose "location" eq "value1". The second table "docmaster" contains a "docnum" column as well but more importantly contains the "docsize" and "entrywhen" columns that the "dochistory" table does not. I need to take the "docnum"s from the "dochistory" table either assign them to some sort of variable i can call upon to query the "docmaster" table to return to me for those docnums the "docsize" and "entrywhen" and then ideally export this or create a table of the results.

Simone B

Based on the information provided, I have 2 tables with the definitions below, and the resulting query.

CREATE TABLE dochistory (docnum INT, location VARCHAR(50))
GO

CREATE TABLE docmaster (docnum INT, docsize INT, entrywhen DATETIME)
GO

SELECT A.docnum, A.location, B.docsize, B.entrywhen FROM docnum AS A
JOIN docmaster AS B ON A.docnum = B.docnum
WHERE A.location = 'value1'

Open in new window

The answers to these questions would be helpful. Are the tables in the same database? Do they have a one to one or a many to one relationship? More details on the results you'd like to see would also be good.

grahamco

ASKER

Yes the tables are in the same db, i count two columns consistent between the two tables "docmaster" and "dochistory" they are [docnum] and [docversion]. I was tasked with determining the amount of storage consumed historically for scanning in documents into our document management system. When a job is scanned into the system it can be seen via the dochistory table with a [location] of GSDA however this table doesn't store file sizes or creation dates and as it's a history table it contains not just the inception event but any other event for a given docnum. I was able to narrow the inception to the location eq to GSDA, so using all docnums with a location of GSDA i'd need to take those docnums to the docmaster table where i can determine based on the docnums, their size and creation date so i'll then will be able to determine by month the growth attributed to scanning.

ASKER CERTIFIED SOLUTION

Simone B

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

this is how I interpreted the post above:

docmaster: [docnum] [size] [creation date]

dochistory: [docversion] [location] (of GSDA) [inception event] (but also any) [other event]

>>they are [docnum] and [docversion]
Are you certain the relationship is docmaster.docnum = dochistory.docversion?

In my experience the document number exists in both a document master table and the history table, because a single document may have many versions. (Just like Buttercup1 outlined).

Docmaster
[docnum] [size] [created]
0001

Docistory
[docnum] [version] [location] [date]
001 1
001 2

Can you post the actual table definitions?

sp_help 'docmaster';
sp_help 'dochistory';

grahamco

ASKER

See attached table definition files for docmaster and dochistory tables. To answer your question docnum doesn't equal docversion i thought buttercup1 was asking how many columns are the same between the two tables in that case each both have a docnum and docversion column. I was a bit weary to run the very solution buttercup proposed as it looks like it's creating tables, with names of existing tables as this is a production database i must be very careful what i'm doing. I will try running the second query now as it doesn't contain any create language.
docmaster.rpt
dochistory.rpt

grahamco

ASKER

Thank you buttercup1, i ran the query and it gave me exactly what i needed

Simone B

Thanks very much.

(The CREATE TABLE statements were just to show how I interpreted your description. They were not meant to be run.)