asked on

Insert Into TableA... From TableB Left Outer Join

Using SQL Express 2005

I have a table (tblDefaultPic) that contains one record, it is priming table, the data in it is used to create a default record in another table (tblPics) when a new user is created.

The key field in all tables is UserID which is a UniqueIdentifier.
The field UserID definately exists in table (aspnet_users)

Can't quite seem to get my head around this, maybe its getting late, looking for a quick answer if possible, thanks.

This is what I have, but the select returns 0 records, hmmm.

INSERT INTO tblPics ([Filename], FileExtension, Picture, [Size], [Type], Created, Thumb, Album, Active, PrimaryPic, UserID)

SELECT      D.[Filename], D.FileExtension, D.Picture, D.[Size], D.[Type], D.Created, D.Thumb, D.Album, D.Active, D.PrimaryPic, @UserID
FROM      tblDefaultPic AS D Left OUTER JOIN
aspnet_Users AS U ON D.UserID = U.UserId LEFT OUTER JOIN
            tblPics as Px ON D.UserID = Px.UserID

WHERE      (Px.UserID IS NULL)AND
            (D.UserID IS NULL)

Alan Warren

ASKER

This actually returns the correct rows, but all the fields except UserId are NULL

SELECT      D.[Filename], D.FileExtension, D.Picture, D.[Size], D.[Type], D.Created, D.Thumb, D.Album, D.Active, D.PrimaryPic, U.UserID
FROM      aspnet_Users AS U Left OUTER JOIN
tblDefaultPic AS D ON U.UserID = D.UserId LEFT OUTER JOIN
            tblPics as Px ON U.UserID = Px.UserID

WHERE      (Px.UserID IS NULL)AND
            (D.UserID IS NULL)

Returns:
NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      39CAC0A4-2ACE-45CF-9832-01A2A31C1651
NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      16A4DAAD-05EC-4E50-9913-023E2EE312D5
NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      AF1AFE3E-91D7-4BFD-91ED-0DA3289D5400
NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      FEEB2322-D400-40E6-86B2-11D28B75D1BB
NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      C93919C6-7D36-4EF8-94CB-29F0EC6CB1E3

Alan

ASKER CERTIFIED SOLUTION

JR2003

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alan Warren

ASKER

Excellent!
Thank you for the quick response.

Alan ";0)