Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I understand that the Cartesian product(X) operation on two databases does not need to be UNION compatible.So,if there is a same attribute called name in the two relations R and S where name in R is the first name and name in S is the second name

How can the related values be identified by the following selection operation

Q=RxS

I want to get the set of tuples whose firstname=lastname,So how am i supposed to write the selection statement?

σ Name=Name(Q)

Will this be a problem using the same attribute name in the selection operation?

share|improve this question
1  
This seems like an inner join, not a cross join. –  ypercube Feb 8 at 14:47
    
Its a Cross Join,Crossing two different relations –  techno Feb 8 at 14:48
1  
But you want a restriction of the cross join, right? –  ypercube Feb 8 at 14:54
1  
@ypercube I don't get what you are saying,im just referring to the problem of writing the relational expression(selection) when same attribute name is used. –  techno Feb 8 at 15:47

1 Answer 1

By way of example, using SQL Server, the simplest answer is indeed an INNER JOIN as @ypercube suggested in his comment.

USE tempdb;
GO
CREATE TABLE Names1
(
    FirstName varchar(50)
);
CREATE TABLE Names2
(
    LastName varchar(50)
);
INSERT INTO Names1 (FirstName) VALUES ('Max');
INSERT INTO Names1 (FirstName) VALUES ('Vernon');
INSERT INTO Names2 (LastName) VALUES ('Max');
INSERT INTO Names2 (LastName) VALUES ('Vernon');

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.FirstName = N2.LastName;

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;

The results:

enter image description here

However, if you are prepared to complicate your query unnecessarily, you could do the following:

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.FirstName = n2.LastName;

Results:

enter image description here

In your question, you ask if using the same field names is going to be an issue; plainly it is not:

USE tempdb;
GO

CREATE TABLE Names1
(
    [Name] varchar(50)
);
CREATE TABLE Names2
(
    [Name] varchar(50)
);
INSERT INTO Names1 ([Name]) VALUES ('Max');
INSERT INTO Names1 ([Name]) VALUES ('Vernon');
INSERT INTO Names2 ([Name]) VALUES ('Max');
INSERT INTO Names2 ([Name]) VALUES ('Vernon');

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.[Name] = N2.[Name];

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;


SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.[Name] = n2.[Name];

Results:

enter image description here

share|improve this answer
1  
I did not expect such a detailed answer :) i was expecting something in terms of relational algebra.Let me read this stuff you have posted. –  techno Feb 8 at 15:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.