Finding database on virtual servers

I have a sql 2000 cluster (multiple instance) and I'm going to move my
databases over and spread them out over each instance. Now when my web
servers need to read/write info to the databases how will they find which
virtual server has the database it needs. Do I have to build a connection
string that says, first look here and if not then look here. I be grateful
for any ideas or suggestions.C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce
>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>|||This is an active/active cluster. All instances of sql need to be running at
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plus
a couple on control databases. I don't want to have the control databases in
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:
> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> >--Original Message--
> >I have a sql 2000 cluster (multiple instance) and I'm
> going to move my
> >databases over and spread them out over each instance.
> Now when my web
> >servers need to read/write info to the databases how
> will they find which
> >virtual server has the database it needs. Do I have to
> build a connection
> >string that says, first look here and if not then look
> here. I be grateful
> >for any ideas or suggestions.
> >
> >.
> >
>|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce
>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient
>use of my hardware.
>"Bruce de Freitas" wrote:
>> C, your cluster, is it active/active and your have
>> multiple SQL SERVER instances that you run
concurrently
>> on BOTH boxes, active/active? or is only one node
active
>> and the other is sitting there just in case
>> (active/passive)? I'm not clear on what you mean by
>> multiple instances? SQL instances I guess... Either
>> way, you'd have a virtual name that your application
>> would refer to. When the node failover, you still
call
>> the virtual name and the cluster wil get you to the
>> active side of the cluster. Maybe I'm not getting
your
>> question completly?!? Bruce
>>
>>
>> >--Original Message--
>> >I have a sql 2000 cluster (multiple instance) and I'm
>> going to move my
>> >databases over and spread them out over each
instance.
>> Now when my web
>> >servers need to read/write info to the databases how
>> will they find which
>> >virtual server has the database it needs. Do I have
to
>> build a connection
>> >string that says, first look here and if not then
look
>> here. I be grateful
>> >for any ideas or suggestions.
>> >
>> >.
>> >
>.
>|||You don't understand my question. The fail-over I'm not worried about and we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:
> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> >--Original Message--
> >This is an active/active cluster. All instances of sql
> need to be running at
> >all times. I am hoping to spread the load (databases)
> evenly across the
> >servers. I have 150 databases on various sizes and
> transaction amounts, plus
> >a couple on control databases. I don't want to have the
> control databases in
> >one instance and the rest in another, because I will not
> be making efficient
> >use of my hardware.
> >
> >"Bruce de Freitas" wrote:
> >
> >> C, your cluster, is it active/active and your have
> >> multiple SQL SERVER instances that you run
> concurrently
> >> on BOTH boxes, active/active? or is only one node
> active
> >> and the other is sitting there just in case
> >> (active/passive)? I'm not clear on what you mean by
> >> multiple instances? SQL instances I guess... Either
> >> way, you'd have a virtual name that your application
> >> would refer to. When the node failover, you still
> call
> >> the virtual name and the cluster wil get you to the
> >> active side of the cluster. Maybe I'm not getting
> your
> >> question completly?!? Bruce
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I have a sql 2000 cluster (multiple instance) and I'm
> >> going to move my
> >> >databases over and spread them out over each
> instance.
> >> Now when my web
> >> >servers need to read/write info to the databases how
> >> will they find which
> >> >virtual server has the database it needs. Do I have
> to
> >> build a connection
> >> >string that says, first look here and if not then
> look
> >> here. I be grateful
> >> >for any ideas or suggestions.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
--
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:
> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> >--Original Message--
> >This is an active/active cluster. All instances of sql
> need to be running at
> >all times. I am hoping to spread the load (databases)
> evenly across the
> >servers. I have 150 databases on various sizes and
> transaction amounts, plus
> >a couple on control databases. I don't want to have the
> control databases in
> >one instance and the rest in another, because I will not
> be making efficient
> >use of my hardware.
> >
> >"Bruce de Freitas" wrote:
> >
> >> C, your cluster, is it active/active and your have
> >> multiple SQL SERVER instances that you run
> concurrently
> >> on BOTH boxes, active/active? or is only one node
> active
> >> and the other is sitting there just in case
> >> (active/passive)? I'm not clear on what you mean by
> >> multiple instances? SQL instances I guess... Either
> >> way, you'd have a virtual name that your application
> >> would refer to. When the node failover, you still
> call
> >> the virtual name and the cluster wil get you to the
> >> active side of the cluster. Maybe I'm not getting
> your
> >> question completly?!? Bruce
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I have a sql 2000 cluster (multiple instance) and I'm
> >> going to move my
> >> >databases over and spread them out over each
> instance.
> >> Now when my web
> >> >servers need to read/write info to the databases how
> >> will they find which
> >> >virtual server has the database it needs. Do I have
> to
> >> build a connection
> >> >string that says, first look here and if not then
> look
> >> here. I be grateful
> >> >for any ideas or suggestions.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>

Finding data

Sorry about posting this in multiple groups but no one in the clients group
seemed to know the answer.
Here's the question;
Is it possible to search an entire database for a string or number? If so
How?
- Hamilton
Hamilton,
You could develop your own stored procedure by iterating through the COLUNM_NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require a cursor. Any matches should also display the table name, owner and column name. A proc might exist on the we
b somewhere to do this, there is nothing native to SQL Server that will do this currently. Be aware that such a search could take a very, very long time to complete as you will be table scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per table, and not one table scan per column - that should keep the already bad performance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Thanks Mark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:D3376437-D5CE-441C-8A86-3948921C9CB6@.microsoft.com...
Hamilton,
You could develop your own stored procedure by iterating through the
COLUNM_NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require
a cursor. Any matches should also display the table name, owner and column
name. A proc might exist on the web somewhere to do this, there is nothing
native to SQL Server that will do this currently. Be aware that such a
search could take a very, very long time to complete as you will be table
scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per
table, and not one table scan per column - that should keep the already bad
performance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Finding data

