Board index » delphi » SQL "IN" Statement

SQL "IN" Statement

All;

    I have a couple of tables (Master/Detail) that I use the "IN" clause
with and they provide the desired results.  I need to do something similiar
only this SQL statement must return a result that does contain all the
criteria that matches the Master table.  It's a bit difficult to explain but
the below is the current sql statement I use:

select * from city
inner join names on city.names = names.names
where names.names in (12,13,14)
group by city.cityname
order by city.cityname

    This returns all the citys that these three people live in.  This sql
statement acts like an "OR" statement in the "IN" clause.  I need to do the
same thing only I need the "IN" statement to work like an "AND" statement.
Ie:  If all three of the names.names people don't live in the same city, I
want a return result of zero.  I know I could issue the "where/and" clause
three times inside the sql statement, but this sql statement could get huge
depending on the number of names I submit to this query.  Anybody got an
answer to this.

Thanks in advance (D6 Pro/ADO);
Dean Jones

 

Re:SQL "IN" Statement


Quote
>select * from city
>inner join names on city.names = names.names
>where names.names in (12,13,14)
>group by city.cityname
>order by city.cityname

>    This returns all the citys that these three people live in.  This sql
>statement acts like an "OR" statement in the "IN" clause.  I need to do the
>same thing only I need the "IN" statement to work like an "AND" statement.
>Ie:  If all three of the names.names people don't live in the same city, I
>want a return result of zero.  I know I could issue the "where/and" clause
>three times inside the sql statement, but this sql statement could get huge
>depending on the number of names I submit to this query.  Anybody got an
>answer to this.

The only way I can come up with is to use two subQueries.

select * from
city  inner join names  on city.names = names.names
where names.names in (12,13,14)
and City.CityName =
(Select Max(city.CityName) from
city  inner join names  on city.names = names.names
where names.names in (12,13,14)
Group by City.CityName)
and City.CityName =
(Select Min(city.CityName) from
city  inner join names  on city.names = names.names
where names.names in (12,13,14)
Group by City.CityName)

group by city.cityname
order by city.cityname
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL "IN" Statement


Brian;

    Thanks for the quick reply.  That's one hell of a way to turn an 'IN'
clause from an 'OR' to an 'AND' statement.  Let me give it a try.

Thanks again;
Dean Jones

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:h14opt0lk9hiblc1cc4ubj0ecofnuaamj8@4ax.com...

Quote

> >select * from city
> >inner join names on city.names = names.names
> >where names.names in (12,13,14)
> >group by city.cityname
> >order by city.cityname

> >    This returns all the citys that these three people live in.  This sql
> >statement acts like an "OR" statement in the "IN" clause.  I need to do
the
> >same thing only I need the "IN" statement to work like an "AND"
statement.
> >Ie:  If all three of the names.names people don't live in the same city,
I
> >want a return result of zero.  I know I could issue the "where/and"
clause
> >three times inside the sql statement, but this sql statement could get
huge
> >depending on the number of names I submit to this query.  Anybody got an
> >answer to this.

> The only way I can come up with is to use two subQueries.

> select * from
> city  inner join names  on city.names = names.names
> where names.names in (12,13,14)
> and City.CityName =
> (Select Max(city.CityName) from
> city  inner join names  on city.names = names.names
> where names.names in (12,13,14)
> Group by City.CityName)
> and City.CityName =
> (Select Min(city.CityName) from
> city  inner join names  on city.names = names.names
> where names.names in (12,13,14)
> Group by City.CityName)

> group by city.cityname
> order by city.cityname
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:SQL "IN" Statement


In <3b9bd01d$1_2@dnews>, Dean Jones wrote:

Quote

> select * from city
> inner join names on city.names = names.names
> where names.names in (12,13,14)
> group by city.cityname
> order by city.cityname

>  If all three of the names.names people don't
> live in the same city, I
> want a return result of zero.  

Dean

If that means you only want a result returned when two or more people
live in the same city, I think you can do this just by adding a
'having' clause:

group by city.name having count(city.name) > 1

Roger Morton
roger.mor...@bigfoot.com

Re:SQL "IN" Statement


Hi Dean,

Quote
> Ie:  If all three of the names.names people don't live in the same city, I
> want a return result of zero.

