SELECT DOES NOT WORK ?!

[aas1611]

I have a strange problem.

I thought the following was a simple query:

select count(*) from adresse, adr_anfrage
where adresse.kontonr = adr_anfrage.kontonr

This query gave me zero, even though there are only two records in table adr_anfrage and these two records (kontonr) exist in adresse. Table adresse has 150K records and field kontonr is one of the primary keys in adresse.
All my other similar queries work just fine. If it matters, I'm using MySQL 4.1.13.

One thing you should probably know, though, the records in adr_anfrage come from an CSV file format
(uploaded from an PHP script, my version is 4.3.10).

Can somebody tell me what's wrong? Thanks!

Andre

 

[r937]

could you please run a couple of single table queries

first, run a query against adr_anfrage and paste the two rows here so we can see the values of the kontonr

then run a separate query on adresse with a WHERE clause to select the kontonrs that were returned by the first query

if you don't get any results, then it's because there are no matching values

r937.com | rudy.ca

 

[aas1611]

Here it is,

select * from adr_anfrage

result:
+----------+-------------+
| blz | kontonr |
+----------+-------------+
| 16080000 | 4674905700 |
| 49051990 | 0003567112 |
+----------+-------------+

Then,

select blz, kontonr from adresse where kontonr = 0003567112 or kontonr = 4674905700

result:
+----------+------------+
| blz | kontonr |
+----------+------------+
| 16080000 | 4674905700 |
| 49051990 | 0003567112 |
+----------+------------+

it retrieved two rows.

But when I do the following,

select adresse.blz, adresse.kontonr from adresse , adr_anfrage
where adresse.kontonr = adr_anfrage.kontonr

result:
+----------+------------+
| blz | kontonr |
+----------+------------+
| 49051990 | 0003567112 |
+----------+------------+

it only retrieved one row. Isn't it supposed to be two rows?

 

[r937]

yes it's supposed to be two rows

if those are numeric columns, then i don't know

if they are character columns, you may have a space in one of them

r937.com | rudy.ca

 

[aas1611]

the column type is varchar(100) (for kontonr in both tables). Like I said, the records in adr_anfrage come from a CSV file.
Do you think it might be the problem?

I will change it to int or some numeric and see what happens.

 

[ericbrunson]

Like Rudy said, you need to make sure there are no leading or trailing spaces in the field values.

 

[aas1611]

I found the problem! I changed the data type on the columns in adr_anfrage to INT, and it worked. But it's still kind of strange, because the columns in adresse are still varchar. I thought varchar would convert automatically to number if the record is all numbers.

By the way, I had checked and there was no leading or trailing spaces.

Well, at least the problem is solved.