Selecting two derived columns from single select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting two derived columns from single select

Author

Topic

jack_mike_2008
Starting Member

5 Posts

Posted - 2007-10-26 : 02:06:37

I am working on query where i will be having around 20 tables. I don't want to join all the tables. I want to use derived columns idea because i will be extracting 2 - 4 columns only from all tables.

Ex:
Select a,b,
(select c from <atable> where...),
(select d from <btable where ...) -- and so on for 20 tables.
from <ctable>

In ctable there are around 30 millions records, so i need to take care of performance also..

I don't want to confuse all by posting 200 lines query..
I will explain my requirement using two tables.

Table Structure:

CREATE TABLE Emp_dept
(eno int PRIMARY key,
ename varchar(50),
deptno int)

CREATE TABLE dept
(deptno int PRIMARY key,
dname varchar(50),
dloc varchar(100))

Temporary Data:

INSERT INTO dept values(1,'Acct','hyd')
INSERT INTO dept values(2,'Mktg','mumbai')
INSERT INTO dept values(3,'Prod','Calcutta')
INSERT INTO dept values(4,'Advt','Jangoan')

INSERT INTO emp_dept VALUES (1,'Jack',1)
INSERT INTO emp_dept VALUES (2,'Sim',2)
INSERT INTO emp_dept VALUES (3,'Mike',1)
INSERT INTO emp_dept VALUES (4,'Tom',3)
INSERT INTO emp_dept VALUES (5,'Arnold',null)

Query:

SELECT eno ,ename,deptno,
(Select dname FROM dept WHERE deptno = ed.deptno) AS dname,
(Select Dloc FROM dept WHERE deptno = ed.deptno) AS DLoc
FROM emp_dept ed

Output:

1 Jack 1 Acct hyd
2 Sim 2 Mktg mumbai
3 Mike 1 Acct hyd
4 Tom 3 Prod Calcutta
5 Arnold NULL NULL NULL

Requirement:

Can i get the same result without joining the two tables and without hitting the same table two times for two columns..
Like in above example i am hitting dept table two times..
1 for dname 2. Dloc

I know joining the two table will do it..but i want to know is there any other way than joining because many of the fields may be null also.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-26 : 03:00:03

use LEFT JOIN

SELECT	ed.eno, ed.ename, ed.deptno, dp.dname, dp.Dloc
FROM emp_dept ed LEFT JOIN dept dp
ON   ed.deptno = dp.deptno

KH
[spoiler]Time is always against us[/spoiler]

jack_mike_2008
Starting Member

5 Posts

Posted - 2007-10-26 : 03:09:10

There will be twenty or more tables. I don't want to use join as i stated already.
Can u tell me alternate solution for this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-26 : 03:12:46

20 tables or 20 column ? It your query shows that it is 20 columns from same table. Using JOINs is more efficient than sub-query. If you don't want to use JOIN, sorry i can't help you further.

KH
[spoiler]Time is always against us[/spoiler]

jack_mike_2008
Starting Member

5 Posts

Posted - 2007-10-26 : 04:15:36

2 to 4 columns from each table.
Total table count will be 20+.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-10-26 : 04:36:52

Try this..

The only assumption is that two columns you are referring doesn't have any comma in the value. In case comma is there, look for unique character or pattern..

Any how this is just to give idea..

SELECT a.eno,a.ename, a.deptno,
substring(a.dname,1,CHARINDEX(',',A.dname)-1) as dloc,
substring(a.dname,CHARINDEX(',',A.dname)+1,len(a.dname))
FROM (
SELECT ed.eno,ed.ename, deptno,
(select dname+','+dloc as dloc
from dept where deptno = ed.deptno) as dname
FROM emp_dept ed) A;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 04:48:55

quote:
Originally posted by jack_mike_2008

Can i get the same result without joining the two tables and without hitting the same table two times for two columns.

The simple and short and only answer is NO.
Why do you need this?
Is this for some "dynamic creation fo query" engine?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources