I'm trying to come up with a way (efficiency isn't the most important thing here) ..to show how a person is related to another person on my website. The table structure is like so (simplified)
ID | UserName| FriendName
1 | Danny | Mike
2 | Mike | Danny
3 | Steve | Jason
4 | Jason | Steve
5 | Steve Danny
6 | Danny | Steve
There are 2 rows each for every 'relationship' - I chose this design to simplify queries like "give me all of danny's friends" etc.
With this stucture, I want to be able to display to a user how they are "related" to someone that they may not necessarily be friends with. For example, lets say you are logged in as "Jason" and you are looking at "Dannys" homepage.
You are not Danny's friend..but you are related to him through Steve (because you are a friend of steve, and steve is a friend of jason)
I'm stumped trying to come up with a decent way to get this type of information..either in a sproc...or doing it in the business layer (c#). I think recursion is the way to go, and I could limit it to 4,5,6 levels whatever... again, efficiency isn't an issue here...I just want to somehow pull the data cleanly.
I have a table with several foreign key relationships. I am scripting with PHP and was wondering if anyone knows how to query the database to show the relationships that a table has. I have been using MySQL, but for a number of projects have to use MS SQL.
I'm using SQL Express with the Management Studio. I create a database diagram, and add a relationship. The relationship is shown in the diagram, but if I right-click it, I only get the option to delete it, I can't see it's properties. When I go thru the nodes for all the objects in the Object Explorer where I think I should be able to find this relationship, I can't find it??? I look in Triggers/Constraints both for the database as a whole and for the individual participating tables but I can't see the relationship anywhere. What am I doing wrong? Where should I be looking?
I'm using Management Studio Express, is there a way to find out what relationships of a database have cascade delete set to true? Also is there a way to change the settings (properties) of a relattionship without having to delete it and add it back?
I just spent the better part of a full day trying to figure out why my MSSQLSERVER service wouldn't start. Finally, after someone suggested I check out "C:Program FilesMicrosoft SQL Server[Instance Name]LogErrorLog" I found my problem. I opened it up to find: (newbie mistake, I know now!!)
2005-12-21 19:27:46.60 server SQL Server evaluation period has expired
So I thought I would pass this onto everyone I can think of so you will not make the mistake I just did.
It would have been nice if Microsoft, like just about every other software company out there, would have been loud & obnoxious about their trial software expiring. Instead they have to put a poor newbie soul thru what I just went thru.
*sigh*
Well if I helped 1 person with this, I've done some good!
Hi im finalizing a site for a custmer and now adding a Tell a friend function. I have done those before but not when storing the mailmessage in the SQL server and my question is How do I build this up in a good way...I have done the mailsender and its sends the email but how do I bind it together... I have two columns comming in a single row dataset from the Database: MailSubjectMailBody I dont want to bind it to a gridview or another control and then fetch that to my mail message Public Function TellaFriend(ByVal strEmail As String, ByVal strMyName As String, ByVal cultureid As Integer) As String 'Tell a friend Dim Data As DAL Data = New DAL Dim dsEmail As DataSet = Data.GetEmailMessage(1, cultureid) MyMailMessage.From = New MailAddress(info@company.com, "Customer Service") MyMailMessage.To.Add(New MailAddress(strEmail, "Hint from a friend" & strMyName)) MyMailMessage.Subject = dsEmail("MailSubject") MyMailMessage.IsBodyHtml = False MyMailMessage.Body = dsEmail("MailBody") 'Create the SMTPClient object and DO NOT specify the SMTP server name Dim SMTPServer As New SmtpClient() Dim MailSent As String Try SMTPServer.Send(MyMailMessage) MailSent = "True" Catch ex As SmtpException MailSent = "False" End Try Return MailSentEnd Function Of course this doesnt work but what do I need to do to bind Data.GetEmailMessage(1, cultureid) without using a gridview or any other datapresentation control directly in this function? If you need it this is from my DALFunction GetEmailMessage(ByVal intMessageId As Integer, ByVal intCultureId As Integer) As DataSet Dim DS As DataSetDim objConn As SqlConnectionDim objCmd As SqlDataAdapter objConn = New SqlConnection(_connStr)objCmd = New SqlDataAdapter("sp_GetEmailMessage", objConn)objCmd.SelectCommand.CommandType = CommandType.StoredProcedureobjCmd.SelectCommand.Parameters.Add(New SqlParameter("@messageid", SqlDbType.Int))objCmd.SelectCommand.Parameters("@messageid").Value = intMessageIdobjCmd.SelectCommand.Parameters.Add(New SqlParameter("@cultureid", SqlDbType.Int))objCmd.SelectCommand.Parameters("@cultureid").Value = intCultureIdDS = New DataSet()objCmd.Fill(DS, "dsMailMessage")Return DSEnd FunctionThanks,
I have created some tables and relationships between them by using MS SQL Server Management Studio Express. The relationships are between a field named OId, which is not a part of the primary key but it is typed as an unique key. And a field named ParentTableOId.
When I open the two tables that have this relationship in the "Design Query in Editor" is not the relationship there. Anybody who knows why?
I have created some tables and relationships between them by using MS SQL Server Management Studio Express. The relationships are between a field named OId, which is not a part of the primary key but it is typed as an unique key. And a field named ParentTableOId.
When I open the two tables that have this relationship in the "Design Query in Editor" is not the relationship there. Anybody who knows why?
I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY, studentId varchar(50) NOT NULL, course varchar(50) ) CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY, course varchar(50) NOT NULL, sco varchar(50) )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath
I'm looking for a query I can use to alter table relationships. What I want to do in particular, is to set every relationship to cascade on update. Can anyone point me out to a solution? MSDN seems very vague in this subject.
We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?
I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name FROM tblTable GROUP BY Name ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Data: insert into product_stocks values(1,'A',30) insert into product_stocks values(2,'A',70) insert into product_stocks values(3,'A',60) insert into product_stocks values(4,'A',40) insert into product_stocks values(1,'B',90) insert into product_stocks values(2,'B',60) insert into product_stocks values(3,'B',70) insert into product_stocks values(4,'B',40) insert into product_stocks values(1,'C',40) insert into product_stocks values(2,'C',50) insert into product_stocks values(3,'C',80) insert into product_stocks values(4,'C',90)
Result Set: product_type product_id no_of_units --------------- ------------- -------------- A 2 70 A 3 60 A 4 40 B 1 90 B 3 70 B 2 60 C 4 90 C 3 80 C 2 50
i.e The result set gives the top 3 products in each product_type based on the no_of_units.
I have a warehouse table but I don't know which query will update warehouse inside of information ? Thus, how to write a query list all query have include this warehouse table name in there ?
Say the table has a primary key, and the latest value on it is 100, then all the records on that table are deleted... If I INSERT new record, the key will be 101... Can I find out what the key is going to be before inserting a new record to the table?
I have a query that can tell me the columns in a table. In this case "Contact". What I would like to add to my query is if the column is part of the primary key. Can that be done? how so?
Code Snippet SELECT c.name AS column_name, c.column_id, SCHEMA_NAME(t.schema_id) AS type_schema, t.name AS type_name, t.is_user_defined, t.is_assembly_type, c.max_length, c.precision, c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;
I've been struggling with this one for quite some time now and would appreciate some help:-
I'm trying to get a list of schools that only has male students. the closest I have so far:-
SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students FROM Schools INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID) WHERE (FORUM_MEMBERS.UserType=4) GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;
The result is a list of schools:- SchoolName, M_SEX, no_of_students school 1, Male, 11 school 1, Female, 20 school 2, Male, 8 school 2, Female, 1 school 3, Male, 12 school 4, Male, 14 school 5, Male, 8 school 5, Female, 1
But what I really need is schools with only male students:- SchoolName, M_SEX, no_of_students school 3, Male, 12 school 4, Male, 14
Hey - I know that I can find space information about SQL Server. Allocated space, free space, used space, data space, etc... BUT is there a way that I can query how much total/available space is on the actual drive? For example, let's say that I have SQL installed on the D drive of a Server; i also have another application on that drive. I know that I can query how much room SQL Server is using, but can i query how much total/available space is on the drive? Any help appreciated.
I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .
The table structure is defined and i cannot edit it .
Please let me know if we could have a single query to do this .
Now I want to run a select query that will only bring the ones that have ‘CA’ or ‘US’ as the first two characters.
I can do that by running this:
select * from tDocISIN where sISIN like 'CA%'or sISIN like 'US%'
But my other where clause I want to add is only bring back the ones that have CA or US as the first two characters and any other character after the first two or in the rest on the string.
So US7636516745 is an ISIN I don’t want to bring back and US763TY16745 is an ISIN I do want to bring back.
The character that does exist after the first two characters will be anywhere within the rest of the string.
Hope that is clearly explain any help would be great.
Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
I have two tables in my database. Some matching rules are associated with these tables and I want a query which will retrieve those data based on the matching rules provided below:
I am using the SSIS Import & Export Wizard to make packages importing data from Access 2003 into SQL Server2005.
In the Access database I am using, there are about 100 tables and 4 select queries.
When I ran the SSIS wizard, it found the first query fine, and the package was made successfully.
Now, I need to make a package to import data using one of the other select queries (it has two subqueries.)
When I run the wizard, the "Select Source Tables and Views" dialog opens and shows all 100 tables, plus only two of the queries, not all 4. Two are missing, including the one I need to do the import.
I cannot see why these two queries would be excluded. One is a compound query (the one I need) the other is not. Is there a limit to the number of objects the dialog can show?
If anybody has had this problem please let me know. For now it's a real show stopper.
I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks -Kyle
