Get 0 value from a count with no rows
You need to use the COALESCE function in PostgreSQL http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html Essentially you need to tell SQL how to handle NULLs. i.e. When NULL return 0.
sql
How to delete in MS Access when using JOIN’s?
Delete Table1.* From Table1 Where Exists( Select 1 From Table2 Where Table2.Name = Table1.Name ) = True To expand on my answer, the official SQL specification does not provide for using Joins in action queries specifically because it can create ambiguous results. Thus, it is better (and Access is much happier) if you can avoid … Read more
How to get table comments via SQL in Oracle?
Since 10g Oracle doesn’t immediately drop tables when we issue a DROP TABLE statement. Instead it renames them like this BIN$IN1vjtqhTEKcWfn9PshHYg==$0 and puts them in the recycle bin. This allows us to recover tables we didn’t mean to drop. Find out more. Tables in the recycle bin are still tables, so they show up in … Read more
Prevent duplicate values in LEFT JOIN
I like to call this problem “cross join by proxy”. Since there is no information (WHERE or JOIN condition) how the tables department and contact are supposed to match up, they are cross-joined via the proxy table person – giving you the Cartesian product. Very similar to this one: Two SQL LEFT JOINS produce incorrect … Read more
Oracle equivalent of Postgres’ DISTINCT ON?
The same effect can be replicated in Oracle either by using the first_value() function or by using one of the rank() or row_number() functions. Both variants also work in Postgres. first_value() select distinct col1, first_value(col2) over (partition by col1 order by col2 asc) from tmp first_value gives the first value for the partition, but repeats … Read more
How to set collation of a column with SQL?
To change the database’s collation ALTER DATABASE MyDataBase COLLATE [NewCollation] To change the collation of a column ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation] But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index. You can … Read more
SQL: how to limit a join on the first found row?
The key word here is FIRST. You can use analytic function FIRST_VALUE or aggregate construct FIRST. For FIRST or LAST the performance is never worse and frequently better than the equivalent FIRST_VALUE or LAST_VALUE construct because we don’t have a superfluous window sort and as a consequence a lower execution cost: select table_A.id, table_A.name, firstFromB.city … Read more
Is ID column required in SQL?
If you really do have some pre-existing column in your data set that already does uniquely identify your row – then no, there’s no need for an extra ID column. The primary key however must be unique (in ALL circumstances) and cannot be empty (must be NOT NULL). In my 20+ years of experience in … Read more
Rails Nested Joins Activerecord with conditions
Looks like you don’t need nested joins here. Try to use something like Event.joins(:store).where(stores: {retailer_id: 2}) Nested join should also work using stores Event.joins(:store => :retailer).where(stores: {retailer: {id: 2}})
How to debug a T-SQL trigger?
You’re actually over-thinking this. I first run this query in one window (to set things up): create table X(ID int not null) create table Y(ID int not null) go create trigger T_X on X after insert as insert into Y(ID) select inserted.ID go I can then discard that window. I open a new query window, … Read more