Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-02 : 09:09:06
|
| Jeremy writes "Ok, I am seting up a site where there is database of members of our organization, but also want to allow people to sign up and use the site without being members, but members must still sign up online to use the site. I have set up a database and members in the area are in the table "MalingList" and people who sign up on the site go in the table "Users", in the table "Users" there is column called "MailingListID". If the column is blank for the user, then they are not someone in our current member directory, only and online users, but if the id points to someone, then they are a member, and they signed up online. OK, you get the background now. Both tables have a field for email address, so that we can send members emails with out them neccarrily having to be online members. Ok, if I want to get a list of all the emails without duplicates how would I set up my query? Remember if the MailingListID column is blank, then there is no duplicate, but if it is not blank, then there could be a duplicate." |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-02 : 09:12:06
|
| you can go for a left outer join between the two tables.-------------------------------------------------------------- |
 |
|
|
gggg-us
Starting Member
3 Posts |
Posted - 2002-04-02 : 19:31:50
|
| Ok, so the SQL statement I want to use is:SELECT DISTINCT MailingList.EmailAddress, Users.EmailAddress FROM [MailingList] LEFT OUTER JOIN [Users] ON MailingList.MailingListID = Users.MailingListID WHERE MailingList.EmailAddress IS NOT NULL OR Users.EmailAddress IS NOT NULL;Is that right?Edited by - gggg-us on 04/02/2002 21:28:00 |
|
|
|
gggg-us
Starting Member
3 Posts |
Posted - 2002-04-02 : 22:04:33
|
| Ok, looks like that worked, but still some problems.I'm using JScript and ASP, and when I run the statement and put it to lets say "Emails" array, I can't pull all of it out.when I:Response.Write(Emails("MailingList.EmailAddress"));it gives me errors, but when I:Response.Write(Emails("EmailAddress")); it only gives me the information from the Users tables, and I get nothing from the MailingList table, but I can see that the array length is the correct length, meaning that it holds information from the MailingList table in there. How do I get it out? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-02 : 22:20:37
|
HiYeah, that will confuse ADO. You have two options, you can reference it by number, i.e. response.write emails(0)& emails(1) (or does it start at 1, I can't remember)or, probably better, use an alias in your sql.SELECT DISTINCT MailingList.EmailAddress as Email1, Users.EmailAddress as Email2FROM [MailingList] LEFT OUTER JOIN [Users] ON MailingList.MailingListID = Users.MailingListID WHERE MailingList.EmailAddress IS NOT NULL OR Users.EmailAddress IS NOT NULL That will force your columns to be called Email1 and Email2 which you can reference with ADO emails("email1") etcHope that helpsDamianEdited by - merkin on 04/02/2002 22:49:08 |
|
|
|
gggg-us
Starting Member
3 Posts |
Posted - 2002-04-02 : 22:34:57
|
| Thanks...big help! |
|
|
|
|
|
|
|
|
merging two possibly related tables.