Select data from multiple tables and join them in MS SQL 2005
Page 1 of 1
20100917
Select data from multiple tables and join them in MS SQL 2005
Another question I got at work, someone wanted to have all instrument sets that remain sterile for 360 days for the operation rooms and all instrument sets that remain sterile for 180 days for other departments in the hospital.
In the application we use there is a table called PSET which has all information for instrument sets, as well as other variations on sets. First I made an easy query showing me everything I can with this table. It looked as follows:
How to get the department and see if they are still used? Well that information is located in another table called SerienNum and is linked through the Snummer of a set. First we are also going to select some more information, to make life easier for the person who wanted this. We are going to select the visible serial number which is also part of the SerienNum table and the number that stands for the department. Then we need to join both tables through the Snummer and we are going to add going to add another filter which helps me see if a set is still used. Now it looks like below.
The results we have now are still hard to read, because we still don’t have any idea what department is which. We have no names but only some numbers.
The names are located in a third table, called PABT. To get these we need to do another join on ANummer. Of course we are not going to select ANummer anymore since we can now select a name.
Our query will look something like this.
As you can see we now have a double INNER JOIN, joining the SNummer from two tables and ANummer from two tables together.
Now we needed something similar for other departments, unfortunately there are about 50 of them, and when I select on Verfallzeit = 180 I will also get all the Operating rooms. To stop this I have added one more filter, SerienNum.ANummer BETWEEN '1.31' AND '2.00'. This way I only get departments whose number is between 1.31 and 2.00 and that rules out the Operating rooms. So in the end I have the following.
If you have any questions on why or what in the above example, please reply to this post and I will try to answer as quickly and accurate as possible. Hope this helped.
In the application we use there is a table called PSET which has all information for instrument sets, as well as other variations on sets. First I made an easy query showing me everything I can with this table. It looked as follows:
- Code:
SELECT Bezeichnung
FROM PSET
WHERE (STyp >= '260') AND (Verfallzeit = 360)
How to get the department and see if they are still used? Well that information is located in another table called SerienNum and is linked through the Snummer of a set. First we are also going to select some more information, to make life easier for the person who wanted this. We are going to select the visible serial number which is also part of the SerienNum table and the number that stands for the department. Then we need to join both tables through the Snummer and we are going to add going to add another filter which helps me see if a set is still used. Now it looks like below.
- Code:
SELECT PSET.Bezeichnung, SerienNum.AnzSerienNum, SerienNum.ANummer
FROM PSET INNER JOIN
SerienNum ON PSET.Snummer = SerienNum.SNummer
WHERE (PSET.STyp >= '260') AND (PSET.Verfallzeit = 360) AND (SerienNum.Gesperrt = 'False')
The results we have now are still hard to read, because we still don’t have any idea what department is which. We have no names but only some numbers.
The names are located in a third table, called PABT. To get these we need to do another join on ANummer. Of course we are not going to select ANummer anymore since we can now select a name.
Our query will look something like this.
- Code:
SELECT PSET.Bezeichnung, SerienNum.AnzSerienNum, PABT.Bezeichnung AS Dept
FROM PSET INNER JOIN
SerienNum ON PSET.Snummer = SerienNum.SNummer INNER JOIN
PABT ON SerienNum.ANummer = PABT.ANummer
WHERE (PSET.STyp >= '260') AND (PSET.Verfallzeit = 360) AND (SerienNum.Gesperrt = 'False')
As you can see we now have a double INNER JOIN, joining the SNummer from two tables and ANummer from two tables together.
Now we needed something similar for other departments, unfortunately there are about 50 of them, and when I select on Verfallzeit = 180 I will also get all the Operating rooms. To stop this I have added one more filter, SerienNum.ANummer BETWEEN '1.31' AND '2.00'. This way I only get departments whose number is between 1.31 and 2.00 and that rules out the Operating rooms. So in the end I have the following.
- Code:
SELECT PSET.Bezeichnung, SerienNum.AnzSerienNum, PABT.Bezeichnung AS Dept
FROM PSET INNER JOIN
SerienNum ON PSET.Snummer = SerienNum.SNummer INNER JOIN
PABT ON SerienNum.ANummer = PABT.ANummer
WHERE (PSET.STyp >= '260') AND (PSET.Verfallzeit = 180) AND (SerienNum.Gesperrt = 'False') AND (SerienNum.ANummer BETWEEN '1.31' AND '2.00')
If you have any questions on why or what in the above example, please reply to this post and I will try to answer as quickly and accurate as possible. Hope this helped.
thierry- Posts : 2238
Join date : 2009-08-18
Age : 42
Location : Zoetermeer
Permissions in this forum:
You cannot reply to topics in this forum