displaying only check 1 when dispay value is not all

[dotJoon]

I have 2 tables like the below.
Please notice Family_Number in myTable2 is foreign key connecting to myTable1.

data in myTable1
Family_ID  Family_Name  Place       Check  Display  
1          Fonda        New York     1      all 
2          Tailor       Chicago      1      0
3          Jackson      L.A.         0      0 
  
data in myTable2
First_ID  Family_Number  First_Name  check  
1          1             Henry       0 
2          1             Jane        0 
3          3             Michael     1 
4          2             Robert      0 
5          2             Liz         0 
6          3             Mary        0

I have the below SQL statement and its result.

SQL 
select Family_Name as Name, Place as Place
from myTable1
UNION
select First_Name as Name, Place as Place
from myTable2
inner join myTable1 on myTable2.Family_Number = myTable1.Family_ID

result

Fonda    New York
Tailor   Chicago
Jackson  L.A.
Henry    New York
Jane     New York
Robert   Chicago
Liz      Chicago
Michael  L.A.
Mary     L.A.

I like to produce the following things.
If display value is "all", it displays all names.
and
If display value is "0" or not all, it displays one name which has the check value "1"(pleae notice only one record has check value "1" in the same family whether it is in myTable1 or myTable2).

The target result would be like the following.

target result

Fonda    New York
Tailor   Chicago
Henry    New York
Jane     New York
Michael  L.A.

Thanks in Advance

[kleineme]

Hi,

maybe you're looking for something like this:

select Family_Name as Name, Place as Place
from myTable1
where Display = 'all' or Check = 1
UNION
select First_Name as Name, Place as Place
from myTable2, myTable1
where myTable2.Family_Number = myTable1.Family_ID
and ((myTable1.Display != 'all' and myTable2.check = 1) or myTable1.Display = 'all')

[dotJoon]

Thanks for your code.

By the way, your code causes SQL errors, and I am finding which line has a problem, but I haven't find it yet, so it still causes the errors.


Thanks in Advance again

[r937]

CHECK is a reserved word

otherwise, kleineme's query works perfectly

[dotJoon]

I've changed the field name "Check" into "Chek".

and I applied kleineme's query with changing "Check" into "Chek".

The following is the query.

kleineme's query1
select Family_Name as Name, Place as Place
from myTable1
where Display = 'all' or Chek = 1
UNION
select First_Name as Name, Place as Place
from myTable2, myTable1
where myTable2.Family_Number = myTable1.Family_ID
and ((myTable1.Display != 'all' and myTable2.Chek = 1) or myTable1.Display = 'all')

As I got an error with "kleineme's query1", I found there is no myTable2.Chek in my database.
So I changed myTable2.Chek into myTable1.Chek, because there is "myTable1.Chek in my database.

So the kleinerme's query1 is modified like the following "kleineme's query2".

kleineme's query2
select Family_Name as Name, Place as Place
from myTable1
where Display = 'all' or Chek = 1
UNION
select First_Name as Name, Place as Place
from myTable2, myTable1
where myTable2.Family_Number = myTable1.Family_ID
and ((myTable1.Display != 'all' and myTable1.Chek = 1) or myTable1.Display = 'all')

result of the kleineme's query2

Fonda    New York
Tailor   Chicago
Henry    New York
Jane     New York
Robert   Chicago
Liz      Chicago

The problem here is the result of the kleineme's query2 is somewhat different from my target result which is in the below.

target result

Fonda    New York
Tailor   Chicago
Henry    New York
Jane     New York
Michael  L.A.

Thanks in Advance

[dotJoon]

Sorry!

I made a mistake in applying kleineme's query1.

As I fix my mistake, It works!

Thank you very much