REALLY NEED HELP!! - SQL Server Forums

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

REALLY NEED HELP!!

Author

Topic

jcoleman_11
Starting Member

11 Posts

Posted - 2009-01-16 : 17:38:26

Here is my script;

select m.tradename, m.genname, m.medid, m.medaltid, m.medaltid2, m.strength, m.units, m.strength, m.units, m.volume, m.unitsvolume, m.dosage, c.classname, s.levelname, o.overname, m.cost
from tblmed as m, tblclassnames as c, tblsecurnames as s, tblovernames as o
where c.classtag=m.medclass and s.levelnum=m.securitylevel and m.overridelevel=o.overtag
order by m.tradename;

basically, this is a script that im building at work to try and extract data from a hospital system. It's a list of all their meds. The only problem is, is when i run it, i only get 600 meds, and the list is about 2200 meds long.

Now, my problem is that if m.overridelevel equals 0, then that value isn't found in tblovernames, therefore wont fit the criteria.

Is there a way if m.overridelevel equals 0, to still include it even though 0 isnt a value in tblovernames?

i tried doing like a (case m.overridelevel when 0 then “None” else o.overtag=m.overridelevel end) but that didnt work. any ideas?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-16 : 17:50:13

Do you mean this?


SELECT   M.TRADENAME,
         M.GENNAME,
         M.MEDID,
         M.MEDALTID,
         M.MEDALTID2,
         M.STRENGTH,
         M.UNITS,
         M.STRENGTH,
         M.UNITS,
         M.VOLUME,
         M.UNITSVOLUME,
         M.DOSAGE,
         C.CLASSNAME,
         S.LEVELNAME,
         O.OVERNAME,
         M.COST
FROM     TBLMED AS M
         INNER JOIN TBLCLASSNAMES AS C
           ON C.CLASSTAG = M.MEDCLASS
         INNER JOIN TBLSECURNAMES AS S
           ON S.LEVELNUM = M.SECURITYLEVEL
         LEFT OUTER JOIN TBLOVERNAMES AS O
           ON M.OVERRIDELEVEL = O.OVERTAG
ORDER BY M.TRADENAME

jcoleman_11
Starting Member

11 Posts

Posted - 2009-01-16 : 18:18:05

omg, that worked flawlessly. ive never seen this inner join left outer join business. Thanks a million!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-16 : 18:19:45

quote:
Originally posted by jcoleman_11

omg, that worked flawlessly. ive never seen this inner join left outer join business. Thanks a million!

Welcome. Then you should read booksonline about it.

jcoleman_11
Starting Member

11 Posts

Posted - 2009-01-16 : 18:29:13

honestly, id love to, but sql isnt that large a part of my job. i provide technical support for computer systems in hospitals. most of teh scripts we need to run are already created, its only when a customer requests something custom that i need to freestyle a bit. It would interest me in learning more then. Thanks again for your help!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-18 : 00:35:56

You can learn basic T-SQL from here:
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources