Tag Info

New answers tagged

1

Did you try chaining your WITH statements together? N.B. This may not be the most-optimal method of solving this problem, but should work based on your existing approach CREATE VIEW dbo.MyViewName AS WITH R(N) AS ( SELECT 0 UNION ALL SELECT N+1 FROM R WHERE N < 12 ), MonthYears AS ( SELECT ...


1

You will have to treat the view as a table. The information_schema already does If you run SELECT table_name FROM information_schema.tables WHERE engine IS NULL; you get all the views. Just grant SELECT on the view to the user as follows GRANT SELECT ON `myDatabase`.`fordibenForYouTable` TO 'thisUser'@'localhost' ; Once you do this, you should have ...


0

Materialized Views Do Not Exist in MySQL. Flexviews has been recommended in the DBA StackExchange before Bill Karwin's post : Does MySQL have a version of Change Data Capture? Redguy's post : Need some support on MySQL Query Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you ...


1

select u.id, u.name, bool_or (a.admin) as is_admin, bool_and (a.admin) as is_complete_admin from users u inner join associations a on u.id = a.user group by u.id, u.name


1

"a boolean column stating whether or not a group has any users" Use EXISTS: CREATE VIEW group_info AS SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty FROM groups g; This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need ...


1

If you have a numbers table, you can use that. If you don't have a numbers table, just create one: CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED); INSERT dbo.Numbers WITH (TABLOCKX) (Number) SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; (You ...


0

Without knowing your table and view structure, I created a test case for myself: CREATE TABLE private_table ( id serial, col1 text ); CREATE OR REPLACE VIEW public_view AS SELECT id, col1 FROM private; CREATE OR REPLACE RULE insert_private AS ON INSERT TO public_view DO INSTEAD INSERT INTO private_table (col1) ...


0

An issue I found on a similar problem was that the ‘first’ grant on the object had to be done ‘with grant option’. Eg for the issue above, this would therefore mean: GRANT SELECT ON <underlying table owner>.<table_name> TO CST0 WITH GRANT OPTION; Then running GRANT SELECT ON <schema>.<view_name> TO CST0; should work. Note this ...


0

Bound to query If you can only change the view, not the query: this is 100 % equivalent using a correlated subquery instead of the LEFT JOIN: CREATE VIEW the_view_new AS SELECT a.id, a.name , (SELECT age_group FROM table_b WHERE id = a.id) AS age_group FROM table_a a; Your query as is just reads top and bottom row from the index now, IOW blazingly ...


8

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB RIGHT JOIN TableA ON TableA.ID = TableB.ID ) ON TableB.TypeID = TableC.TypeID If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...


3

The optimizer makes choices based on costing estimates. The cost model is generic, and may not always choose optimal plans for your particular hardware, and its assumptions may not always be valid for your circumstances. In this case, the optimizer assesses a hash join as the cheaper option over nested loops when the estimated number of rows to be joined is ...


1

In the SQL Language Reference you will find under Prerequesites (emphasized by me): The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather ...


3

Try this select a.Date, a.Foo, b.Bar from TableA a LEFT JOIN TableB b ON a.Date = b.Date UNION select b.Date, a.Foo, b.Bar from TableB b LEFT JOIN TableA a ON a.Date = b.Date Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left ...



Top 50 recent answers are included