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

Question

Post reply

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: 33506 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: 33506 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

lijusmail

Default port

Points: 1486

June 24, 2008 at 4:41 am

#833209

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. 🙂