May 18, 2009 at 6:17 am
Hi,
I need help and the scenario is as below.
Assuming i have two tables [User] and [UserProfile],
User Table has logindetails where as userprofile table has address, gender and other personal information.
We have some users where in there are records in both user and userprofile table for few users they have not entered their information related to address, gender which was supposed to be in userprofile tabl
Now,
Select *
FROM
LEFT OUTER JOIN UserProfile ON [User].UserID=UserProfile.UserID
IF i execute the above query, the query scans both the tables, now in this for for few there is no records in userprofile and for others there are records in userprofile table along with user table.
what i need to know is, is there any way so that if there are any records in userprofile table then let it scan and fetch records by joining both the user and userprofile tables , if there is no record for some users in userprofile it should not scan the userprofile table and not join user with userprofile table, instead it should fetch records from user table.
will pls let me know is there any way to get this.
With Regards
Dakshina Murthy
May 18, 2009 at 6:45 am
Not exactly sure what you want but here goes
First result - users in BOTH tables
Select *
FROM
INNER JOIN UserProfile
ON [User].UserID=UserProfile.UserID
Second result - users only in table
select *
from
where not exists (select userid from userprofile where userid = .userid)
May 18, 2009 at 6:59 am
Hey Thanks for the reply,
But what i want is, is there any way where in if records are present in the userprofile table then only it should have left outer join with the userprofile table else it should directly fetch records from user table.
My concern is , there might be records where they might have not entered the records for userprofile so , there will be no records in userprofile, if we make use of inner join or left outer join irrespective of the records present in the table [userprofile] it will scan and try to fetch the records, i dont want in this way, it should scan or try to fetch the records if exists in userprofile else only from user table.
with regards
Dakshina Murthy
May 18, 2009 at 7:14 am
can you give some sample tables with values and expected output
May 18, 2009 at 11:23 pm
Hi,
Here i am attaching my scenario example with the explaination, please do the need full.
With Regards
Dakshina Murthy
May 18, 2009 at 11:40 pm
dakshinamurthy (5/18/2009)
My concern is , there might be records where they might have not entered the records for userprofile so , there will be no records in userprofile, if we make use of inner join or left outer join irrespective of the records present in the table [userprofile] it will scan and try to fetch the records, i dont want in this way, it should scan or try to fetch the records if exists in userprofile else only from user table.
How would SQL be expected to know if there's a matching record in the UserProfile table without reading the userprofile table?
The left outer join you have at the beginning is the closest you're going to get (unless I'm misunderstanding what you want). You can change the select clause so that you have ISNull there, that will allow you to show whichever column is not null.
So
SELECT IsNull(UserProfile.Name, User.Name) AS UserName, IsNull(UserProfile.Gender, User.Gender) AS Gender ....
FROM
LEFT OUTER JOIN UserProfile ON [User].UserID=UserProfile.UserID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply