filter union with different value to join

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

General SQL Server Forums

New to SQL Server Programming

filter union with different value to join

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2010-02-17 : 08:22:46

i have a 5 table

subject
------------------
sbcode pk
yrlvlid
desc
curricode

curriculum
------------------
curricode - pk
cdesc

section
------------------
secid - pk
sdesc
curricode
yrlvlid

yearlvl
------------------
yrid
yrdesc

this is the table value of curriculum
------------------------
curricode - cdesc
------------------------
bec - adadadad
dd - ddddddd
gg - pppppppppp

the bec should always get on the other curriculum

if i have 5 subject on bec on the same yrlvl

i will union it on the other curriculum

then i will filter all the subject with in every section within every curriculum has bec.....

this is my query
let assume that the yrlvlid is value

this is for the curricode dd

Select * from curriculum where curricode in(Select curricode from
(Select sbcode,desc,yrlvlid,'dd' as `curricode` from subject where subjcode='BEC'
union all
Select sbcode,desc,yrlvlid,'dd' as `curricode` from subject where subjcode='dd')

im having difficulties in every curricode to filter

can you plz.. reply immediately...

tnx... a lot....

finding the truth
making a maze on my mind....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-17 : 08:45:00

I have read it 5 times but still not understanding - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 09:27:39

see how to post question with necessary data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wormz666
Posting Yak Master

110 Posts

Posted - 2010-02-17 : 09:28:44

all i want to filter all the curriculum with in every curriculum has union on curriculum on curricode has a value of bec..
so therefor in every curriculum except bec... filter with the corresponding subject of the curriculum...

like for example

-subject
--------------------------------------------------------------------------------------------
subjcode - desc - yrlvlid - curricode
--------------------------------------------------------------------------------------------
math1 - mathematics - 1 - bec
english1 - english - 1 - bec
science1 - science - 1 - bec
advancemath - integral - 1 - dd
advanscience -fffff - 1- gg

--------------------------------

i want to union it and with every curricode on the value of bec which is categories

suppose to be this is the output

-----------------------------------------------
math1 - mathematics - 1 - dd
english1 - english - 1 - dd
science1 - science - 1 - dd
advancemath - integral - 1 - dd
math1 - mathematics - 1 - gg
english1 - english - 1 - gg
science1 - science - 1 - gg
advanscience -fffff - 1- gg

finding the truth
making a maze on my mind....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 09:34:47

do u mean this?

SELECT subjcode,desc,yrlvlid,curricode
FROM
(
SELECT d.subjcode,d.desc,d.yrlvlid,c.curricode
FROM
(SELECT curricode
FROM curriculum
WHERE curricode <> 'bec')c
CROSS JOIN 
(SELECT subjcode,desc,yrlvlid
 FROM subject
 WHERE curricode='bec') d
UNION
SELECT subjcode,desc,yrlvlid,curricode
FROM subject
WHERE curricode<>'bec'
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources