0

i have a database with 5 tables. one field in all the tables is the same. Now, due to some data entry errors, a few rows are non matching. how do i find which ones are non matching and in what tables. table structure is as follows:-

stockmaster(code,description)
stockquantity(code,quantity)
stockbuffer(code,buffer)
stockweights(code,weight)
stockorders(code,orderqty)

i want to find out which codes are non matching in all the tables. any help would be greatly appreciated.

i used the following query to find the matching ones.

SELECT stockmaster.code as a, stockquantity.code as b, stockbuffer.code as c, stockweights.code as d, stockorders.code as e FROM stockmaster JOIN stockquantity ON stockquantity.code = stockmaster.code  and so on...

above gives me the matching ones.

mmdel
  • 1,279
  • 4
  • 21
  • 30
  • This may help you in figuring out how to do set difference: http://stackoverflow.com/questions/2723839/mysql-difference-of-two-result-sets – rink.attendant.6 May 23 '13 at 12:18

1 Answers1

0

To get a list of codes which have a record missing on a table (assuming here there is a 1 to 1 relationship), and a comment saying on which table they are missing something like this should do it

SELECT Codes.code,
CONCAT(CASE WHEN stockmaster.code IS NULL THEN 'Missing from stockmaster. ' ELSE '' END
CASE WHEN stockquantity.code IS NULL THEN 'Missing from stockquantity. ' ELSE '' END
CASE WHEN stockbuffer.code IS NULL THEN 'Missing from stockbuffer. ' ELSE '' END
CASE WHEN stockweights.code IS NULL THEN 'Missing from stockweights. ' ELSE '' END
CASE WHEN stockorders.code IS NULL THEN 'Missing from stockorders. ' ELSE '' END)
FROM (SELECT code FROM stockmaster
UNION
SELECT code FROM stockquantity
UNION
SELECT code FROM stockbuffer
UNION
SELECT code FROM stockweights
UNION
SELECT code FROM stockorders) Codes
LEFT OUTER JOIN stockmaster ON stockmaster.code = Codes.code
LEFT OUTER JOIN stockquantity ON stockquantity.code = Codes.code
LEFT OUTER JOIN stockbuffer ON stockbuffer.code = Codes.code
LEFT OUTER JOIN stockweights ON stockweights.code = Codes.code
LEFT OUTER JOIN stockorders ON stockorders.code = Codes.code
WHERE stockmaster.code IS NULL
OR stockquantity.code IS NULL
OR stockbuffer.code IS NULL
OR stockweights.code IS NULL
OR stockorders.code
Kickstart
  • 21,403
  • 2
  • 21
  • 33