If you want a result only when all people live in the same city and not just
more than one, I think you can also use the "having" clause as Roger
mentioned, only it is a little more complicated:

group by city.cityname
having (Count(city.cityname)=
 (select Count(*) from city inner join names on city.names = names.names
 where names.names in (12,13,14)))

Thrse

Re:SQL "IN" Statement


Roger and Thrse;

 I wish to thank you for responding to my earlier post.  I'm still have
trouble with this SQL statement and could use more help if you're offering.
Below I added additional info of the end result I wish to achieve.  I'm
using D6 Pro, ADO, Acess2K.

 I have two tables, first table (city):

Fields:
city (number,pkey), cityname (text)

second table (names):

Fields:
names (number,pkey), city (number), LastName (text)

 The user enters in the pkey of the names table of the persons he wants to
search for.
The query needs to only return a result set if all the names submitted live
in the same city.  For example, "I want to return a city list of where there
is both Jones and Smiths residing in the same city".  If only Jones lives in
a city and not any Smiths, then the result set must not include that city.
Below is the query I thought would work, but the 'IN' clause does an 'OR'
vice an "AND".

 select * from city
 inner join names on city.names = names.names
 where names.names in (12,13,14)
 group by city.cityname
 order by city.cityname

 I've trid the 'Having' clause but am not having any luck with it.  It's not
stopping the 'OR' portion of the 'IN' clause.  I really need to figure this
out and can use any help offered.

Thanks in Advance;
Dean

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3b9c75f2_1@dnews...
Quote
> Hi Dean,

> > Ie:  If all three of the names.names people don't live in the same city,
I
> > want a return result of zero.

> If you want a result only when all people live in the same city and not
just
> more than one, I think you can also use the "having" clause as Roger
> mentioned, only it is a little more complicated:

> group by city.cityname
> having (Count(city.cityname)=
>  (select Count(*) from city inner join names on city.names = names.names
>  where names.names in (12,13,14)))

> Thrse

Re:SQL "IN" Statement


Hi Dean,

Quote
>  I have two tables, first table (city):
> Fields:
> city (number,pkey), cityname (text)
> second table (names):
> Fields:
> names (number,pkey), city (number), LastName (text)

First of all, your SQL statement does not fit with the description of your
tables:

Quote
> inner join names on city.names = names.names

should probably be:

inner join names on city.city = names.city

What we proposed with HAVING should prevent the OR clause, but in order to
do that, you should keep track in your application of the number of values
that are provided in the IN clause. Let us call this TheNumberOfValues. In
your example AListOfValues = 12,13,14 and TheNumberOfValues = 3.

SELECT city.city, cityname
FROM [names] INNER JOIN city ON names.city = city.city
WHERE (((names) In (AListOfValues)))
GROUP BY city.city, cityname
HAVING (((Count(names))=TheNumberOfValues));

This will select the cities where names 12, 13 and / or 14 live, and for
each, the number of records.
If the number of records for the city is equal to the number of values (3)
(meaning that 12, 13 and 14 live in that city), the city will be selected,
otherwise it will not.

Thrse

Re:SQL "IN" Statement


Hey Thrse;

    Thanks for the reply and catching the SQL item.  I think we are getting
closer.  The modified SQL with the having statement works but it will also
return a city name if there are more than one 'Jones' and no 'Smiths'.

Quote
> SELECT city.city, cityname
> FROM [names] INNER JOIN city ON names.city = city.city
> WHERE (((names) In (12,13)))
> GROUP BY city.city, cityname
> HAVING (((Count(names))=2));

    You'd think that this shouldn't be that hard, I've been itching my head
for over a week on this one.  Any other ideas?

Dean

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3ba5a061$1_1@dnews...
Quote
> Hi Dean,

> >  I have two tables, first table (city):
> > Fields:
> > city (number,pkey), cityname (text)
> > second table (names):
> > Fields:
> > names (number,pkey), city (number), LastName (text)

> First of all, your SQL statement does not fit with the description of your
> tables:

> > inner join names on city.names = names.names

> should probably be:

> inner join names on city.city = names.city

