Do I want to use a join?

I have two tables, both containing memberID fields. One table is a membership table and the other is a list of members in a committee. So one or more memberIDs from the first table are in the second table, while every memberID in the second table is in the first table.

I need to get all the memberIDs in the first table that are NOT in the second table.

The point of this is to have two lists, one of members not on the committee and one of member that are on the committee. Add a couple of arrow buttons and the user can add or delete members for the committee.

Diane

You can get all Members not on the committee by

SELECT * FROM TableA WHERE MemberId NOT IN (SELECT MemberId FROM TableB)

|||

That was fast, thank you.

Diane

|||As soon as you validated the soultion, please mark the question as answered.|||I did, when I posted my thank you.

Do i need primary key?

Here is the scenario
I have a table (let's call it tblTest) with 3 fields, UserID, Note,
NoteEnterDate (simple one to many relationship. user can have many notes)
Query would be to join it with user table and get the first and last name of
the user (based on UserID) and show the Note and NoteEnterDate (at this
point, I cannot think of any other query that uses this tblTest other than
this).
As far as index is concerned, I think I just need to create clustered index
on UserID.
I know it is good to declare a primary key for every table. Is there a need
for primary key for tblTest perhaps by adding another field called NoteID?
Thanks"Justin" <jus820@.hotmail.com> wrote in message
news:e4OGN5TbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
> of the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered
> index on UserID.
> I know it is good to declare a primary key for every table. Is there a
> need for primary key for tblTest perhaps by adding another field called
> NoteID?
>
Would you allow the same (UserID,Note,NoteEnterDate) to be entered twice?
If so, add a synthetic NoteID to tell them apart. If not, create the PK on
(UserID, NoteEnterDate,Note).
David|||you don 't need to waste the space on another column, if the noteid is not
having significance. and if you are going to use an identity for it then its
of no use at all.
u can use user ID and NoteEnterDate as the primary key.
Hope this helps.
--
"Justin" wrote:

> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
of
> the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered inde
x
> on UserID.
> I know it is good to declare a primary key for every table. Is there a ne
ed
> for primary key for tblTest perhaps by adding another field called NoteID?
> Thanks
>
>|||NoteEnterDate is smalldatetime datatype (since we don't basically care about
the seconds). User can type more than one note in a minute. If I create
primary key on UserID and NoteEnterDate, this is not allowed.
Even even if this was not allowed (creating more than one notes in a
minute), what would be the point of creating primary key on UserID,
NoteEnterDate?
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0F05A821-5FFD-4A20-97E2-301565FFA187@.microsoft.com...
> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
> u can use user ID and NoteEnterDate as the primary key.
> Hope this helps.
> --
>
>
> "Justin" wrote:
>|||> Even even if this was not allowed (creating more than one notes in a
> minute), what would be the point of creating primary key on UserID,
> NoteEnterDate?
Maybe to prevent someone hitting refresh on your web page, 80 times in a
minute, and populating your table with redundant data.
A|||> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
I don't think I particularly agree. What harm does an IDENTITY column do?
I don't think it's as horrible as you make it out to be. The OP doesn't
know all future requirements now, so we can't really gauge its significance,
but let's say you wanted to track notes for some reason (e.g. show who has
viewed them and when). You might create another table called:
CREATE TABLE dbo.NoteTracking
(
? FOREIGN KEY REFERENCES dbo.tblTest(?),
ViewDate SMALLDATETIME
)
Surely you don't suggest it would be better to make up this primary key
UserID,NoteEnterDate and use that as your reference in the secondary table?
Like the OP, I'm not clear on what business sense a primary key on
UserID,NoteEnterDate would make, other than to fulfill the mantra "every
table must have a primary key." If it really is possible (and even
desirable) for a single user to enter two notes in one minute, then we're
back at square one.
A|||All tables must have at least one set of columns that can invariably
identify every row in the table. And there are obvious practical reasons to
explicitly declare one of those sets of columns as the primary key.
Anithsql

Divide by Zero Error

I have a function that compiles a number of different aspects of a Select query and then creates some high-bred fields based on the result. The problem I have is that some of these values contain a zero or Null and therefore I am getting a divide by zero error. The three sums I have are as follows;

Number 1:-

SELECT........, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, BLAH BLAH FROM

Number 2 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc, BLAH BLAH FROM

Number 3 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, BLAH BLAH FROM

I have been informed that I need to use something like a CASE statement. What I want it to do is that if a ZERO or NULL is detected in any element of the source of the sum, then I want it to ignore the sum and just place the value of zero in whatever the AS xxxxxxxx dictates.

