Pls Note:
1. I am working with D5, SQL7 and TADO.
2. I realize there are better ways of doing this example - but it is just a
simple example. My selects are far more complex than this.

If I wanted a combined result set that included all the matches in the
individual selects I would do a UNION (to remove dups) or UNION ALL (if dups
were OK).  To my way of thinking this is like a boolean 'OR'.

Q1. Am I right?

select ID from mytable1 where mycolumn1=100
UNION ALL
select ID from mytable2 where mycolumn1=200
UNION ALL
select ID from mytable3 where mycolumn1=300
UNION ALL
select ID from mytable4 where mycolumn1=400

Q2. Now, if I wanted a combined result set that included all the values of
ID *that were common* to all the individual selects, how would I do it?   In
other words, is there a boolean 'AND' verb that corresponds with UNION or
does it need to be done another way?

select ID from mytable1 where mycolumn1=100
{AND}
select ID from mytable2 where mycolumn1=200
{AND}
select ID from mytable3 where mycolumn1=300
{AND}
select ID from mytable4 where mycolumn1=400

If you want Duplicates you will have to use the above.  If you don't want
duplicates, you could use:
    select ID from mytable1
    where mycolumn1=100 or mycolumn1=200 or mycolumn1=300 or mycolumn=400

or event better

    select ID from mytable1
    where mycolumn1 in (100,200,300,400)

The following may be a possibility:

    select ID from mytable1
    where mycolumn1=100 and mycolumn2=200 and mycolumn3=300 and
mycolumn4=400

It seems to me that your queries are rather complex when AND, OR etc. will
give you what you want.  But sometimes UNION and UNION ALL is necessary.

Oliver

What you are asking for is an Intersection. There is a specification for
intersection in the SQL standard but I don't know if MSSQL (or anyone
else) supports it or not. If so then use that. If not then it can be
done with inner joins. Since you only want IDs that appear in all
tables, I would think you do not need to have every ID returned 4 times
but just once.

select ID from mytable1 t1 where t1.mycolumn1=100
inner join mytable2 t2 on t2.mycolumn1 = t1.mycolumn1
inner join mytable3 t3 on t3.mycolumn1 = t1.mycolumn1
inner join mytable42 t4 on t4.mycolumn1 = t1.mycolumn1

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/RADBooks/delphibooks.html
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist" - Ayn Rand

Just a couple of clarifications.

1) Does your response truly give me all ID's common to all four tables or is
there a typo? All your lines list ... on t?.mycolumn1 = t1.mycolumn1 i.e.
all are compared to t1.mycolumn1.

2) What would the Inner Join syntax be to include the other conditions in my
example (i.e. where mycolumn1=200, ... where mycolumn1=400 etc)?

tia

"Wayne Niddery (TeamB)" <winwri...@chaffhome.com> wrote in message
news:3aeaf6f6$1_2@dnews...

You only want the record if it appears in all 4 tables, so yes, for each
record in t1, it will only select it if there is a match in all of the
other three tables. This makes it easier for the query to be optimzed by
the server since each join is only one level deep.

Just add a where clause as normal after the last join. Make sure you
specify *which* table it should take mycolumn1 from, e.g. t1.mycolumn1 =
200. You can never be too explicit when writing queries, failing to be
explicit can generate very puzzling results.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/RADBooks/delphibooks.html
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist" - Ayn Rand