CODEWITHSUNDEEP
sql
Rome Torres
Rome Torres

Reputation: 1081

SQL How can I create an inner join from these 2 tables

I am just learning sql and I am creating a social network as a learning experience . I have 2 tables called Streams and Votes, Streams pulls user created content and Votes stores the content that people have liked . What I am trying to figure out is how can I return the data from both tables to check if a user a liked a particular post being shown . For instance this is how both my tables look . If you see they both have a field in common stream_id and they both have number 278. How can I do an inner join that checks to see if there are any common stream_ID in both tables ? This is the sql code that I use that gets me the Stream data

Query 1

select post,profile_id,
votes,id as stream_id FROM streams WHERE latitudes>=28.1036 AND
            28.9732>=latitudes AND longitudes>=-81.8696 AND -80.8798>=longitudes 
            order by id desc limit 10

The User ID is 11 and both Streams.profile_id and Votes.my_id are the same field . I have tried this SQL query but this only returns in total 1 result . Again I would like to return all results from the Streams table which I do in query 1 and also add another column to the results from the Votes table where Votes.stream_id=Streams.ID because it'll show that the particular user has liked that post. Any hemp would be great

Query 2

select s.post,s.profile_id,
s.votes,s.id as stream_id, v.my_id as ID  FROM streams s inner join Votes v on (s.id = v.stream_id) WHERE latitudes>=28.1036 AND
            28.9732>=latitudes AND longitudes>=-81.8696 AND -80.8798>=longitudes 
            order by id desc limit 10

Streams Streams Table

Votes Votes Table

Upvotes: 1

Views: 55

Answers (2)

dst3p
dst3p

Reputation: 1038

You need to use a LEFT OUTER JOIN instead of an INNER JOIN. 

SELECT s.post, s.profile_id, s.votes, s.id as stream_id, v.my_id as ID  
FROM streams s 
LEFT OUTER JOIN Votes v on (s.id = v.stream_id) WHERE latitudes>=28.1036 AND
        28.9732>=latitudes AND longitudes>=-81.8696 AND -80.8798>=longitudes 
        order by id desc limit 10

For more information about joins, look here.

Upvotes: 1

Imtiaz Buckas
Imtiaz Buckas

Reputation: 13

I think that you are looking for a LEFT JOIN

 select s.post,s.profile_id,
     s.votes,s.id as stream_id, v.my_id as ID  FROM streams s LEFT JOIN Votes v on (s.id = v.stream_id) WHERE latitudes>=28.1036 AND
                 28.9732>=latitudes AND longitudes>=-81.8696 AND -80.8798>=longitudes 
                 order by id desc limit 10

Upvotes: 1

Related Questions