[SOLVED] Querying from two tables at once

[tibberous]

I have two tables, one called users and one called contact. They both have a field called id, with a one to one relationship.

 

Ex:

 

Users:

 

id Name

6 Trent

 

Contact:

 

id PhoneNumber

6 555-555-5555

 

I want to make a select query to grab the name with the id of 6, and the phone number with the id of 6, but I forget how.

 

Can someone help me out?

 

Thanks

[Renlok]

SELECT u.Name, c.PhoneNumber FROM users u

LEFT JOIN contact ON ( u.id = c.id )

WHERE id = 6

 

this will join the two tables and pull out the persons name and their phone number

[aschk]

If they have a 1-to-1 relationship why are they in 2 tables?

 

p.s. i know what you're going to say, i just want to hear you say it...

[tibberous]

Just to organize it more, and because the contact data isn't used very often.

 

That worked, thanks. Isn't there also a way to do it with subqueries? I'm trying to get better at mysql, since for a long time I have just used the minimum statements and done all the processing with PHP.

 

 

[aschk]

You could do it with a subquery, but it's much of a muchness and you're probably better off with a LEFT JOIN purely for presentational purposes.

Here is your subquery:

SELECT u.Name as Name
         , (SELECT c.PhoneNumber FROM contact c WHERE c.id=u.id) as PhoneNumber
FROM users u
WHERE id = 6