0

Hi Everyone-

We are building a database that needs table(purchaser) to check table(paypal) for a matching telephone number. Table(paypal) has 'purchaser telephone' as PK and table(purchaser) has 'purchaser telephone' as FK. I can insert and see the data in both tables, but running a select query returns an empty result. The format for datasets in both tables is decimal(19,5) to allow for international numbers and a 5 digit extension. We are running innodb. We have also tried it with email addresses and got the same (they are varchar). The queries that return empty are below.

SELECT purchaser telephone 
FROM purchaser
WHERE purchaser telephone = paypal.purchaser telephone

SELECT * FROM `paypal` WHERE `payer_email`= 'purchaser.p_email'

SELECT * FROM `paypal` WHERE `purchaser telephone` LIKE 'purchaser.purchaser telephone'

Any advice is appreciated! Thanks!

3
Contributors
6
Replies
8
Views
5 Years
Discussion Span
Last Post by MitchellJ
0

hi MitchellJ,
You cant directly check other table value.. you should go with join operations.you can write query like this

SELECT purchaser.purchaser telephone 
FROM purchaser  INNER JOIN paypal
ON purchaser.purchaser telephone = paypal.purchaser telephone

for more help on join operation click

0

Okay great that works. Thanks so much, I guess I didn't fully understand the role of primary and foreign keys.

0

Okay so it actually isn't working. It seems the table was renamed a bit, but this query isn't working even when modified. Log below...

SELECT purchaser.purchaser telephone
FROM purchaser
INNER JOIN paypal_ipn ON purchaser.purchaser telephone = paypal_ipn.purchaser telephone
LIMIT 0 , 30

MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'telephone = paypal_ipn.purchaser telephone
LIMIT 0, 30' at line 3

Thanks again...

0

You can't have spaces in your column names, so purchaser telephone needs to be something like purchaser_telephone (at the moment you are saying select purchaser.purchaser and then have a random word telephone afterwards)

0

I renamed the columns without spaces and it all works. Thanks everyone!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.