I have a view which is working fine, but i have been told that i need to make sure the resulting output excludes all rows with nic_status equal to the string "removed".
Does anybody know in which part of the code below i would place the conditional ...
WHERE nic_status != 'removed'
view code below
CREATE VIEW `view2` AS
select
`t2`.`serial` AS `serial`,
`t2`.`nic_name` AS `nic_name`,
`t2`.`nic_ip` AS `nic_ip`,
`t2`.`nic_duplex` AS `nic_duplex`,
`t2`.`nic_speed` AS `nic_speed`,
`t2`.`nic_status` AS `nic_status`,
`t2`.`datetimestamp` AS `datetimestamp`
from `network` `t2`
where (`t2`.`serial`,`t2`.`nic_name`,`t2`.`datetimestamp`) in
(select `network`.`serial` AS `serial`,`network`.`nic_name` AS `nic_name`,max(`network`.`datetimestamp`) AS `MAX(datetimestamp)`
from `network` where ((`network`.`serial` = `t2`.`serial`) and (`network`.`nic_name` = `t2`.`nic_name`)) group by `network`.`serial`,`network`.`nic_name`)
order by `t2`.`serial`,`t2`.`nic_name`;
The answer is: "It depends".
You are doing two things in your view -
(a) determining the maximum datetimestamp for each tuple (serial, nic_name) of the network table.
(b) Fetching a few more columns for the tuple (serial, nic_name, max_datetimestamp), nic_status included.
Now, do you want to consider status = "removed" while finding out the max of datetimestamp ?
If no, then the predicate simply gets added to the subquery.
If yes, then the predicate goes in the outer query.
Again, in the latter case, what do you want to do if the max of datetimestamp is for a record that has nic_status = "removed" ? The inner query would fetch that, but it would be discarded by the outer query. Do you want no rows returned for that tuple, or do you want to check for the max datetimestamp of records with nic_status != "removed" in the first place itself ?
thank you for explaining that Tyler. Im pretty new to this so its quite hard for me to get my head around
I guess end goal is to have a view which will show the "latest" record for that tuple unless that record has has a nic_status of "removed", in which case i dont want to it to return a value for that nic_name at all
For example .... If I have 4 'nic_names' bge0, bge1, bge2 and bge3, and the "latest" time stamped record for 'nic_name' bge1 has nic_status of "removed" , then I would want NO record for bge1 to appear on the view at all ! same goes for any other interfaces
so my view for example will only have latest records for
bge0
bge2
bge3
With this in mind, I guess the answer to your question would be that the predicate would go in the outer query as effectively i would want to
a) get all the latest records for each unique interface
b) once this is all in, return only those that dont have nic_status of "removed"
apologies for the lack of knowledge on how views are organised but would you be able to advise whereabouts in the view, i would place my WHERE statement.. I am having trouble working out which is the inner query and which is the outer ...would I put an "AND" after the current WHERE statement or do I need a whole new WHERE statement in there
