How to join two table in a single View

Question

1.00/5 (1 vote)

See more:

I have two table one is EMP_DETAILS which is containing 7 columns (EMP_ID
[Primary Key], FIRST_NAME, LAST_NAME, AGE, ADDRESS, SALARY, DEPT_ID
[Foreign Key]) and another one is DEP_DETAILS which is containing 2 columns (DEPT_ID [Primary Key], DEPT_NAME).

I want to create a single View for those tables but without using JOIN Query. Means :

EMP_ID FIRST_NAME LAST_NAME AGE ADDRESS SALARY DEPT_ID DEPT_NAME

Posted 16-Dec-14 23:50pm

Bittu14

Updated 16-Dec-14 23:53pm

v3

Add a Solution

Comments

Tomas Takac 17-Dec-14 5:53am

This seems to be trivial. What did you try so far?

Shweta N Mishra 17-Dec-14 5:54am

Why dont you want to use JOIN?, Use where clause but this going backword in tech

Tomas Takac 17-Dec-14 5:57am

I just noticed the "without using JOIN Query" requirement. What's wrong with joins?

3 solutions

Solution 3

I want to create a single View for those tables but without using JOIN
Unfortunatelly, you can't!

I'd suggest to back to basics and read this: Visual Representation of SQL Joins[^]

Posted 17-Dec-14 0:15am

Maciej Los

Comments

Tomas Takac 17-Dec-14 6:27am

+5 for clearly saying the requirement is nonsense

Maciej Los 17-Dec-14 6:41am

Thank you, Tomas ;)

jaket-cp 17-Dec-14 6:33am

you have my 5 on this also, even though I put in a solution :)

Maciej Los 17-Dec-14 6:41am

Thank you ;)

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Accepted Answer · 2014-12-17T00:11:00

Solution 2

It can be done with a where clause.
Used to do it like this years ago, I believe before MS SQL Server 2000 come out.

SQL

select 
	FIRST_NAME, 
	LAST_NAME, 
	AGE, 
	ADDRESS, 
	SALARY,
	DEPT_NAME.DEPT_ID,
	DEPT_NAME
from EMP_DETAILS, DEPT_NAME
where EMP_DETAILS.DEPT_ID = DEPT_NAME.DEPT_ID

But I recommend to use join.

SQL

select 
	FIRST_NAME, 
	LAST_NAME, 
	AGE, 
	ADDRESS, 
	SALARY,
	DEPT_NAME.DEPT_ID,
	DEPT_NAME
from EMP_DETAILS
inner join DEPT_NAME
	on EMP_DETAILS.DEPT_ID = DEPT_NAME.DEPT_ID

Posted 17-Dec-14 0:11am

jaket-cp

Comments

Maciej Los 17-Dec-14 6:17am

Using where in first query is - in fact - join. It meant that query returns only matching data.
My vote of 4, becasue of Join recommendation.

jaket-cp 17-Dec-14 6:24am

Yes I get your point.
I wanted to show that even though it can be done, it is not the recommended way of doing it.
Hopefully they will use join syntax.
Also with more complicated queries, using the where clause to do the joins is not the way to go.
:)

Bittu14 17-Dec-14 6:20am

THANK YOU SO MUCH.

jaket-cp 17-Dec-14 6:26am

Glad to help, but take note off all the comments mentioned about using the JOIN - it is the best way to go.
As you can see from the two queries, the join query is very similar to the where clause method.

KM Perumal · Accepted Answer · 2014-12-17T00:06:00

Solution 1

Try this

SQL

select t1.EMP_ID,	t1.FIRST_NAME,t1.LAST_NAME, t1.AGE, t1.ADDRESS, t1.SALARY, t2.DEPT_ID, t2.DEPT_NAME from EMP_DETAILS t1, DEP_DETAILS t2

Posted 17-Dec-14 0:06am

KM Perumal

Comments

Tomas Takac 17-Dec-14 6:10am

I don't think that cross join is the solution. What is the result good for? Did you forget the where clause?

KM Perumal 17-Dec-14 6:20am

Yeah ...without Join Keyword

Tomas Takac 17-Dec-14 6:24am

I was tempted to vote you down but as OP accepted your answer I will let it be. But do you realize that there is no meaning in the results your query produces?

KM Perumal 17-Dec-14 6:41am

Your wishes ... this is return expected answer

Maciej Los 17-Dec-14 6:13am

Sorry, but your answer deserved for 1!
Above query - in fact - is cross join, which is undesirable.

KM Perumal 17-Dec-14 6:20am

In above scenario .they need single resultset without join keyword

KM Perumal 17-Dec-14 6:17am

He is asking Single result set .thats y i didnt use Where clause

Bittu14 17-Dec-14 6:20am

THANK YOU SO MUCH.

Maciej Los 17-Dec-14 6:23am

For what? For bad solution? Have you tested it?

Tomas Takac 17-Dec-14 6:25am

Did you try it? Does this really solve your problem? Does the query produce meaningful results?

Bittu14 17-Dec-14 7:00am

Yes I solved the problem. Really I am very thank full that he did this for me. Try
this one :

CREATE OR REPLACE VIEW EMP_DEP_VIEW AS
select t1.EMP_ID, t1.FIRST_NAME,t1.LAST_NAME, t1.AGE, t1.ADDRESS, t1.SALARY, t2.DEPT_ID, t2.DEPT_NAME from EMP_DETAILS t1, DEP_DETAILS t2
WHERE t2.DEPT_ID = t1.DEPT_ID;

It will show you the actual result without using any join query.

Tomas Takac 17-Dec-14 7:16am

So this solution is actually not correct. You should not accept it then.

Bittu14 17-Dec-14 8:02am

Yes it is. Just I wrote one where clause on it.

KM Perumal 17-Dec-14 8:03am

@Bittu14 Good ...!!

Bittu14 17-Dec-14 8:06am

Thanks... :) @KM Perumal

Bittu14 17-Dec-14 8:13am

One think KM Perumal, if I want to add any column inside a View then what I have to do. I was tried to ALTER that but it had shown some error. Can you please help me out of that.

How to join two table in a single View

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

How to join two table in a single View

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us