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 - 1592 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