> What we proposed with HAVING should prevent the OR clause, but in order to
> do that, you should keep track in your application of the number of values
> that are provided in the IN clause. Let us call this TheNumberOfValues. In
> your example AListOfValues = 12,13,14 and TheNumberOfValues = 3.

> SELECT city.city, cityname
> FROM [names] INNER JOIN city ON names.city = city.city
> WHERE (((names) In (AListOfValues)))
> GROUP BY city.city, cityname
> HAVING (((Count(names))=TheNumberOfValues));

> This will select the cities where names 12, 13 and / or 14 live, and for
> each, the number of records.
> If the number of records for the city is equal to the number of values (3)
> (meaning that 12, 13 and 14 live in that city), the city will be selected,
> otherwise it will not.

> Thrse

Re:SQL "IN" Statement


Hi Dean,

Quote
>  The modified SQL with the having statement works but it will also
> return a city name if there are more than one 'Jones' and no 'Smiths'.

There must be something I don't get, because I understood that the field
"names" was the primary key ot the table "names". How is it possible then to
have duplicates? Do you use yet another table?

Thrse

Re:SQL "IN" Statement


Hey Thrse;

    Thanks for the quick reply.  I do have another field in the query that I
didn't think would have an impact.  Below is the same structure of the
tables I sent before with the additional field

I have two tables, first table (city):

Fields:
city (number,pkey), cityname (text)

second table (names):

Fields:
names (number,pkey), city (number), LastName (text), VehicleMake (number)

VehicleMake is the additional field that the user can use to select which
types of manufacture to search on, ie (chevy, ford).  I build the SQL
statement on the fly determining what the user is searching on during the
build of the SQL.  The result is the same as originally stated, only those
city's that contain both a ford and a chevy needs to be returned.  The user
also can select a last name to add to the query if they desire.  The hardest
part about this entire SQL is building it correctly based on what the user
has decided to search on (lastname, firstname,vehiclemake, vehiclecolor or a
combinations of two or more).  Below is the same SQL only modified for the
vehiclemake:

    My original SQL I thought was a close answer but your SQL took the
master table (city) and made it the child and the child (names) the master.

Quote
> SELECT city.city, cityname
> FROM [names] INNER JOIN city ON names.city = city.city
> WHERE (((vehiclemake) In (12,13)))
> GROUP BY city.city, cityname
> HAVING (((Count(vehiclemake))=2));

    I hope I didn't muck the waters to much.  I already owe you some beer
for the great help you've offered so far.  Right now I'm still going in
circles on this, I can get some of the queries to return results as desired,
but when you select multiple vehiclemakes, the result is not correct.  If
you have some additional guidance, I sure would appreciate it.

Thanks again;
Dean Jones

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3ba64f29$1_1@dnews...
Quote
> Hi Dean,

> >  The modified SQL with the having statement works but it will also
> > return a city name if there are more than one 'Jones' and no 'Smiths'.

> There must be something I don't get, because I understood that the field
> "names" was the primary key ot the table "names". How is it possible then
to
> have duplicates? Do you use yet another table?

> Thrse

Re:SQL "IN" Statement


Hi Dean,

Here is something that could solve your problem for vehiclemake. However if
you need to combine several criteria this might be more difficult...

SELECT city.city, cityname
FROM (SELECT DISTINCT city, vehiclemake FROM [names]) AS A INNER JOIN city
ON A.city = city.city
WHERE (A.vehiclemake) In (12,13)
GROUP BY city.city, cityname
HAVING (Count(A.vehiclemake)=2);

Thrse

Re:SQL "IN" Statement


Hi Thrse;

    yes, Yes, YES!!! I've done some op checking and it appears to be finally
working.  I wish to thank you greatly for your help and patience.  I've
learned a few things on SQL which I owe to you.

Thanks you again;
Dean Jones

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3ba7480e_2@dnews...
Quote
> Hi Dean,

> Here is something that could solve your problem for vehiclemake. However
if
> you need to combine several criteria this might be more difficult...

> SELECT city.city, cityname
> FROM (SELECT DISTINCT city, vehiclemake FROM [names]) AS A INNER JOIN city
> ON A.city = city.city
> WHERE (A.vehiclemake) In (12,13)
> GROUP BY city.city, cityname
> HAVING (Count(A.vehiclemake)=2);

> Thrse

Other Threads