How to check in mysql that normalized table is ok or not in mysql

Question

MY base table have two colums assume col1,col2. col1 have id, col2 have many comma seprated values colm1 is primary key. like

  col1        col2
 ---------------------
  123        (22,34,12)
  124        (45,67,11)

Now i have another table which is in normalized form of the first one like

col1     col2
---------------
123       22 
123       34
123       12
124       45 
124       67

Now the question is that i want to check that my second table have exect data of first table or not. And if not then how to find that error.

( i need to show the extra row of second table and missing row of second table seperately )

score 0 · Answer 1 · answered Mar 23 '12 at 13:09

0

Something liket that. But unverified

select col1, col2 from old_table 
left outer join (select col1, group_concat(col2 separator ',') as col2
            from new_table
            group by col1) as new_table 
on new_table.col1 = old_table.col1 and new_table.col2 = old_table.col2

answered Mar 23 '12 at 13:09

juergen d

201,996
37
293
362

i have used similar query to this but i also want to show the conflicts of second colum seperately. – Mar 23 '12 at 13:15
Actually, this won't work as intended because the unnormalized data is not ordered in the cell. Even if it was, you haven't provided an order by clause – Mosty Mostacho Mar 23 '12 at 13:52

score 0 · Answer 2 · answered Mar 23 '12 at 13:10

0

Get a cursor or recordset and you will then do a loop of all records from table 1,

rs_col1 = id rs_col2 = the comma seperated values

for each record in rs select * from table_2 where id = rs_col1 and table_2.col2 not in (rs_col2) loop

answered Mar 23 '12 at 13:10

Craig Trombly

464
2
9

i have to check my second table in respect of first and have show the conflict of col2 of second table seperately. – Mar 23 '12 at 13:17
So you are basically doing a DIFF of both table, but one has them CSV and one Normalized? – Craig Trombly Mar 23 '12 at 13:20
Here's a similar post http://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows – Craig Trombly Mar 23 '12 at 13:43
link is not usefull in my case – Mar 26 '12 at 05:19

score 0 · Answer 3 · answered Mar 23 '12 at 14:03

0

I think your unnormalized data is not quite useful, as the data is not ordered so you can't use a group_concat. However, you might be able to find a solution by using find_in_set and counting the amount of matches of col2 (normalized table) in col2 (unnormalized table) grouped by col1. You will then have to make sure that amount of matches is equal to the amount of elements in the multivalued cell.

answered Mar 23 '12 at 14:03

Mosty Mostacho

42,742
16
96
123

find_in_set is not usefull because it gives only count, i need the conflicting data too..... – Mar 26 '12 at 05:15

score 0 · Accepted Answer · 2012-03-26T05:56:11.007

0

Thanx to everbody for suggestions. I got my solution through this query

select * table1 as a join table2 as b on a.col1=b.col1 where a.col2 not like concat('%',b.col2,'%')

edited Mar 26 '12 at 05:56

answered Mar 26 '12 at 05:41

How to check in mysql that normalized table is ok or not in mysql

4 Answers4