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.
| Author |
Topic |
|
sica
Posting Yak Master
143 Posts |
 Posted - 2001-09-29 : 07:26:42
|
| I have 4 tables as following:typetabletypeid typename1 type12 type23 type34 type45 type5periodtableperiodid periodname1 20002 20013 19994 1998maintablemainid mainname1 test12 test23 test3The 4th table has a contrain from alla other tablereductiontablereductionid mainid periodid typeid1 1 1 12 1 2 13 1 3 14 1 4 15 1 1 26 1 2 27 1 3 28 1 4 29 1 1 3What I want is to return all available typeid for a certain mainid in case of all period are not fulfilled.Ex: For mainid = 1 the selection should return:type3type4type5because the type1 and type2 are already having the period fullfilled.My solution is to take all the types and compare the period from the periodtable w/ existing tuples in reductiontable.If the number doestn't match I have a typeMy select looks like this:SELECT typeid, typenameFROM typetable WHERE typeid IN (SELECT CASE WHEN typeid=1 AND (SELECT COUNT(periodid)FROM periodtable) <> (SELECT COUNT(periodid)FROM reductiontable WHERE mainid = 1 AND typeid=1)THEN typeidWHEN typeid=2 AND (SELECT COUNT(periodid) FROM periodtable) <> (SELECT COUNT(periodid) FROM reductiontable WHERE mainid=1 AND typeid=2) THEN typeidWHEN typeid=3 AND (SELECT COUNT(periodid) FROM periodtable) <> (SELECT COUNT(periodid) FROM reductiontable WHERE mainid=1 AND typeid=3) THEN typeidWHEN typeid=4 AND (SELECT COUNT(periodid) FROM periodtable) <> (SELECT COUNT(periodid) FROM reductiontable WHERE mainid=1 AND typeid=4) THEN typeidWHEN typeid=5 AND (SELECT COUNT(periodid) FROM periodtable) <> (SELECT COUNT(periodid) FROM reductiontable WHERE mainid=1 AND typeid=5) THEN typeidEND AS typeid FROM typetable )Does anyone have a better idea?Thanks.Best regards,Sica |
|
|
|
|
|
Better idea for a SELECT