The following table is used for the problem.
Table
sId  sName

Where sId is an integer and the primary key.
The question asks:
return all sId pairs where sName is the same for both sId's.
(n,n) should not be in the table
if (n1,n2) is in the table, then (n2,n1) should not be
New Table
sId1  sId2

I came up with the following that returns all the pairs, but it also allows permutations(it will have (n1,n2) and (n2,n1) in the table)
Any help on removing the permutations or coming up with a different approach would be greatly appreciated.
I'll denote operations with the normally subscripted parameters in [param]
A < Rename[sId1,sName](Table)
B < Rename[sId2,sName](Table)
C < A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](B)
Project[sId1,sId2]©
5 Replies  1319 Views  Last Post: 02 August 2010  03:17 PM
Replies To: Database question
#2
Re: Database question
Posted 03 May 2010  04:28 AM
Why don't you post this in the database forum?
#4
Re: Database question
Posted 11 May 2010  02:10 AM
vasdueva, on 18 April 2010  12:45 PM, said:
The following table is used for the problem.
Table
sId  sName

Where sId is an integer and the primary key.
The question asks:
return all sId pairs where sName is the same for both sId's.
(n,n) should not be in the table
if (n1,n2) is in the table, then (n2,n1) should not be
New Table
sId1  sId2

I came up with the following that returns all the pairs, but it also allows permutations(it will have (n1,n2) and (n2,n1) in the table)
Any help on removing the permutations or coming up with a different approach would be greatly appreciated.
I'll denote operations with the normally subscripted parameters in [param]
A < Rename[sId1,sName](Table)
B < Rename[sId2,sName](Table)
C < A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](
Project[sId1,sId2]©
Table
sId  sName

Where sId is an integer and the primary key.
The question asks:
return all sId pairs where sName is the same for both sId's.
(n,n) should not be in the table
if (n1,n2) is in the table, then (n2,n1) should not be
New Table
sId1  sId2

I came up with the following that returns all the pairs, but it also allows permutations(it will have (n1,n2) and (n2,n1) in the table)
Any help on removing the permutations or coming up with a different approach would be greatly appreciated.
I'll denote operations with the normally subscripted parameters in [param]
A < Rename[sId1,sName](Table)
B < Rename[sId2,sName](Table)
C < A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](
Project[sId1,sId2]©
select sid from table as a,table as b where a.sid<>b.sid and a.sname=b.sname;
#5
Re: Database question
Posted 11 May 2010  04:05 AM
vasdueva, on 18 April 2010  02:45 PM, said:
C < A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](
That's some wonky syntax, there. I don't believe I've ever seen a caret (^) for a logical and.
The standard SQL syntax is what you want, unless your instructor has come up with some home grown language for it.
This should meet the requirements:
select distinct a.sId as sId1, b.sId as sId2 from Table a inner join Table b on a.sName=b.sName and a.sId!=b.sId
#6
Re: Database question
Posted 02 August 2010  03:17 PM
baavgai, on 11 May 2010  03:05 AM, said:
vasdueva, on 18 April 2010  02:45 PM, said:
C < A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](
That's some wonky syntax, there. I don't believe I've ever seen a caret (^) for a logical and.
The standard SQL syntax is what you want, unless your instructor has come up with some home grown language for it.
This should meet the requirements:
select distinct a.sId as sId1, b.sId as sId2 from Table a inner join Table b on a.sName=b.sName and a.sId!=b.sId
I feel the urge to defend my syntax even though this thread is months old. I wasn't try to write sql. I was trying to write relational algebra. The syntax for that isn't easy to put in text without Latex or some other math markup. The carat and upside down carat(not sure what symbol name) are used pretty commonly for the logical and and or in an algebraic syntax.
Page 1 of 1
