mysql,join,inner-join,sql-view
Subqueries are an unnecessarily complicated way of going about this. Easier just to join emp to works, work to dept, and then dept back to emp. select e.*, e2.* from emp e inner join works w on w.eid = e.eid inner join dept d on w.did = d.did inner join...
sql,sql-server,common-table-expression,sql-view
Views can be indexed but CTE can't. So this is one important point. CTE work excellent on tree hierarchyi.e. recursive Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide...
Try this: SELECT e.Text AS StatusText, a.Created AS [DATE], CASE a.status WHEN 2 THEN NULL WHEN 3 THEN NULL ELSE a.Username END, b.Name AS CustomerName, c.Name AS ServiceName, d.Message AS DeviationMessage FROM dbo.StatusUpdate AS a LEFT OUTER JOIN dbo.Customer AS b ON a.CustomerId = b.CustomerID LEFT OUTER JOIN dbo.Service AS...
You might look to SQL views as a result set, that may combine the values of multiple tables (by joining, using sub-select's etc.). If you query a SQL view, you get better performance. Linq 2 SQL returns you set of data and does a mapping to an object, so you...
sql,sql-server,performance,join,sql-view
You are not correct when you state "It appears to me that in general the join of views is not recommended." Views can be combined with other views and will perform well provided that all JOINs are optimizable and have the appropriate index created and any filtering done within the...
mysql,duplicates,self-join,sql-view
Try using conditional aggregation instead: select eav.entity_id, max(case when entity_attributes_id = 2 then eav.value end) as company_id, max(case when entity_attributes_id = 1 then eav.value end) as entity_name, max(case when entity_attributes_id = 3 then eav.value end) as company_name, . . . from entities_attributes_values eav group by eav.entity_id; This will make it...
Aggregate functions, such as MIN are for aggregating across multiple rows, and will not do what you expect here (as you've already found out). Instead, you should use a case to choose which table and column to show from. Yes, I mean "table" here because your two joined rows are...
SELECT TRIM(USER.PNAME || ' ' || USER.FNAME) AS "NAME" ...
Yes, you would need to create the "GroupBillRange" table. Otherwise you lose any record of what the Bill Start/Stop dates were on the old groups. (Sure, you could assume that they are always from the 15th to the 14th. But the one time that they are not, you will have...
oracle,stored-procedures,plsql,metadata,sql-view
From documentation, ALL_PROCEDURES lists all functions and procedures, along with associated properties. For example, ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified. It doesn't clarify...
c#,entity-framework,asp.net-mvc-4,sql-view,updatable-views
Here is my work around, essentially using the data from the initial view to present data to the model, then on post map the data to the actual table (used within initial view) that contains a Primary Key: public ActionResult Customer(int id) { var viewModel = new Customer(); using (var...
sql,postgresql,pattern-matching,sql-view
First, you can simplify your SELECT. select concat_ws ('/' , c.abreviation , to_char(date_op,'yy/mm') , to_char(compteur_op_cat, 'FM000') ) AS num_bord from operation o join categories c USING (id_cat); But there are no "triggers on SELECT". You may be looking for a VIEW or a MATERIALIZED VIEW. The key to performance with...
Your outer query isn't doing anything, so just remove it: SELECT H.username, H.variety, IFNULL(SUM(H.weight),0) - IFNULL(HU.weight,0) As qty FROM harvest H LEFT JOIN harvest_used HU ON H.variety = HU.variety AND H.username = HU.username WHERE H.username = 'palendrone' GROUP BY H.variety ORDER BY H.variety ASC; MySQL does not allow subqueries in...
use this query: I was unable to connect to SQLfiddler. So, do check the code and tell me what it returned. select * from ( SELECT id, name, value FROM table A) pivot ( max(value) for name in ('dog name', 'cat name', 'childs') ) order by id You can learn...
Use an OUTER JOIN: select `l`.`ID` AS `IDL`, `l`.`NAME` AS `NAME`, AVG(`lr`.`RATING`) AS `RATING` from `LESSONS` AS `l` LEFT JOIN `RATINGS` AS `lr` ON `l`.`ID` = `lr`.`ID` group by `l`.`ID`; Also, I don't think there is a need for the Case statement -- you can just use AVG(lr.rating). A Visual...
Try dropping the parentheses: create view ex_view as with alias1 as (select...), alias2 as (select ... from alias1), alias3 as (select col1, col2 ... from alias2) from alias3; ...
Use lag() with a subquery: create view alarming_data as select id, data1, data1high, data1.low from (select sd.*, lag(data1) over (order by id) as prevvalue from SensorData sd ) sd where data1 <> prevvalue; If you want the first value, then include or prevvalue is null in the where clause....
SELECT Id, CASE WHEN Give IS NOT NULL and Quanity IS NOT NULL THEN Give + '/' + Quanity WHEN Give IS NOT NULL and Quanity IS NULL THEN Give WHEN Give IS NULL and Quanity IS NOT NULL THEN Quanity WHEN Give IS NULL and Quanity IS NULL THEN...
oracle,select,oracle11g,case,sql-view
What you are doing is similiar to: with d1 as (select 1 col1, 'A' col2 from dual union select 2, 'B' from dual), d2 as (select 1 col1, 'Q' col2, 'W' col3, 'E' col4 from dual), d3 as (select 2 col1, 'R' col2, 'T' col3, 'Y' col4 from dual) select...
sql,entity,primary-key,sql-view
You cannot create primary keys on the view itself. To get around the no primary key issue, you can create a unique column that the entity framework will pick up and use. ISNULL(CAST((row_number() OVER (ORDER BY <columnName>)) AS int), 0) AS ID So: SELECT ISNULL(CAST((row_number() OVER (ORDER BY uniqueid)) AS...
why would the sql command be any different if executed as a command from C# ? the following should work: using (var command1 = connection.CreateCommand()) { command1.CommandText = "IF EXISTS(select * from INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'ViewName') DROP VIEW dbo.ViewName"; //todo: execute command, etc... } ...
You can add a UUID column to every row of the Views so then you can use the UUID column as an @Id.
mysql,join,sql-view,sqlperformance
The following query works: (although I am not using the view) select pc1.product_id, products.name, products.section, group_concat(categories.name) from products inner join product_categories pc1 on (pc1.product_id = products.product_id and pc1.category_id = 3) inner join product_categories pc2 on (pc2.product_id = products.product_id) inner join categories on (categories.category_id = pc2.category_id) group by pc1.product_id, products.name, products.section...
Assuming your grades have a limit to how high they can go could you use an isnull in your orderby and set a default high value for null occurences and a tie breaker for when two values have the same rank (e.g 35) e.g CREATE VIEW [dbo].[test] AS SELECT distinct...
sql-server,tsql,sql-server-2008-r2,sql-server-2012,sql-view
There is no solution to the problem except using T-SQL in management studio. This is a bug, or as Microsoft calls it by design. The view designer has more issues then just BIt datatypes. There are several valid SQL syntaxes that are not supported by designer. That's the reason I...