Sorry about posting this in multiple groups but no one in the clients group
seemed to know the answer.
Here's the question;
Is it possible to search an entire database for a string or number? If so
How?
- HamiltonHamilton,
You could develop your own stored procedure by iterating through the COLUNM_
NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require a curs
or. Any matches should also display the table name, owner and column name. A
proc might exist on the we
b somewhere to do this, there is nothing native to SQL Server that will do t
his currently. Be aware that such a search could take a very, very long time
to complete as you will be table scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per t
able, and not one table scan per column - that should keep the already bad p
erformance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Thanks Mark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:D3376437-D5CE-441C-8A86-3948921C9CB6@.microsoft.com...
Hamilton,
You could develop your own stored procedure by iterating through the
COLUNM_NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require
a cursor. Any matches should also display the table name, owner and column
name. A proc might exist on the web somewhere to do this, there is nothing
native to SQL Server that will do this currently. Be aware that such a
search could take a very, very long time to complete as you will be table
scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per
table, and not one table scan per column - that should keep the already bad
performance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Finding BINN directory for Multiple Instances

I am writing an installer that needs to put a dll file with a bunch of
extended stored procedures in the BINN directory. The problem is that
if the machine is running multiple instances of SQL Server, there is
more than one BINN directory. The installer has a dynamically
populated dropdown with the name of the instances, so does anyone know
if this logic is correct and/or reliable assuming SQL Server 2000?

If the instance name = "MSSQLSERVER"
then path to BINN directory = registry value of
HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"

If the instance name != "MSSQLSERVER"
then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"

Thanks in advance for any help."Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0407231210.28d2403c@.posting.google.c om...
> I am writing an installer that needs to put a dll file with a bunch of
> extended stored procedures in the BINN directory. The problem is that
> if the machine is running multiple instances of SQL Server, there is
> more than one BINN directory. The installer has a dynamically
> populated dropdown with the name of the instances, so does anyone know
> if this logic is correct and/or reliable assuming SQL Server 2000?
> If the instance name = "MSSQLSERVER"
> then path to BINN directory = registry value of
> HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"
> If the instance name != "MSSQLSERVER"
> then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
> SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"
> Thanks in advance for any help.

See questions 12 and 13:

http://support.microsoft.com/defaul...6&Product=sql2k

Simon

Finding a user that has multiple log-ins at the same time

I have a table that contains the following

UserName - Which is unique to each user

TimeStart - Which is the time and date that the user logs in

TimeEnd - Which is the time and date that the user logs out

Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.

Try this query....

Select
MainQ.*
From
UserLog MainQ
Join
(
Select

Main.UserName,
Main.TimeStart,
Main.TimeEnd
From
UserLog Main
Join UserLog Started On Main.UserName = Started.UserName
And Main.TimeStart <= Started.TimeStart
And Main.TimeEnd >= Started.TimeStart

Group BY
Main.UserName,
Main.TimeStart,
Main.TimeEnd
Having Count(*) <> 1
) as Data
On MainQ.UserName = Data.UserName
And MainQ.TimeStart >= Data.TimeStart
And MainQ.TimeStart <= Data.TimeEnd

find unique identifier through multiple tables

>In one table there is GUID as a primary key and another GUID as
foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<

I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.

This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

You really need to throw this thing out. But if you cannot, then
update your resume.--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In one table there is GUID as a primary key and another GUID as

foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<
>
I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.
>
This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

He can add REFERENCES clauses (once he figures out which ones should
be added, and cleans up any existing exceptions), surely?|||>He can add REFERENCES clauses (once he figures out which ones should be added, and cleans up any existing exceptions), surely? <<

Then he will have mimicked a 1970's pointer chain DB in SQL instead of
making this a properly designed RDBMS.

For example, if I use an VIN for an automobile, I can verify the VIN
by going to the automobile, the DMV, insurance company, etc. But if I
use a GUID (or any other hardware generated value), I have no trusted
external source for verification.

I do not have a good way to validate it, in fact. The magical
universal GUID might be used for an automobile, a squid or Britney
Spears!

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999
SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:

>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:

>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

find out if an ID is tied to multiple ids in another table

Table 1

stock

stock_id

cat_id

stock_name

is_fund

Table 2

Fund_contents

fund_id

stock_id

I need to find out all the stock_id's from the stock table that are associated with 2 different fund_id's in the second table and the stock_id's in the first table have to have is_fund = 0 from the stock table

any ideas?

How about,

SELECT S.Stock_Id
FROM StockAs S
JOIN Fund_Contents F
ON S.Stock_Id = F.Stock_Id
WHERE S.IS_Fund = 0
GROUP BY S.Stock_Id
HAVINGCOUNT(F.Fund_Id) >= 2

Hope this helps,

|||

SELECT S.Stock_Id
FROM StockAs S
JOIN Fund_Contents F
ON S.Stock_Id = F.Stock_Id

WHERE S.IS_Fund = 0
GROUP BY S.Stock_Id
HAVINGCOUNT(F.Fund_Id) >= 2

|||

perfect thanks both of you