Board index » delphi » MySQL 3.X SQL question

MySQL 3.X SQL question


2003-12-02 12:21:50 PM
delphi267
I have two tables, a Cities table and a lendersInCities table
Cities table has the following fields:
id (int), state(varchar), cityname(varchar)
LendersInCities Table fields:
id (int), cityid(int), lenderid(int)
What I am wanting is SQL that will give me the remaining cities (cities not
in the lendersincities table) by lenderID.
For example, I pick lenderID "1". I'd like the SQL that would give me
the cities NOT posted into the LenderInCities Table.
MySQL (ver 3) does not support sub selects.
This is what I have, but it flakes out for some reason?
select cities.CityName, cities.state, lendersincities.* from cities,
lendersincities where (lendersincities.lenderid = :lenderID) and
(lendersincities.cityid <>cities.id)
Any help would be appreciated... I am thinking I can not see the forest
through the trees! it is been a long day, any help would be appreciated!
Curt
 
 

Re:MySQL 3.X SQL question

What's the error message?
George Christoforakis.
"Curt Krueger" <XXXX@XXXXX.COM>writes
Quote

I have two tables, a Cities table and a lendersInCities table

Cities table has the following fields:

id (int), state(varchar), cityname(varchar)

 

Re:MySQL 3.X SQL question

"George Christoforakis"
Quote
What's the error message?
George Christoforakis.

Hi George,
It does not give me an error, it gives me erroneous results. When I had
just a few (2 lenders) "lenders" in the lendersinCities table, all was well,
now it produces double (two city names) cities for cities that should be
removed (if the logic in the query was correct). Distinct won't do it
because the cities that should not be in there are.
Here's the SQL again:
select cities.cityName, cities.state, lendersincities.* from cities,
lendersincities where (lendersincities.lenderid = :lenderID) and
(lendersincities.cityid <>cities.id)
thanks,
Curt
 

Re:MySQL 3.X SQL question

try the group by clause.
George Christoforakis.
"Curt Krueger" <XXXX@XXXXX.COM>writes
Quote

"George Christoforakis"

>What's the error message?
>George Christoforakis.
>

Hi George,
It does not give me an error, it gives me erroneous results. When I
had
just a few (2 lenders) "lenders" in the lendersinCities table, all was
well,
 

Re:MySQL 3.X SQL question

There are a few help lines in the mysql manual which explain how to use the
subselect substitution in versions < 4.1.x
Version 4.1.x alpha supports subselects. I had something like that you want
to do and I changed to 4.1 alpha.
George Christoforakis.
"Curt Krueger" <XXXX@XXXXX.COM>writes
Quote

"George Christoforakis"

>What's the error message?
>George Christoforakis.
>

Hi George,
It does not give me an error, it gives me erroneous results. When I
had
just a few (2 lenders) "lenders" in the lendersinCities table, all was
well,
now it produces double (two city names) cities for cities that should be
removed (if the logic in the query was correct). Distinct won't do it
because the cities that should not be in there are.

Here's the SQL again:

select cities.cityName, cities.state, lendersincities.* from cities,
lendersincities where (lendersincities.lenderid = :lenderID) and
(lendersincities.cityid <>cities.id)

thanks,
Curt