Recusive Query To Find Friend Relationships?
Apr 18, 2008
Greetings,
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.
Any ideas?
View 5 Replies
ADVERTISEMENT
Jan 12, 2005
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.
Any help will be greatly appreciated.
View 2 Replies
View Related
Mar 20, 2007
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?
View 2 Replies
View Related
Sep 20, 2007
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?
View 1 Replies
View Related
Dec 22, 2005
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!
View 1 Replies
View Related
May 18, 2008
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,
View 7 Replies
View Related
Nov 12, 2007
What is the iso sql query to get the relationships between all tables in a db?
View 4 Replies
View Related
Jan 26, 2008
Hello
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?
Best Regards
/Erik
View 1 Replies
View Related
Jan 26, 2008
Hello
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?
Best Regards
/Erik
View 1 Replies
View Related
Jun 7, 2005
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
View 4 Replies
View Related
Aug 22, 2007
Hi,
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.
Thanks,
Tiago
View 2 Replies
View Related
Jul 23, 2005
I have a 1:1 relationship between tables and am finding that the parentis sometimes mising the child.How do I query for what is not there?TIA
View 4 Replies
View Related
Sep 19, 2006
Hello All,
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?
Thanks in advance!
View 1 Replies
View Related
Jul 19, 2004
Hi
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.
Hope you can help.
Thanks
View 2 Replies
View Related
Jul 8, 2002
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.
Regards,
Vnk.
View 3 Replies
View Related
Jul 8, 2002
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.
Regards,
Vnk.
View 3 Replies
View Related
Mar 7, 2006
Hi
I need help in finding the query which will provide the following resultset from the below table..
Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)
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.
thanks
View 14 Replies
View Related
Nov 13, 2013
If I run sample query below against an adventureworks database
where can I find query cost?
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;
Does estimated subtree cost for in the actual execution plan (when right click SELECT operator in the execution plan) is considered query cost?
View 3 Replies
View Related
Sep 4, 2014
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 ?
View 1 Replies
View Related
Jun 20, 2007
can anyone explain how this works:
USE NORTHWIND
GO
SELECT freight
FROM orders E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))
replace N by a number. To find that Nth value from the table.
Ashley Rhodes
View 2 Replies
View Related
May 5, 2008
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?
View 7 Replies
View Related
Sep 4, 2007
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;
Thnx
Matt
View 4 Replies
View Related
Aug 16, 2007
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
Many Thanks
View 7 Replies
View Related
Jun 1, 2001
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.
View 1 Replies
View Related
Jan 18, 2006
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.
Could someone PLEASE help?!!
Thanks,
Karen
View 3 Replies
View Related
Apr 5, 2007
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 .
Thank you
kishore
View 14 Replies
View Related
May 16, 2004
Hello, everyone:
This is testing question from a tesing seb site. Any reply will be appreciated.
ZYT
View 3 Replies
View Related
May 30, 2007
Dear Experts, i need one query to find all the indexed columns with table names ,column names and type of indexes....
i'm trying with sysindexes, but i was unable to finish it....i'm not clear about keys column in sysindexes...
please guide me.
thank you very much
View 12 Replies
View Related
Jul 19, 2007
Morning/Afternoon
Hope you can help me, am current working on ISIN numbers it doesn’t matter if you don't know what they are.
Now I have a table that is like this
table name = tDocISIN
col1 = LID(int)
col2 = sISIN(varchar(50))
col3 = lDocumentId(int)
Which has sample data like this
84237USX7318VAA4543997395
92579USO45167AR4544021575
84244USY63651114644025820
84243USY63651AA4544025820
113633USP51225AA1144028865
92846USG36912AG1344029240
92847US359860AG8444029240
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.
Am a total beginner. Thanks in advance.
Lee
View 6 Replies
View Related
Jul 13, 2006
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
View 4 Replies
View Related
Aug 10, 2015
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:
1)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate=
Table2.EndDate
2)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate!=
Table2.EndDate
3)
Table1.Firstname!=Table2.Firstname,Table1.Lastname!=Table2.Lastname,Table1.StartDate!=Table2.StartDate,
Table1.EndDate=Table2.EndDate
Table 1 & Table 2 contains the following data:
Table1
First Name
Last Name
Start Date
End Date
City
[code]...
I want this output by writing a single query.
View 4 Replies
View Related
Jul 5, 2007
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.
Many thanks
Mike Thomas
View 7 Replies
View Related
Aug 29, 2007
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
View 1 Replies
View Related
