Click here to Skip to main content
14,427,455 members
Rate this:
Please Sign up or sign in to 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
Updated 17-Dec-14 0:53am
v3
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?
Rate this:
Please Sign up or sign in to vote.

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[^]
   
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 ;)
Rate this:
Please Sign up or sign in to vote.

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.
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.
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
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Try this

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100