0

I've been trying to get my head around how to use a single query to select data from two of my tables. If anybody can suggest a better way than a single query, I'm all ears! Previously I would do this using two queries which I could make work easily although I'm led to believe that a single query would be better, hence trying to learn.

One of my tables resembles this in a cut down form. Call this table "member": 

ID  |  firstName  |   lastName  | networkingID

And the other table which I'll call "networking": 

ID  |  websiteURL | facebookURL | twitterURL


What I'm trying to do is run a query on the table member like:

SELECT * FROM `member` WHERE `ID`=2

Which returns the data from the table member.

However I also wish to return the relating value from the table networking. The column networkingID in the table member is the ID of the row in networking.

How would I go about doing this?

So far, I have experimented using all of the JOINs that I was able to find through Google but I am unable to make it work. My best result was with a LEFT JOIN where all of the columns were present but the results from the networking table were all NULL.

Improve this question
5
  • 1
    so when you do an inner or outer join you're getting null values from the networking table? how many rows are you talking about in each table? have you confirmed that the ID is in fact the common value between the two? Left join will give you all the rows from the first table and the rows from the second table where there is a match OR null values for the second table if there isn't a match. sounds like the member.networkingId = networking.id isn't what you think it should be Commented Sep 26, 2012 at 15:28
  • Is there a foreign key constraint on networkid in the member table? Commented Sep 26, 2012 at 15:38
  • Just a very stupid original author. Somehow, despite remembering adding the test values, the test values were missing from the database; I think that explains the NULL result. You can tell it's been a long few days! Commented Sep 26, 2012 at 15:40
  • 1
    understandable. seems most of the "answerers" didn't really read the question. key is to remember what the different types of joins do and what the result set should be. if it isn't what you expect then look at the data to see if there is some anomaly Commented Sep 26, 2012 at 15:43
  • 1
    I had never used any form of join before in the past and wasn't entirely sure on its use after a quick search so just presumed it was a coding error. This will certainly teach me for the future. Commented Sep 26, 2012 at 15:45

5 Answers 5

Reset to default
3 
SELECT * FROM member
LEFT JOIN networking
ON member.networkingID=networking.ID
WHERE member.ID=2
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for cleaning that up, Smamatti :)
0

Simple join, between a common id. Inner join will ensure there are records in the networking table, otherwise it won't show that member. you can replace it with a LEFT JOIN if you want all the member rows regardless if they have anything joined in the network table

 SELECT * FROM member m 
      INNER JOIN networking n 
 ON (m.networkingID = n.id) 
 WHERE m.id = 2;
Improve this answer

Comments

0 
select
   *
from
   member as m
   left outer join
      networking as n
   on
      m.networkingID=n.ID
Improve this answer

Comments

0

A JOIN should work.

SELECT * FROM member, networking WHERE member.ID=2 AND member.networkingID=networking.ID

This will return an empty result if there's no networking data for member.ID=2. If you want to get a result in this case, you can try LEFT JOIN.

SELECT * FROM member LEFT JOIN networking ON member.networkingID=networking.ID WHERE member.ID=2
Improve this answer

Comments

0 
select * from member a, networking b 
where a.networkingID=b.ID and a.ID = 2
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.