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 [email protected]} 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

[email protected]        [email protected]         

[email protected]        [email protected]

 

'info' table

email                                price

[email protected]        32.99         

[email protected]        31.49

 

The outcome list I am trying to display is:

[email protected] friends prices

[email protected]    £32.99

[email protected]    £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='[email protected]'

 

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