SQL: Filter table by multiple conditions-CodePudding

I have a master table with data for men and women. There are various fields: name, surname, date of birth, address, surname of the husband. The latter field is valued only for women (but not for all women, of course). I need to create in ms-access or mysql a second table with:

all men (this is not a problem);
all women without a spouse (this is also not a problem);
married women whose husband is not already on the list (comparing man.surname with woman.surname of the husband and address).

-example:

name	surname	gender	husband's surname	city	address
n1	s1	m		c1	a1
n2	s2	f		c2	a2
n3	s3	f	s1	c1	a1
n4	s4	f	s4	c4	a4

-the results:

name	surname	gender	husband's surname	city	address
n1	s1	m		c1	a1
n2	s2	f		c2	a2
n4	s4	f	s4	c4	a4

Thank you all!

CodePudding user response：

For your given sample data, You can check the below query having left join -

SELECT D1.*
  FROM DATA D1
  LEFT JOIN DATA D2 ON D1.surname = D2.husband_surname
                   AND D1.address = D2.address
 WHERE D1.husband_surname IS NULL
    OR D1.name = D2.name;

Demo.

CodePudding user response：

You may use Not Exists correlated query as the following:

Select * From tblname T
Where Not Exists (Select * From tblname D Where D.surname = T.husband_surname 
                  And D.address = T.address And D.gender = 'm')
/*And T.gender = 'f' un-comment this to get women only*/

Works on most DBMS. See a demo on MySql 8.0 from db<>fiddle.