January 29, 2010 at 8:44 am
Hi All,
I need to build a query that displays Machine Information based on the status of those same machines.
I have 2 tables, one gives me all the machines in the system, the other the current status ( only for the ones that are running at that point).
The 2 tables are as follows:
--MACHINES
--
CREATE TABLE [dbo].[tbl_Prod_Machines](
[MachineID] [nchar](8) NOT NULL,
[Description] [nchar](16) NULL,
CONSTRAINT [PK_tbl_Items] PRIMARY KEY CLUSTERED
(
[MachineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- STATUS
--
CREATE TABLE [dbo].[tbl_Prod_Running](
[MachineID] [nchar](8) NOT NULL,
CONSTRAINT [PK_tbl_Prod_Running] PRIMARY KEY CLUSTERED
(
[MachineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Lets say I have the following values:
INSERT INTO [dbo].[tbl_Prod_Machines](MACHINEID, DESCRIPTION) VALUES ('A','ZONE A')
INSERT INTO [dbo].[tbl_Prod_Machines](MACHINEID, DESCRIPTION) VALUES ('B','ZONE B')
INSERT INTO [dbo].[tbl_Prod_Machines](MACHINEID, DESCRIPTION) VALUES ('C','ZONE C')
INSERT INTO [dbo].[tbl_Prod_Machines](MACHINEID, DESCRIPTION) VALUES ('D','ZONE D')
INSERT INTO [dbo].[tbl_Prod_Machines](MACHINEID, DESCRIPTION) VALUES ('E','ZONE E')
INSERT INTO [dbo].[tbl_Prod_Machines](MACHINEID, DESCRIPTION) VALUES ('F','ZONE F')
INSERT INTO [dbo].[tbl_Prod_Running](MACHINEID) VALUES ('A')
INSERT INTO [dbo].[tbl_Prod_Running](MACHINEID) VALUES ('D')
INSERT INTO [dbo].[tbl_Prod_Running](MACHINEID) VALUES ('E')
Now I need a table that gives me all the Machines with an extra column with the status (Running or Not Running) something like:
MachineID, Description, Status
A, ZONE A, RUNNING
B, ZONE B, NOT RUNNING
C, ZONE C, NOT RUNNING
D, ZONE D, RUNNING
E, ZONE E, RUNNING
F, ZONE F, NOT RUNNING
Hope this explains my problem, please keep in mind that all the values may change, new machines will be added, the status will change constantly and so on...
At this point I have this but I am not sure if I'm even close... The Status shows RUNNING in all of the columns... not really what I expect!
SELECT MACHINEID, DESCRIPTION,
CASE
WHEN EXISTS(SELECT * FROM TBL_PROD_RUNNING) THEN 'RUNNING' ELSE 'NOT RUNNING' END AS STATUS
FROM TBL_PROD_MACHINES
Thank you all for your time!!!
January 29, 2010 at 10:04 am
You want to do a a LEFT OUTER JOIN. With tbl_Prod_Machines as the left (first) table. Then your case would be when [column from right table] is null then 'Stopped' else 'Running'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 29, 2010 at 1:22 pm
Perfect!!
Thank you very much!
January 29, 2010 at 8:37 pm
Hopefully, you ended up with something like this...
SELECT m.MachineID,
m.Description,
CASE
WHEN r.MachineID > '' --A bit faster than IS NOT NULL
THEN 'RUNNING'
ELSE 'NOT RUNNING'
END AS Status
FROM dbo.tbl_Prod_Machines m
LEFT OUTER JOIN dbo.tbl_Prod_Running r ON m.MachineID = r.MachineID
Of course, if we tweak your original code to produce what you wanted by correlating the subquery, it'll be a tiny bit faster because it does fewer reads and 1 less scan...
SELECT MACHINEID, DESCRIPTION,
CASE
WHEN EXISTS(SELECT 1 FROM TBL_PROD_RUNNING r WHERE m.MachineID = r.MachineID ) THEN 'RUNNING' ELSE 'NOT RUNNING' END AS STATUS
FROM TBL_PROD_MACHINES m
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply