For example, I have database with table book (id, title, themeId, authorId), table author (id, name) and table theme (id, name). I dont know, should i create entity exactly like in database (and then i will have problems with join queries): or create something like this: in second case i will have problems if authorId or themeId is null (it
Tag: database
In PostgreSQL, how to query for a computed results by the grouping of a field?
In my PostgreSQL database I have an invitations table like so: Invitations Table: Fields Details I’d like to query the Invitations table and group by unique sources. And then per source, include the total # of records and the total of those completed. Completed being where complated_at is NOT NULL. Desired Results: Suggestions? Answer You can do aggregation like so:
convert column in rows and get count of different values of same column in postresql
test convert column in rows and get count of different values of same column in postresql
Designing a mysql database of relationships between contacts?
I’m trying to design a database of contacts but I also want to keep track of their relationships with other contacts whether it be family, friends, cowokers etc. I created a table for contacts and created an affiliates table that labels the type of relationship but I’m not sure if I did the design correct and I’m unsure if this
Keyset pagination with WHERE & HAVING
I ran into a question that I can’t find a solution yet for keyset pagination: Suppose we have this query: How should we do keyset pagination on that? For a.number only, we can have WHERE a.number < ?; For rating only, we do HAVING rating < ?; If there’re two conditions for where, we can have WHERE (x, y) <
How to get TOP larger tables in SQL Server 2000 and their sizes
Is there a way via T-SQL to get the larger tables and their respective size in a SQL Server 2000 engine? Answer I think this script works with SQL Server 2000 even if sp_msforeachtable was not documented
Sum unequal and removing duplicates from SQL query results
My base query: It can get me these outputs: Output example: Obs: There is a constraint on (id, on_date) meaning there can always be only one record of a project production on a specific date. Duplicate records, that have the same id, and exist in both dates and have different production values (wanted) Single records, that exists on only one
PHP/SQL Find duplicate rows in a column, with the condition that another column is different
I need an SQL query to do the following task: I have two columns. Column ‘foo’ and column ‘bar’. The query needs to return results if and only if column ‘foo’ has different values but ‘bar’ has the same values. For example: If I was to run the query on the above dataset, then both rows indicated withe arrows above
trying to select random data PostgreSQL
I’m trying to select random strings. The problem is it returns the same value for each row. Why is that and how to fix? 10 rows Answer Postgres overoptimizes the subquery. I think this is an error, because it is missing the fact that random() is volatile. A simple fix is a correlation clause: I rewrote the logic a bit,
List of values as table
I’m looking for a smarter way to have a list of values as a table in Oracle. What I do nowadays is select ‘value1’ as val from dual union select ‘value2’ from dual What I’m hoping for is some …