Query to find records that do not match with 2 sets of data

Question

Query to find records that do not match with 2 sets of data

lijusmail

Default port

Points: 1486
More actions
June 9, 2008 at 1:11 am

#188730

Hi
First of all the congratulations to all who are putting an effort on this to happen.
I am pretty new to this and it has been helping me a lot.I am just about to finish a very small project and would require some help... 🙂 in accomplishing
I have a Table named as Tab1 and a view named as View1.
The columns have the same datatype except for View1 there is an additional column which is of no interest to me.
Let me give you the list of columns which is common to both TAB1 and VIEW1
GENDATANUM
MODULEID
MODGRPCODE
STARTTIME
FINISHTIME
WEEKDAY
ROOMID
LECTURERID
What i would like to do is
1. Get the Records which are in TAB1 and not in VIEW1
2. Get records which are in VIEW1 and not in TAB1
3. For the matching GENDATANUM in VIEW1 generate those records which do have the rest of the data matching
For eg (this is for the 3rd query)
Consider the following
TAB1
-----
GENDATANUM MODULEID MODGRPCODE STARTTIME FINISHTIME WEEKDAY ROOMID LECTURERID
12345 LANG220 50 1000 1050 1 8030 00000002721
12345 LANG220 50 1000 1050 3 8031 00000002722
VIEW1
-------
GENDATANUM MODULEID MODGRPCODE STARTTIME FINISHTIME WEEKDAY ROOMID LECTURERID
12345 LANG220 50 1000 1150 1 8030 00000002721
12345 LANG220 50 1000 1050 3 8031 00000002722
If you see in the above example in View1 there is the record where the finishtime is not matching with the corresponding value in TAB1. I would like to get that record in the result.
This was just an example...what i could have is i could have records in VIEW1 where only the GENDATANUM matches but everything else does not match. I could have records which do not have matching Starttimes or matching Finishtimes or matching Moduleid or matching modgrpcode or matching lecturerid or matching weekday or matching roomid
Is there anyway that a query could be generated in that way?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Hi,

Your view contains a list of columns belonging to one or more tables. Can you give us the underlaying table definitions? Does the view contain the same table you want to compare the values with? Does the view contain a calculated column?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

lijusmail Default port Points: 1486 More actions · Answer 2

Hi

Sorry for the late answers....was out of town...

The view is not derived from the same table as mentioned . the view is derived from other tables, but has data which matches the table1..

My view does not contain a calculated column.

My view1 and table1 has got matching gendatanum but some fields might be not matching and i want to select those rows which do not have matching data.

Thanks in advance

steveb. SSC-Forever Points: 46733 More actions · Answer 3

You could use a join to match the View and Table on GENDATANUM

and then use where conditions to select the records that do not match

SELECT GENDATANUM, MODULEID, MODGRPCODE, STARTTIME FINISHTIME , WEEKDAY, ROOMID, LECTURERID

From TAB1 inner join VIEW1 on TAB1.GENDATANUM = VIEW1.GENDATANUM

Where VIEW1.MODULEID <> TAB1.MODULEID

or VIEW1.MODGRPCODE <> TAB1.MODGRPCODE

or VIEW1.STARTTIME <> TAB1.STARTTIME

or VIEW1.FINISHTIME <> TAB1.FINISHTIME

or VIEW1.WEEKDAY <> TAB1.WEEKDAY

or VIEW1.ROOMID <> TAB1.ROOMID

or VIEW1.LECTURERID <> TAB1.LECTURERID

Post your tables in the form of Create Table statements for further help

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

You can use UNION to combine the matching columns of both the TAB1 and VIEW1. When you select a GROUP BY from this result and include a counting of rows, matching rows are grouped and therefor the count will be 2 (or higher if duplicates exist). You can filter the count on 1 to get the unique rows. This will give you the answer you need for point 1 and 2 in your request. I included a textfield in the UNION to show the origin of the row (table or view).

See code below for an example.

SELECT

GENDATANUM

,MODULEID

,MODGRPCODE

,STARTTIME

,FINISHTIME

,WEEKDAY

,ROOMID

,LECTURERID

, MAX(Origin) AS Origin

, COUNT(GENDATANUM) AS RecordCount

FROM (SELECT

GENDATANUM

,MODULEID

,MODGRPCODE

,STARTTIME

,FINISHTIME

,WEEKDAY

,ROOMID

,LECTURERID

, 'Table' AS Origin

FROM TAB1

UNION

SELECT

GENDATANUM

,MODULEID

,MODGRPCODE

,STARTTIME

,FINISHTIME

,WEEKDAY

,ROOMID

,LECTURERID

, 'View' AS Origin

FROM VIEW1

) AS Combined

GROUP BY

GENDATANUM

,MODULEID

,MODGRPCODE

,STARTTIME

,FINISHTIME

,WEEKDAY

,ROOMID

,LECTURERID

HAVING COUNT(GENDATANUM) = 1

ORDER BY GENDATANUM

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 5

I had to do something similar, but it was comparing 2 tables with about a hundred different columns, 3 of which were considered keys

I selected the columns from each into temp tables for those records with

matching keys present on both tables

matched the columns using EXCEPT and inserted into table variable

i.e.

INSERT INTO @Table1(col1, col2, etc)

SELECT 1,* FROM #temp1

EXCEPT

SELECT 1,* FROM #temp2

INSERT INTO @Table2(col1, col2, etc)

SELECT 2,* FROM #temp2

EXCEPT

SELECT 2,* FROM #temp1

JOINED the 2 tables on key fields and looked for differences

lijusmail Default port Points: 1486 More actions · Answer 6

ho ...ho.....

I just got lively back into this again.....

thanks for all the wonderful scripts......

we were kind of having problems at my end....

Now i will check the scripts now and i will get back to you with the results...

Please bare with me if i come a bit long. 🙂