Unmatched Rows in Tables

Question

Post reply

Unmatched Rows in Tables

shenyjoy

SSC-Addicted

Points: 421
More actions
July 20, 2008 at 11:53 pm

#191193

Row matching in Tables
--------------------------------------------------------------------------------
Hi,
I have two tables , Table_A and Table_B and the same rows in both the tables. The rows have a column called ID which has multiple rows corresponding to it.
Columns are
Id CourseId Group Weekday Starttime EndTime Facultyid
I am trying to match every row in the first table with every row in the second table.
Eg :
If the Table_A.Starttime <> Table_B.Starttime ,where Table_A.ID=Table_B.ID, I want to show an error "Starttime does not match"
If the Table_A.Endtime <> Table_B.Endtime ,where Table_A.ID=Table_B.ID, I want to show an error "Endtime does not match"
I am illustarting an example below:
Table_A
Id CourseId Group Weekday Starttime EndTime Facultyid
123 AAA 3 1 0800 0930 562312
123 AAA 3 3 0800 0930 562312
123 AAA 3 3 1500 1730 562312
Table_B
Id CourseId Group Weekday Starttime EndTime Facultyid
123 AAA 3 1 1000 1130 562312
123 AAA 3 3 1000 1130 562312
123 AAA 3 3 1530 1730 562312
Here you notice that the Startime changes as well as teh end time.
How can I write a query for this.
Please help. I am trying to use the LEFT OUTER JOIN
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

seenay, which datatype are you using for this starttime and endtime columns?

Cheers!

Sandy.

--

shenyjoy SSC-Addicted Points: 421 More actions · Answer 2

Text coz thats easy to compare... i have made all the columns as text

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

I think I have answered this in another forum. Same question.

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

shenyjoy SSC-Addicted Points: 421 More actions · Answer 4

Hi,

I tried using the Join and the Query does take a long time to execute. Will the Cursor work better , as I am doing a row by row comparison ??I have heard and tried but this looks like its a bit complicated.

Will appreciate help.

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Hi Shenijoy

In your first post, you show three rows for each table. Each of the three rows from Table_A has the same Id, "123".

Lets say you want to compare the first row from Table_A to Table_B. Which row (or rows) in Table_B do you want to compare it to?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shenyjoy SSC-Addicted Points: 421 More actions · Answer 6

Hi,

My aim is to see that the three rows match exactly. I am comparing my table to another table in Oracle to see that the entries match. I thought my illustration explained that Sorry. I hope u got me now

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

This will provide you with rows which are in Table_A but do not have an exact match in Table_B.

SELECT a.*

FROM Table_A a

LEFT JOIN Table_B b

ON b.[Id] = a.[Id] AND

b.CourseId = a.CourseId AND

b.[Group] = a.[Group] AND

b.[Weekday] = a.[Weekday] AND

b.Starttime = a.Starttime AND

b.EndTime = a.EndTime AND

b.Facultyid = a.Facultyid

WHERE b.Id IS NULL

You have a column 'Id' in each table but it's not a primary key - do you have a pk on either table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

DROP TABLE #Table_A

DROP TABLE #Table_B

CREATE TABLE #Table_A (Id CHAR(3), CourseId CHAR(3), [Group] CHAR(1), Weekday CHAR(1), Starttime CHAR(4), EndTime CHAR(4), Facultyid CHAR(6))

INSERT INTO #Table_A (Id, CourseId, [Group], Weekday, Starttime, EndTime, Facultyid)

SELECT '123', 'AAA', '3', '1', '0800', '0930', '562312' UNION ALL

SELECT '123', 'AAA', '3', '3', '1000', '0930', '562312' UNION ALL

SELECT '123', 'AAA', '3', '3', '1500', '1730', '562312'

CREATE TABLE #Table_B (Id CHAR(3), CourseId CHAR(3), [Group] CHAR(1), Weekday CHAR(1), Starttime CHAR(4), EndTime CHAR(4), Facultyid CHAR(6))

INSERT INTO #Table_B (Id, CourseId, [Group], Weekday, Starttime, EndTime, Facultyid)

SELECT '123', 'AAA', '3', '1', '1000', '1130', '562312' UNION ALL

SELECT '123', 'AAA', '3', '3', '1000', '1130', '562312' UNION ALL

SELECT '123', 'AAA', '3', '3', '1530', '1730', '562312'

SELECT d.*,

CASE

WHEN b.[Id] IS NOT NULL THEN 'Starttime'

WHEN c.[Id] IS NOT NULL THEN 'Endtime'

ELSE 'Not determined'

END AS MisMatch

FROM (SELECT a.*

FROM #Table_A a

LEFT JOIN #Table_B b

ON b.[Id] = a.[Id] AND

b.CourseId = a.CourseId AND

b.[Group] = a.[Group] AND

b.[Weekday] = a.[Weekday] AND

b.Starttime = a.Starttime AND

b.EndTime = a.EndTime AND

b.Facultyid = a.Facultyid

WHERE b.Id IS NULL

) d

LEFT JOIN #Table_B b

ON b.[Id] = d.[Id] AND

b.CourseId = d.CourseId AND

b.[Group] = d.[Group] AND

b.[Weekday] = d.[Weekday] AND

--b.Starttime = d.Starttime AND

b.EndTime = d.EndTime AND

b.Facultyid = d.Facultyid

LEFT JOIN #Table_B c

ON c.[Id] = d.[Id] AND

c.CourseId = d.CourseId AND

c.[Group] = d.[Group] AND

c.[Weekday] = d.[Weekday] AND

c.Starttime = d.Starttime AND

--c.EndTime = d.EndTime AND

c.Facultyid = d.Facultyid

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden