querys linking mysql tables

[unistake]

Hi all,

I am trying to link up two tables in a mysql database.

 

I am trying to create a script to do with refering. A user will be able to view their friends they have refered and see how much money they have in their account.

 

There are two tables I am using.

In table 'members' there are 2 columns 'email' and 'friend', both columns contain email addresses.

In table 'info' there are also 2 columns 'email' and 'price'.

 

In the members table, the field values in the friend column are the same as the values in the info table in the email column.

 

I am trying to SELECT {all the email addresses from table 'members' where friend=$user@hotemail.com} and then, once these email addresses have been selected, display all the friends emails along with their price in simple list. 

 

'members' table

email                                friend

friend1@hotletter.com        user@hotemail.com         

friend2@hotletter.com        user@hotemail.com

 

'info' table

email                                price

friend1@hotletter.com        32.99         

friend2@hotletter.com        31.49

 

The outcome list I am trying to display is:

user@hotemail.com friends prices

friend1@hotletter.com    £32.99

friend2@hotletter.com    £31.49

 

 

Thanks for the help

 

 

 

[cbolson]

Hi,

 

I think that you have got a little mixed up, either in your explanation or in the example of how the database data looks.

You said:

In the members table, the field values in the friend column are the same as the values in the info table in the email column.

However, from the example data, it looks like the coinciding column is the "email" column.

 

Also, it is a good idea to always have a unique numeric column in all tables and use this for referencing data between tables. (just a tip)

 

 

Anyway, assuming you are using mysql database you could join the tables something like this:

 

SELECT t1.friend, t2.email, t2.price FROM members AS t1 LEFT JOIN info AS t2 ON t2.email=t1.email WHERE t1.friend='user@hotemail.com'

 

That might not be exactly right but should be a good starting point.

 

Chris

 

[unistake]

Hi Chris, yeah my mistake! The example i have given is correct, ignore that

"In the members table, the field values in the friend column are the same as the values in the info table in the email column."

 

Ill have a play around with the code

 

Thanks