Determining Unique Children

Nov 17, 2005

Hi,

I need to be able to identify if a parent has the same child names as another parent.

Have a look at my data (in pic)

Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?

So you would get something like:

Bob | 2
Bill | 1
Glen | 2

thanks,
david.

View 7 Replies


ADVERTISEMENT

Reporting Services :: SSRS Recursive Parent Gives Distinct Children Only When Children Have Multiple Parents

Aug 18, 2015

I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).

View 6 Replies View Related

How To Get All The Children In The MLM Tree..

Sep 10, 2007

hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I  have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because  in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
Please Suggest me...

View 1 Replies View Related

Find All Children Of A Specified Group ID

Dec 30, 2004

Hi all, Im designing a product menu like the one seen here.
I need to be able to itterate through all product menu groups in a table and find all children of a specified parent. Sorry for the cryptic Explanation and crappy Diagram!

Please see my diagram

View 5 Replies View Related

How To Get Children Of Same Parent In SSRS

Jul 12, 2007

Hi

I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter

when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent



As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)

to be more specfic consider this



Region SubRegion

Asia India

Asia Pakistan

Asia Japan

Australia Melborne



Suppose my report parameter is India , I should get all the values under Asia(Parent of india)



Can any one help me ..



View 2 Replies View Related

Cascade Deletions Of Children When FKs Present

Dec 8, 1999

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

View 1 Replies View Related

Finding Cases With All Children Closed

Jan 5, 2013

Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:

CaseInfo Table
CaseID,
CaseNumber,
CaseName

CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate

Party Table
ID,
PartyID,
Firstname,
LastName

SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID

WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName

But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.

View 5 Replies View Related

Insert Single Parent And Multiple Children

Sep 20, 2006

I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options  available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table). My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table. Thanks for any help

View 2 Replies View Related

How Can I Append Children To Parents In A SSIS Data Flow Task?

Apr 11, 2007

I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.



In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk



The external agency can only use a delimited file looking like

p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b



where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.



How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.

View 4 Replies View Related

Analysis :: Include And Exclude Children In A Single Hierarchy In Parent Child Dimension In MDX

Apr 8, 2015

Include children and exclude children in a single  hierarchy in parent child dimension in mdx

*12-parent
 **20-parent
 - 9-parent
       --250-child1
       --210-child2
        --240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of  9 with children

View 3 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

View 1 Replies View Related

Determining If A Server Is 32 Bit Vs 64

Jun 11, 2007



Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out. I need to know this to pick the correct upgrade version.

-Kyle

View 1 Replies View Related

Determining If A Server Is 32 Bit Vs 64

Jun 11, 2007

Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out.

-Kyle

View 4 Replies View Related

Determining What To Input As Server

Jun 9, 2006

I'm trying to connect to a sql database, but I don't know what myserver is in the following code.Dim strConn As String = "server=myserver;database=Northwind"I can't get the code to link up with my Northwind database.I'm running everything locally if that helps.Thanks!Jon

View 2 Replies View Related

Determining Database Size

Oct 31, 2001

Is there a system stored procedure that I can execute that will return the actual size of the database you are working with? Any information is appreciated.

View 1 Replies View Related

Determining If A Table Exists

May 2, 2000

How do I find out if a temporary table named '##test' exists? I have a stored
procedure that creates this table and if it exists another stored procedure
should do one thing, if it does not exist I want the SP to do something else.
Any help as to how I can determine if this table exists at the current time
would be greatly appreciated.

Thanks in advance for you help,
Jon

View 6 Replies View Related

Determining DB Type/Character Set

Feb 26, 2002

Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set?
Any help will be greatly appreciated. Thanks a lot!!

View 1 Replies View Related

Determining Last DBCC Procedure

Mar 7, 1999

Hi,
Is it possible to determine when last a DBCC statement was run on a database & what that DBCC was.

TIA
Shaun Tinline
Database Administrator
Alcatel Altech Telecoms

View 1 Replies View Related

Determining Database Size

May 9, 2008

Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database.

My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full.

I'd appreciate any insights.

Kamen

View 3 Replies View Related

Determining A Range Of Values

Mar 29, 2012

suppose you have a large table with 2 columns

create table tick
(
ID bigint identity (1,1) primary key not null
, price money not null
)

and I want to know 3 things

Starting with ID = 1 through ID = (last)
give me the low and high price (that satisfies the below WHERE clause), and the last ID
WHERE high price - low price = 0.10
and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10

So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.

I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc...
Then calculate the min(ID) that goes up 0.01 then up 0.02, etc..finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10.

View 2 Replies View Related

Determining If SQL Statement Is A Query

Jul 27, 2007

Hi,

I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.

There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):

try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}

Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.

My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?

Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)

Thanks for your time, all ideas appreciated

Jim

View 1 Replies View Related

Determining What Columns Are All Null

Jul 6, 2006

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

View 5 Replies View Related

Determining Last Entry ID In Table

Nov 17, 2006

Not sure if this is the right place to post this but hopefully someone can help me. I would like to determine what the last automatically incremented ID is in a table I need to return it as a variable to a VB.NET program but I'm not sure what the SELECT statement would look like for this. Any help would be greatly appreciated.

View 4 Replies View Related

Determining Is A Database Is A Subscriber

Feb 1, 2006

Hi,



can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!!

I want to provide a small app that creates a merge publication but only if the database isn't a subscriber.

Thanks for your help

Graham

View 3 Replies View Related

Determining Which Version Of SQL Is Running

Apr 12, 2007

hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is.

thank you for your help

View 1 Replies View Related

Determining The OS Version That SQL Is Running On

Apr 30, 2007

I am writing a client application that offers an UI that allows an administrator to remotely add/delete/update user accounts accross many different SQL Servers running on XP and up.



When the operating system is W2K3 or higher I want to take advantage of the "check_expiration, check_policy, must_change' arguments to create login and exclude those features when the host OS does not support them.



Is there an easy way to determine if those arguments are supported?



Thanks

Mark

View 5 Replies View Related

Transact SQL :: Get All Children Of A Product With Product ID In Result Set

Aug 30, 2015

I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.

DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT)
INSERT INTO @Hierarchy VALUES (123, 234)
INSERT INTO @Hierarchy VALUES (234, 456)
INSERT INTO @Hierarchy VALUES (456, 678)
INSERT INTO @Hierarchy VALUES (678, 891)
INSERT INTO @Hierarchy VALUES (891, NULL)

[Code] .....

View 3 Replies View Related

Determining Null Values In Columns

Sep 7, 2004

How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:


int count = (int)row["Count"];

and

int count = row["Count"] as int;


the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?

View 6 Replies View Related

Determining What Tables Do Not Have Primary Keys

Aug 14, 2001

Does anyone have a script that will roll through the tables in a database and identify tables without primary keys defined? I did not see any in the online script database.

Thanks,
Rick

View 1 Replies View Related

Determining Last Time A Table Was Updated (6.5, 7.0, 2k)

May 28, 2002

I am trying to find a global way of when the last time a row in one of my tables was updated or data inserted. I say global because I don't want to drill down through each table looking for modified rows.

I am a DBA of several hundred databases and want to retire those no longer being used.

Is there a column of a system table that has this info.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved