• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

SQL statement help

Hello experts.

I have a table storing user updates and another table storing icons for those updates. At the moment it works where it gets all updates for a user based on User id. I am now wanting to join another table "aspnet_Membership" and to say.

only show user updates where aspnet_Membership.CreateDate > tbl_latestupdates.DateAdded

records from the aspnet_Membership table are retrieved from a variable called strUserId so I'd imagine it would be WHERE aspnet_Membership = " + strUserId

Can anyone help?

Thanks


sb.Append("SELECT TOP 8 ");
        sb.Append("tbl_latestupdates.LULinkText_" + up.UserLanguage + " LULinkText, ");
        sb.Append("tbl_latestupdates.LULinkURL, ");
        sb.Append("tbl_latestupdates.DateAdded, ");
        sb.Append("tbl_latestupdates.LUUserId, ");
        sb.Append("tbl_latestupdatesicons.LUIconPath ");
        sb.Append("FROM tbl_latestupdates ");
        sb.Append("INNER JOIN tbl_latestupdatesicons ");
        sb.Append("ON tbl_latestupdates.LUImageId = tbl_latestupdatesicons.LUIconId ");
        sb.Append("WHERE (tbl_latestupdates.LUUserId = @UserProfileId) ");
        sb.Append("OR (tbl_latestupdates.LUUserId = - 1) ");
        sb.Append("ORDER BY tbl_latestupdates.DateAdded DESC");

Open in new window

0
thomasmutton
Asked:
thomasmutton
  • 4
  • 3
  • 2
2 Solutions
 
SujithData ArchitectCommented:
Assuming that strUserId returns a single row from the aspNetMembership table.
	sb.Append("SELECT TOP 8 ");
        sb.Append("tbl_latestupdates.LULinkText_" + up.UserLanguage + " LULinkText, ");
        sb.Append("tbl_latestupdates.LULinkURL, ");
        sb.Append("tbl_latestupdates.DateAdded, ");
        sb.Append("tbl_latestupdates.LUUserId, ");
        sb.Append("tbl_latestupdatesicons.LUIconPath ");
        sb.Append("FROM tbl_latestupdates ");
        sb.Append("INNER JOIN tbl_latestupdatesicons ");
        sb.Append("ON tbl_latestupdates.LUImageId = tbl_latestupdatesicons.LUIconId ");
	sb.Append("CROSS JOIN aspnet_Membership ");
        sb.Append("WHERE (tbl_latestupdates.LUUserId = @UserProfileId) ");
        sb.Append("OR (tbl_latestupdates.LUUserId = - 1) ");
        sb.Append("AND aspnet_Membership = " + strUserId + " ");
        sb.Append("ORDER BY tbl_latestupdates.DateAdded DESC");
 

Open in new window

0
 
thomasmuttonAuthor Commented:
Hi, I tried this but it came up with this error.

Incorrect syntax near 'fa'.

on

GridView1.DataBind();
sb.Append("SELECT TOP 8 ");
        sb.Append("tbl_latestupdates.LULinkText_" + up.UserLanguage + " LULinkText, ");
        sb.Append("tbl_latestupdates.LULinkURL, ");
        sb.Append("tbl_latestupdates.DateAdded, ");
        sb.Append("tbl_latestupdates.LUUserId, ");
        sb.Append("tbl_latestupdatesicons.LUIconPath ");
        sb.Append("FROM tbl_latestupdates ");
        sb.Append("INNER JOIN tbl_latestupdatesicons ");
        sb.Append("ON tbl_latestupdates.LUImageId = tbl_latestupdatesicons.LUIconId ");
        sb.Append("CROSS JOIN aspnet_Membership ");
        sb.Append("WHERE (tbl_latestupdates.LUUserId = @UserProfileId) ");
        sb.Append("OR (tbl_latestupdates.LUUserId = - 1) ");
        sb.Append("AND aspnet_Membership.UserId = " + strUserId + " ");
        sb.Append("ORDER BY tbl_latestupdates.DateAdded DESC");

Open in new window

0
 
thomasmuttonAuthor Commented:
and yes, every record in the aspnet_Membership table has its own unique UserId
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aflockhartCommented:
If you are comparing string values you need to enclose them in single quotes.

e.g.  (notice the extra quote marks before and after strUserId) ...

sb.Append("AND aspnet_Membership.UserId = '" + strUserId + "' ");
0
 
SujithData ArchitectCommented:
yes the single quotes should do.
0
 
thomasmuttonAuthor Commented:
Hi guys, putting the single quotes worked fine. I am still having problems with this tho.

Basically if we wanted to have the update sent to everyone, we would put the latest update User Id as -1

I think there is something wrong with the logic as it says WHERE the user id = 13 OR latest update UserId = -1 AND Membership User Id = whatever

should the latest update User Id be before the cross join?
StringBuilder sb = new StringBuilder();
        sb.Append("SELECT TOP 8 ");
        sb.Append("tbl_latestupdates.LULinkText_" + up.UserLanguage + " LULinkText, ");
        sb.Append("tbl_latestupdates.LULinkURL, ");
        sb.Append("tbl_latestupdates.DateAdded, ");
        sb.Append("tbl_latestupdates.LUUserId, ");
        sb.Append("tbl_latestupdatesicons.LUIconPath ");
        sb.Append("FROM tbl_latestupdates ");
        sb.Append("INNER JOIN tbl_latestupdatesicons ");
        sb.Append("ON tbl_latestupdates.LUImageId = tbl_latestupdatesicons.LUIconId ");
        sb.Append("CROSS JOIN aspnet_Membership ");
        sb.Append("WHERE (tbl_latestupdates.LUUserId = @UserProfileId) ");
        sb.Append("OR (tbl_latestupdates.LUUserId = -1) ");
        sb.Append("AND aspnet_Membership.UserId = '" + strUserId + "' ");
        sb.Append("AND tbl_latestupdates.DateAdded > aspnet_Membership.CreateDate ");
        sb.Append("ORDER BY tbl_latestupdates.DateAdded DESC");

Open in new window

0
 
thomasmuttonAuthor Commented:
I know what is wrong with this but i dont know how to solve it.

if tbl_latestupdates.LUUserId = -1 then the aspnet_Membership record will not exist.
sb.Append("WHERE (tbl_latestupdates.LUUserId = @UserProfileId) ");
        sb.Append("OR (tbl_latestupdates.LUUserId = -1) ");
        sb.Append("AND aspnet_Membership.UserId = '" + strUserId + "' ");
        sb.Append("AND tbl_latestupdates.DateAdded > aspnet_Membership.CreateDate ");

Open in new window

0
 
aflockhartCommented:
Depends why you need to link to the aspnet_Membership  table in the first place, and how you want to handle the situation where there is no membership record.

I would look at bracketing your conditions, so you only check the records from aspnet_Membership  when the value of tbl_latestupdates.LUUserId  is not -1  .



0
 
aflockhartCommented:
e.g.

WHERE ( (userID =@userprofileID) and (membership.userid=@strUnserID) and (dateadded > membership.createdate )  )
OR
(userid=-1)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now