Sql

[shamas21]

Hi All

The following statement will not execute. Can someone tell me what im doing wrong?

Im trying to build a relationship between the two tables to get back specific results.

SELECT forename, surname, letter
FROM customers, table1
WHERE table1.age = customers.age


Thanks

 

[jal]

Hi All

The following statement will not execute. Can someone tell me what im doing wrong?

Im trying to build a relationship between the two tables to get back specific results.

SELECT forename, surname, letter
FROM customers, table1
WHERE table1.age = customers.age


Thanks

What error are you getting?

Also, keep in mind that if the two tables have the same column names, you will have to clarify which table you are referring to:


SELECT C.forename, C.surname, C.letter
FROM customers as C, table1
WHERE table1.age = customers.age

 

[boblarson]

The following statement will not execute.

Also, when you say it won't execute, how are you trying to execute it?

 

[shamas21]

Hi

I am getting a type mismatch error.

If i execute the following statement it runs fine, but when i add the where clause it stops running.


SELECT forename, surname,letter
FROM customers, table1

 

[georgedwilkinson]

Add age from both tables to the select clause and see if they are the same type. Or just look in the 2 tables design to see if age in both tables are the same type.

 

[shamas21]

Add age from both tables to the select clause and see if they are the same type. Or just look in the 2 tables design to see if age in both tables are the same type.

Fantastic. I did what you said and it appeared that in the customers table i had Age as a Text type instead of a number, and in the other table the age was a number type. Anyway, it works perfect.'

But, one thing, how important is it that the two fields that are being related in the WHERE statement that they are both the same data type? Also, how important are primary keys in the matter.

 

[jal]

But, one thing, how important is it that the two fields that are being related in the WHERE statement that they are both the same data type? Also, how important are primary keys in the matter.

Well, you can probably see some of the importance already - it avoids the sort of error messages you got. Also it helps avoid getting back incorrect result sets.

You don't need to join on the primary key. Join on whatever columns in the right table correspond to those of the left table. For best peformance, mark the join-columns as indexed in Design view.

 

[Banana]

While it is true that you can join any columns together (as long they are same type or you do a explicit typecast), it is much, much preferable to always join on keys. The performance will be much faster and you'll get the same results.