Could someone point me in a direction here or provide me with a little sample of how to go about doing this in a SELECT statement.

Thanks in advance

you can use case like this

select case when fnWTRalldata.floortotal = 0 then 0 else fnWTRalldata.floortotocc / fnWTRalldata.floortotal end as floorspaceperc from table

This should help you avoid division by zero errors.

|||

Thanks for your suggestion Andreas.

In addition to my previous post, would I be better to evaluate these conditions using a CASE statment in a second,third and fourth function and then deliver the results to the afore mention function, or could I use a nested statement within the existing SELECT statements?

Regards

|||

you can also use the iif in reporting services if that helps but it is a pain.

=iif(denominator > 0, numerator/iif(denominator >0, denominator, 1), 0)

|||

This is what I did to solve it. Found the answer elsewhere on the net

SELECT case when floortotal <> 0 then fnWTRalldata.floortotocc / fnWTRalldata.floortotal
else 0 end AS floorspaceperc,
case when FinalRtLsincSC <> 0 then (fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1
else 0 end AS rentrolldiscperc,
case when floortotal <> 0 then fnWTRalldata.NetRent / fnWTRalldata.floortotal
else 0 end AS netrentpersqft, BLAH BLAH
FROM fnWTRalldata

Thanks for all your suggestions, the solution I found was down the road you were all heading in.

Thanks

Divide by 0 in query

Hi, I want to something very simple...

a query that divides two fields and does not crap out when it gets a divide by 0 error.

this is my query...

select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(cache.respondentcount/nodes.inviteecount * 100) as percentage
from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname

PLEASE HELP!!CREATE FUNCTION returnNullIf0 (@.Num Varchar(100))
RETURNS Varchar(100) AS
--if the passed value is 0, the function returns null
--needs to be used whereever the value is used as denominator
BEGIN
DECLARE @.NewNum as varchar(100)
if isnumeric(@.Num) = 1
Begin
IF round(@.Num, 5)= 0
BEGIN
Select @.NewNum = null
END
Else
Select @.NewNum = @.Num
END
ELSE
BEGIN
Select @.NewNum = @.Num
END
Return(@.NewNum)
END

go


select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(returnNullIf0(cache.respondentcount)/returnNullIf0(nodes.inviteecount) * 100) as percentage
from
cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname|||Originally posted by ngillis
Hi, I want to something very simple...

a query that divides two fields and does not crap out when it gets a divide by 0 error.

this is my query...

select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(cache.respondentcount/nodes.inviteecount * 100) as percentage
from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname

PLEASE HELP!!

Also, you could use case:

(cache.respondentcount/case when isnnodes.inviteecount=0
then 1 -- or whatever you want
else isnnodes.inviteecount
end * 100)|||It somewhat depends on what you want to return when the divisor is zero. Zero? Null? A message?

blindman

Distrubuted query for inserting

I established a linked server, managed to do select (quering the remote
table) in a SP frm the local server . The table has 2 fields, fld 1 is int
(identity), the second is nvarchar(20) as follows
When I try to insert a new line using the following syntax from the local
server, I get errors
INSERT INTO sql2k5.mytable.dbo.MarketingTable1 VALUES ('données depuis Srv1
').
The error I am getting is : "Insert Error: Column name or number of supplied
values does not match table definition."
When deleted the identity column, inserting from local server worked ok.
Any idea of why this is happening? is there restriction on data types when
using distributed queries?
ThanksSalamElias (eliassal@.online.nospam) writes:
> I established a linked server, managed to do select (quering the remote
> table) in a SP frm the local server . The table has 2 fields, fld 1 is int
> (identity), the second is nvarchar(20) as follows
> When I try to insert a new line using the following syntax from the
> local server, I get errors INSERT INTO
> sql2k5.mytable.dbo.MarketingTable1 VALUES ('donnes depuis Srv1'). The
> error I am getting is : "Insert Error: Column name or number of supplied
> values does not match table definition."
> When deleted the identity column, inserting from local server worked ok.
> Any idea of why this is happening? is there restriction on data types when
> using distributed queries?
I guess that what the local SQL Server see is a remote table with two
columns. Keep in mind that it does not know that this is an SQL Server
table - it could be Access, Oracle or Active Directory on the other
end of the line.
In any case, it is best practice to always list the columns of the table you
are inserting to. Today the table has two columns, tomorrow it has three,
and then your INSERT till blow up.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi and thanks. Why do you say that "the local server doesn't know". The
localserver knows very well that the remte server is a sql server. I used th
e
following to create the linked server
sp_addlinkedserver 'sql2k5', N'SQL Server'
GO
Best regards
"Erland Sommarskog" wrote:

> SalamElias (eliassal@.online.nospam) writes:
> I guess that what the local SQL Server see is a remote table with two
> columns. Keep in mind that it does not know that this is an SQL Server
> table - it could be Access, Oracle or Active Directory on the other
> end of the line.
> In any case, it is best practice to always list the columns of the table y
ou
> are inserting to. Today the table has two columns, tomorrow it has three,
> and then your INSERT till blow up.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||SalamElias (eliassal@.online.nospam) writes:
> Hi and thanks. Why do you say that "the local server doesn't know". The
> localserver knows very well that the remte server is a sql server. I
> used the following to create the linked server
> sp_addlinkedserver 'sql2k5', N'SQL Server'
When SQL Server talks with a linked server, it acts a client add accesses
the remote server through the OLE DB API, which exposes generic elements.
Of course, it could be conceivable, that there are separate code paths
in case the linked server is SQL Server, but I would expect this particular
piece of code to be agnostic what is at the other end as much as possible,
to keep down the complexity.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I understand that you encountered the error:
"Insert Error: Column name or number of supplied values does not match
table definition."
when you executed the insert statement on a remote table with IDENTITY key.
If I have misunderstood, please let me know.
This is an expected behavior since the scope of the @.@.IDENTITY function is
current session on the local server on which it is executed. This function
cannot be applied to remote or linked servers. You may refer to:
@.@.IDENTITY (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms187342.aspx
I recommend that you create a stored procedure in the database of the
remote server, then perform INSERT operation via that SP. For example: EXEC
[mysqlserver-01].Test1.dbo.proc_insDict 'édepu','test'.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

distribution agent bcp error

I am trying to use immediate updating transactional replication. A few of the tables have text fields. I want to filter out these fields, but when the subscriber reinitializes I get a bcp error - unexpected EOF. When I switch the publisher to use chara
cter format (rather than native) I get an unknown character error. The columns are marked default null. If I do not filter the columns, the initialization works fine. Is there anything I am missing?
Any help is appreciated.
Sql Server 2000 Enterprise sp3
Debra,
I haven't seen this before and don't know of a direct solution. Some posts
have mentioned that the column order is relevant. There is a known bug
(http://support.microsoft.com/?id=271344) which reports the same error - in
this case you'd need to manually apply the snapshot. Please can you post up
the schema of the problematic table and an explanation of the filter you're
using.
TIA,
Paul Ibison
|||I am begining to believe the problem is specific to that server. I copied the databases to to my local machine and the replication with filters worked fine. But, I have to say I have not installed the application locally. I really do not believe the pr
oblem is the application, though.
On the servers, I am replicating the databases behind Great Plains - 2 separate boxes with named instances. I have handled all the identity fields with not for replication and ranges. In the tables that are giving me headaches there are between 10 and 3
0 columns. There are some indexes and PK, but not on the columns I am trying to filter. I tried moving the columns (I had also read that column position might be to blame), with no luck. For a work around, I changed the columns to varchar and left th
em in the replication. When I move to production, I will attempt to leave the fields as text and use the filter. I'll be sure to post if it works or not. I did not set up the servers I am using, but I can not think of why they would not handle the filt
ers!
Thanks for confirming that this was not a known error and possibly is restricted to my enviornment.
"Paul Ibison" wrote:

> Debra,
> I haven't seen this before and don't know of a direct solution. Some posts
> have mentioned that the column order is relevant. There is a known bug
> (http://support.microsoft.com/?id=271344) which reports the same error - in
> this case you'd need to manually apply the snapshot. Please can you post up
> the schema of the problematic table and an explanation of the filter you're
> using.
> TIA,
> Paul Ibison
>
>
|||I moved the databases to a different server and recreated the subsciption and publication. When replicating just that one table, the bcp works with no error. When I add all the other tables (175), the bcp fails if there are any filters on the tables th
at have many columns. I did not test filtering the tables that have fewer columns.
I worked around the issue by doing as you said and manually applying the snapshot. Luckily this is a one time deal and there will only be one subscriber.
Thanks for your help!
"Paul Ibison" wrote:

> Debra,
> I haven't seen this before and don't know of a direct solution. Some posts
> have mentioned that the column order is relevant. There is a known bug
> (http://support.microsoft.com/?id=271344) which reports the same error - in
> this case you'd need to manually apply the snapshot. Please can you post up
> the schema of the problematic table and an explanation of the filter you're
> using.
> TIA,
> Paul Ibison
>
>