0

I am new to inner joins and i have a query that has had me stumped for a few days, I basically have a query in which I am trying to pull out records from rows that match on different tables this is what I have..

var sql = "SELECT  t.*, 
                   p.* 
             FROM Threadposts t 
                  INNER JOIN profiles p 
                        ON t.profileID = p.ProfileID 
            WHERE t.threadID = @id";

I am getting the results from Threadposts t but profile p is always null. What the query is supposed to say is this

Get all of the results from table Threadposts and profiles where Threadpost.threadID= @id and also get the profile rows where t.profileID = p.ProfileID

The @id is a forum page so essentially if your on forum page 20 then I want to get all the users that have posted on forum page 20 hence the Threadpost.thread= @id . After we get the user replies then lets get they're profile information to also display they're names etc. above they're post hence t.profileID = p.ProfileID . As stated above I get all of they're replies so Threadpost works but something is going on with the Profiles, what are things I can do to fix that?

Threadpost has fields: profileID and ThreadID profileID is the unique identification that each user has and ThreadID is the page number that corresponds to a forum posted in. When someone clicks on Forum page 20 then the id=20 which is then matched with ThreadID of 20 and each of those rows will have a ProfileID of users; I want to use that ProfileID and match it with the profileID in the profiles table so i can get the users name and location.

user3329640
  • 121
  • 2
  • 11
  • Tell more about your tables – Alexander Feb 27 '14 at 06:55
  • i think you don't have the records in the profile table for the id. – Suraj Shrestha Feb 27 '14 at 06:57
  • profile p is a shorthand notation that can be used for the tables http://stackoverflow.com/questions/20492071/simple-inner-join-result-with-dapper . I am using dapper and I have the fields matching could it be that since ThreadID on a where and it is only in the threadpost table that profile gets ignored? – user3329640 Feb 27 '14 at 07:03
  • 1
    Your select statement looks fine. So it seems the problem is somewhere else. Maybe there are empty records in the profile table only containing the profile id and nothing more? Or more likely you are not accessing the records properly in php or whatever language you are using to show the fetched data. – Thorsten Kettner Feb 27 '14 at 07:03
  • Likely you have nulls as values in p for each thread id and you're selecting them out. If there weren't matching rows, you shouldn't get any result at all, so rows should be matching – Allan S. Hansen Feb 27 '14 at 07:10
  • It has value but since I am dealing with mvc3 things can get tricky but I just wanted to make sure that it wasn't the actual sql that had issues. – user3329640 Feb 27 '14 at 07:11
  • oh ok, I will go back to my IDE and see exactly were this thing is having issues. – user3329640 Feb 27 '14 at 07:13

1 Answers1

0

It appears to be a data issue. If you are returning records, then it sounds as though your profile records have null values. What happens if you run the following query?

select top 1000 * from profile;

Which columns have data?

user3358344
  • 193
  • 1
  • 9