[vasdueva]

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]©

[nkasei28]

Why don't you post this in the database forum?

[jjsaw5]

Moved to database forum.

[meherzad4u]

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]©

select sid from table as a,table as b where a.sid<>b.sid and a.sname=b.sname;

[baavgai]

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

[vasdueva]

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.