April 28, 2009 at 3:25 am
Hi there. I'm having quite a lot of problems trying to do what I need to with SQL. This may be very basic to some but my understanding of T-SQL is only as good as the snippets I find on google......
Essentially, this is an Audit function...
A table filled with the checked data is compared with the main table where an ID is checked that it is in the correct room, location and sublocation.
I can get a list of all that is wrong with it using...
SELECT * FROM tblAudit
EXCEPT
SELECT Room, Location, SubLocation, IDNo FROM tblMain
Hovever, that will only show the info within the Audit table. If I reverse it, it shows around 15000 records! I can qualify the room, location and sub location to get one area, but still only shows one lot of data, not both simultaneously.
I did use INNER JOIN on the IDNo which presented the information well, but displayed every record, not just the ones that do not match. Every time I add INNER JOIN to the EXCEPT statement, it kicks out a few errors...
Another option I did think of was using T-SQL to effectively "add" a column stating whether Audit = Main to present to a grid view (asp.net), however all attempts on this front have failed.
Any help on this would be most appreciated.
April 28, 2009 at 3:42 am
SELECT * FROM tblAuditEXCEPT
SELECT Room, Location, SubLocation, IDNo FROM tblMain
The requirements are not very clear (no DDL of the tables, no expected output). Anyway do you mean something like that:
SELECT * -- specify columns in production
FROM tblAudit AS A
WHERE NOT EXISTS
(SELECT *
FROM tblMain AS M
WHERE A.IdNo = M.IdNo)
brgds
Philipp Post
brgds
Philipp Post
April 28, 2009 at 4:08 am
Philipp Post (4/28/2009)
The requirements are not very clear (no DDL of the tables, no expected output). Anyway do you mean something like that:
I do apologise. I'm unsure as to "DDL" of tables, but the coloumns are as follows...
tblAudit
-------
Room nvarchar(50)
Location nvarchar(50)
SubLocation nvarchar(50)
IDNo int NOT NULL
tblMain
-------
Room nvarchar(50)
Location nvarchar(50)
SubLocation nvarchar(50)
IDNo int NOT NULL IDENTITY(1,1)
The main table has some 30 something columns. This cannot be changed in any way shape or form.
Using the code below...
SELECT * FROM tblAudit
EXCEPT
SELECT Room, Location, SubLocation, IDNo FROM tblMain
I see (for example)
Room Location SubLocation IDno
08 Bookcase01 Shelf1 14521
This record in tblMain has a sublocation of Shelf2 however in the output, you cannot see that. The rows the EXCEPT statement returns are perfect, but don't show the whole picture. I tried using a JOIN to add the tblMain data on to it, but failed at every turn.
Room Location SubLocation IDno MRoom MLocation MSublocation
08 Bookcase01 Shelf1 14521 08 Bookcase01 Shelf2
What would be better though (but seemingly completely out of my grasp of T-SQL) is to have the above returned as a simple join (so every record is displayed) but add a "validated" column that checks Room, Location and Sublocation is the same then ouptuts True or False. That way I can present the data easier through ASP.net.
I hope this makes sense. I was thrown this project acompanied by a "teach yourself" book and google so I do apologies for any bad terminology used!
April 28, 2009 at 4:13 am
There are probably other ways but using a CTE (Common table expression) is a possibility
I'll assume you want the main table data for records you found in the audit where the
data items are different
with MyAuditRecords as (
SELECT Room, Location, SubLocation, IDNo FROM tblAudit
EXCEPT
SELECT Room, Location, SubLocation, IDNo FROM tblMain )
select a.Room, a.Location, a.SubLocation, a.IDNo,
m.Room, m.Location, m.SubLocation, m.IDNo
from MyAuditRecords a
join tblMain m on m.IDNo = a.IDNo
Just about to post and saw your last update with more info. I think this is roughly what you were after without re-reading the details
Cheers
Tony
April 28, 2009 at 6:38 am
That is perfect!!!
Thank you ever so much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply