Needs Another help

hello,
i wanted to know to write bello with out subqueries to increase my
application performance
CREATE TABLE COURSE (
COURSESLNO INT,
COURSENAME VARCHAR)
INSERT INTO COURSE VALUES (1,'JR');
INSERT INTO COURSE VALUES (2,'SR');
INSERT INTO COURSE VALUES (3,'LT');
INSERT INTO COURSE VALUES (4,'ST');
CREATE TABLE BRANCH_COURSE(
BRANCHNAME VARCHAR,
COURSESLNO INT)
INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
and i want
BRANCHNAME COURSESLNO COURSENAME
BR1 1 JR
BR1 2 SR
BR1 3 LT
BR1 4 ST
BR2 1 JR
BR2 NULL NULL
BR2 3 LT
BR2 NULL NULL
BR3 NULL NULL
BR3 2 SR
BR3 3 LT
BR3 NULL NULL
BR4 NULL NULL
BR4 NULL NULL
BR4 NULL NULL
BR4 4 ST
thx a lot
*** Sent via Developersdex http://www.examnotes.net ***I assume that you also have a table for branches?
create table branches(branchname varchar(10) not null primary key);
insert into branches(branchname) values('br1');
insert into branches(branchname) values('br2');
insert into branches(branchname) values('br3');
insert into branches(branchname) values('br4');
If so, use:
select b.branchname, bc.courseslno,
case when bc.courseslno is null then null else c.coursename end as
coursename
from course as c
cross join branches as b
left outer join branch_course as bc
on bc.courseslno = c.courseslno
and bc.branchname = b.branchname
order by b.branchname, c.courseslno;
Otherwise simply cross with a distinct list of branches from branch_course.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.yahoo.co.in> wrote in message
news:e2cTLu1lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> hello,
> i wanted to know to write bello with out subqueries to increase my
> application performance
>
> CREATE TABLE COURSE (
> COURSESLNO INT,
> COURSENAME VARCHAR)
>
> INSERT INTO COURSE VALUES (1,'JR');
> INSERT INTO COURSE VALUES (2,'SR');
> INSERT INTO COURSE VALUES (3,'LT');
> INSERT INTO COURSE VALUES (4,'ST');
>
> CREATE TABLE BRANCH_COURSE(
> BRANCHNAME VARCHAR,
> COURSESLNO INT)
>
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
>
> and i want
> BRANCHNAME COURSESLNO COURSENAME
> BR1 1 JR
> BR1 2 SR
> BR1 3 LT
> BR1 4 ST
> BR2 1 JR
> BR2 NULL NULL
> BR2 3 LT
> BR2 NULL NULL
> BR3 NULL NULL
> BR3 2 SR
> BR3 3 LT
> BR3 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 4 ST
>
> thx a lot
>
> *** Sent via Developersdex http://www.examnotes.net ***|||try this query
SELECT T.BRANCHNAME, BC.COURSESLNO, CASE WHEN BC.COURSESLNO IS NULL THEN
NULL ELSE C.COURSENAME END FROM
(
SELECT DISTINCT BRANCHNAME
FROM BRANCH_COURSE
) T CROSS JOIN COURSE C
LEFT OUTER JOIN BRANCH_COURSE BC ON C.COURSESLNO = BC.COURSESLNO AND
T.BRANCHNAME = BC.BRANCHNAME
ORDER BY T.BRANCHNAME
ph
"kamal hussain" wrote:

> hello,
> i wanted to know to write bello with out subqueries to increase my
> application performance
>
> CREATE TABLE COURSE (
> COURSESLNO INT,
> COURSENAME VARCHAR)
>
> INSERT INTO COURSE VALUES (1,'JR');
> INSERT INTO COURSE VALUES (2,'SR');
> INSERT INTO COURSE VALUES (3,'LT');
> INSERT INTO COURSE VALUES (4,'ST');
>
> CREATE TABLE BRANCH_COURSE(
> BRANCHNAME VARCHAR,
> COURSESLNO INT)
>
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
>
> and i want
> BRANCHNAME COURSESLNO COURSENAME
> BR1 1 JR
> BR1 2 SR
> BR1 3 LT
> BR1 4 ST
> BR2 1 JR
> BR2 NULL NULL
> BR2 3 LT
> BR2 NULL NULL
> BR3 NULL NULL
> BR3 2 SR
> BR3 3 LT
> BR3 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 4 ST
>
> thx a lot
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
here is the query that u can use:
SELECT BRANCHNAME, CASE WHEN result = 0 THEN NULL ELSE COURSESLNO END,
CASE WHEN result = 0 THEN NULL ELSE COURSENAME END
FROM
(
SELECT TOP 100 PERCENT B.BranchName, C.COURSESLNO , C.COURSENAME,
SUM(CASE WHEN C.COURSESLNO=B.COURSESLNO THEN B.COURSESLNO ELSE 0 END) result
FROM BRANCH_COURSE B
CROSS JOIN COURSE C
LEFT OUTER JOIN COURSE C1 ON B.COURSESLNO = C.COURSESLNO
GROUP BY B.BranchName, C.COURSESLNO , C.COURSENAME
ORDER BY B.BranchName
)Der
Please let me know if this worked.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"kamal hussain" wrote:

> hello,
> i wanted to know to write bello with out subqueries to increase my
> application performance
>
> CREATE TABLE COURSE (
> COURSESLNO INT,
> COURSENAME VARCHAR)
>
> INSERT INTO COURSE VALUES (1,'JR');
> INSERT INTO COURSE VALUES (2,'SR');
> INSERT INTO COURSE VALUES (3,'LT');
> INSERT INTO COURSE VALUES (4,'ST');
>
> CREATE TABLE BRANCH_COURSE(
> BRANCHNAME VARCHAR,
> COURSESLNO INT)
>
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
>
> and i want
> BRANCHNAME COURSESLNO COURSENAME
> BR1 1 JR
> BR1 2 SR
> BR1 3 LT
> BR1 4 ST
> BR2 1 JR
> BR2 NULL NULL
> BR2 3 LT
> BR2 NULL NULL
> BR3 NULL NULL
> BR3 2 SR
> BR3 3 LT
> BR3 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 4 ST
>
> thx a lot
>
> *** Sent via Developersdex http://www.examnotes.net ***
>