Mysql select returns empty result

[MitchellJ]

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!

[anand01]

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

[MitchellJ]

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

[MitchellJ]

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...

[simplypixie]

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)

[anand01]

ya foll simplypixie's suggestion

[MitchellJ]

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