LEFT JOIN ???
-
HI I am having problems with the join i need to do to get the required result. This is the problem: I have two tables one with user data and one with user profile image. I need to join these tables to get each user with his profile image (user might have many profile pictures but only one is set to be default=1). But some users might not have profile images uploaded yet. So the problem wen i do left join i get an entry with users having no profile images and also i get TWO entries for the same users that have a profile image : one with the right answer and one with an empty image. But i want my join to select empty image ONLY wen the user has no image uploaded. Any help??
/\|-||\/|/\|)
-
HI I am having problems with the join i need to do to get the required result. This is the problem: I have two tables one with user data and one with user profile image. I need to join these tables to get each user with his profile image (user might have many profile pictures but only one is set to be default=1). But some users might not have profile images uploaded yet. So the problem wen i do left join i get an entry with users having no profile images and also i get TWO entries for the same users that have a profile image : one with the right answer and one with an empty image. But i want my join to select empty image ONLY wen the user has no image uploaded. Any help??
/\|-||\/|/\|)
I don't understand how unless you actually have a placeholder row for an empty image. If that is the case then it should be simple to add something into the WHERE clause to filter that out. You might want to post the relevent parts of the datamodel and the query you are using. It might make it easier to visualise your actual situation.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live. Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ... * Reading: SQL Bits My website
-
I don't understand how unless you actually have a placeholder row for an empty image. If that is the case then it should be simple to add something into the WHERE clause to filter that out. You might want to post the relevent parts of the datamodel and the query you are using. It might make it easier to visualise your actual situation.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live. Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ... * Reading: SQL Bits My website
SELECT C.UserID FROM Contact AS C LEFT JOIN Album AS A ON A.UserID = C.UserID AND A.Type = 1 LEFT JOIN AlbumImage AS AI ON AI.AlbumID = A.AlbumID AND AI.'Default' = 1 Contact: UserID pk ... one to many Album: AlbumID pk UserID fk Type ... one to many AlbumImage: ImageID pk AlbumID fk Default a contact may have many albums one of which is for profile pictures and all the profile pictures can be selected from album image if a user did not upload a picture yet he will have an album for profile picture (of type=1) but without album images i want to select this user so i can give him throught the code later a default picture of my choice
/\|-||\/|/\|)
-
SELECT C.UserID FROM Contact AS C LEFT JOIN Album AS A ON A.UserID = C.UserID AND A.Type = 1 LEFT JOIN AlbumImage AS AI ON AI.AlbumID = A.AlbumID AND AI.'Default' = 1 Contact: UserID pk ... one to many Album: AlbumID pk UserID fk Type ... one to many AlbumImage: ImageID pk AlbumID fk Default a contact may have many albums one of which is for profile pictures and all the profile pictures can be selected from album image if a user did not upload a picture yet he will have an album for profile picture (of type=1) but without album images i want to select this user so i can give him throught the code later a default picture of my choice
/\|-||\/|/\|)
I think you need to add a
WHEREclauseWHERE AI.AlbumID IS NULLAlso, ifAlbumwill always exist at least once with aTypeof1for every contact then that should be changed to anINNER JOIN. The join toAlbumImagestays as aLEFT JOIN.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live. Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ... * Reading: SQL Bits My website
-
I think you need to add a
WHEREclauseWHERE AI.AlbumID IS NULLAlso, ifAlbumwill always exist at least once with aTypeof1for every contact then that should be changed to anINNER JOIN. The join toAlbumImagestays as aLEFT JOIN.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live. Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ... * Reading: SQL Bits My website
I try this wat i get is trully all the results that i need except the users that has a profile picture ill get the result that is without a profile picture. So this way ill get all users without a profile picture eventhough they have one. But wat i need is to get the profile picture for the people who has one and get null for people who don't thx for ur suggestion though
/\|-||\/|/\|)
-
HI I am having problems with the join i need to do to get the required result. This is the problem: I have two tables one with user data and one with user profile image. I need to join these tables to get each user with his profile image (user might have many profile pictures but only one is set to be default=1). But some users might not have profile images uploaded yet. So the problem wen i do left join i get an entry with users having no profile images and also i get TWO entries for the same users that have a profile image : one with the right answer and one with an empty image. But i want my join to select empty image ONLY wen the user has no image uploaded. Any help??
/\|-||\/|/\|)
I don't understand the difficulty you are having. You clearly specify the qualifications for the join in your question. Did you not integrate
Defaultinto theJOIN? A sample of your code would be helpful if the query below does not solve your issue.SELECT u.UserId, u.UserName, u.OtherUserStuff, p.UserImage FROM [UserTable] u LEFT JOIN [ProfileTable] p ON ((u.UserId = p.UserId) AND (p.Default = 1)) -
I don't understand the difficulty you are having. You clearly specify the qualifications for the join in your question. Did you not integrate
Defaultinto theJOIN? A sample of your code would be helpful if the query below does not solve your issue.SELECT u.UserId, u.UserName, u.OtherUserStuff, p.UserImage FROM [UserTable] u LEFT JOIN [ProfileTable] p ON ((u.UserId = p.UserId) AND (p.Default = 1))I did integrate default into the join the problem is simple: If a user has more than one profile picture but only one is selected as default and if i do the join as u and i suggested ill get two results one witht the profile picture and another one without the profile picture. It is this second result that i wish to eliminate thanks anyway for your help :)
/\|-||\/|/\|)
-
I did integrate default into the join the problem is simple: If a user has more than one profile picture but only one is selected as default and if i do the join as u and i suggested ill get two results one witht the profile picture and another one without the profile picture. It is this second result that i wish to eliminate thanks anyway for your help :)
/\|-||\/|/\|)
You have lost me. Given the join I specified (and I think it mirrors your attempt) the only way you should get more than one row per user is if there are Duplicate User Records or muliple Default=1 per user.
-
You have lost me. Given the join I specified (and I think it mirrors your attempt) the only way you should get more than one row per user is if there are Duplicate User Records or muliple Default=1 per user.
By definition the Left join gives two results. I have read something similar to the following in many references about the left join: "If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table." I want that this is y i chose the left join but the thing is that apparently the left join gave an extra record for all users having a profile picture with all columns set to null for the right table but i need this to happen ONLY wen there is no profile picture. This is the question how can I make this diffrenciation?? Ps: There is no multiple users Records nor there is multiple default = 1 per user Btw wen I add the default = 1 in the where clause i get strict result about only users with profile pictures and users without are neglected...
/\|-||\/|/\|)
-
By definition the Left join gives two results. I have read something similar to the following in many references about the left join: "If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table." I want that this is y i chose the left join but the thing is that apparently the left join gave an extra record for all users having a profile picture with all columns set to null for the right table but i need this to happen ONLY wen there is no profile picture. This is the question how can I make this diffrenciation?? Ps: There is no multiple users Records nor there is multiple default = 1 per user Btw wen I add the default = 1 in the where clause i get strict result about only users with profile pictures and users without are neglected...
/\|-||\/|/\|)
You can't put the
default=1in theWHEREclause. It has to be put in theJOINclause as I listed in a previous post. TheWHEREclause will eliminate non-matching rows (a null in the default column does not match 1). I have long since stopped qualifying my joins in theWHEREclause because of this type of issue. The sample query I listed before should get you your desired results if your tables are structured as you have stated. If you are getting duplicate results, there is something in your structure that I don't understand. I do LEFT JOINS all the time without the side effect you are getting. Can you post the actual query you are using?