This may be simple, but I cannot figure out the correct and simplest way to query a table which contains a date col to return the rows in which the date belongs to the current academic year. Knowing that for academic year I mean the period from the 1st of September of a year to the 31st of august of
Tag: sql
Get all matching records where IDs are joined to a comma separated list
I have two database table like below: Form Id Name Fields(varchar-255) FormFields Id Name InputType Sample data for Form Sample data for FormFields Now I write query as below: And I get only one record as below: But I want all the records of FormFields table whose Id is present in the Fields columns of Form t…
MySQL Query join last comment to topic
I have a topic and topic_comments table. I want to join them both. The join is on topic.id = topic_comments.parent_id. I want to show the topic with latest comment and order by latest comment createdate. And not show duplicate topics. Can anyone help me? So far I have this: This however doesn ‘t show to…
Generate_series in Postgres from start and end date in a table
I have been trying to generate a series of dates (YYYY-MM-DD HH) from the first until the last date in a timestamp field. I’ve got the generate_series() I need, however running into an issue when trying to grab the start and end dates from a table. I have the following to give a rough idea: Postgres 9.3…
“No database selected” when implementing Zebra_pagination with SQL/PHP
Relatively new to PHP and first Stack question, so apologies in advance for any mistakes on my part. I’m trying to implement Zebra Pagination with my SQL database (using latest MAMP) and getting an error of ‘No database selected’. I think the issue lies in how I’m hooking up my connect…
REPLACE empty string
I discover some behavior I didn’t know before. Why this line of code does not work? I can’t even have ” in where condition. It just doesn’t work. I have this from imported Excel where in some cells are no values but I’m not able to remove them unless I used LEN(”) = 0 funct…
MYSQL: UNION results between two tables where omitting records from first table if PK found in second table
I have two tables products and product_edits which hold product information on the pricelist. My app works in a way that if user changes any product info in products table it inserts it into …
Calculates the difference between employee salary and the average salary of job group
Given a table like: How to write a query that for each employee calculates the difference between his/her salary to the average salary of his/her job group? To get the avg. Salary for each group I use: But I’m struggling to find the difference between salary and avg. salary for each job title. Answer De…
Run a query with a LIMIT/OFFSET and also get the total number of rows
For pagination purposes, I need a run a query with the LIMIT and OFFSET clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT and OFFSET clauses. I want to run: And: At the same time. Is there a way to do that, particularly a way that lets
Sharing composite foreign key based on the same columns across multiple tables in MS SQL Server
I have database with multiple tables which contains information about different instruments (Currencies, Funds, Equities etc.). All instruments have Type and Id fields which represents unique key for …