I have a simple tree table in which I reference types, sub-types (no level limit), brands and models for a simple inventory application. data structure is as shown below: (nodeIsModel is a boolean …
Tag: sql
How to sort an EAV model that have null values?
We have an EAV (Entity-Attribute-Value) data model that can be simplified with those 3 tables: We want to sort entities on certain field: However, this will not include entities that do not have values for this field. The wanted behavior would be to have those NULL entries at the beginning of the result, like…
Arithmetic operation on the subsets of results from a SQL groupby statement
I have a schema with sex(m/f),state_name(all states of a country), student_name,and education (phd,ms). I use following query to return total males or females in each category of education for every …
MySQL query result does not return some rows that should be in the result
This is a query that should return a match result of a lost item that has been found. According to my database below, I expect it to return two rows, but only one row is returned. Before I added the second rows in tbl_lost_items and tbl_found_items the result was an empty set. This is the result These are my …
How to compare column combinations from two tables?
I have two tables from different years, 2015 and 2016. Both tables have three columns as exampled below in table_2015: I want to see how many people had the same plan type between the years and how many people switched plan types. How do I go about doing this? Answer If customers are in each table only once a…
Getting wrong sum when using group by with inner join
With reference to this question (How to get the sum in a joined table when using group by – getting wrong results) I have two tables orders and order_items. I need to group the results by days. But I also need to get the sum of energy_used for each day from another table. When I try that using a join,
How to delete duplicate rows in a table based on what is supposed to be a unique column referring to a table in another database
In Table A PersonID refers to Table B another database and is supposed to be unique here. For reasons, it isn’t and a stored procedure that uses PersonID and FacilityID as input parameters is returning multiple rows for the instances of that duplicate PersonID. I would like to remove the top record in T…
Update Rows Where the following conditions are true
I am attempting to overwrite a field value with the value from another table where a certain condition is true. I have mocked up my code below Pseudocode: My Code: But this is returning the error: I’m not sure where i am going wrong exactly Answer If you are looking for the team “ops”, then …
Having trouble with adding one property to an object inside a list after assigning the rest of them
I’ve been having trouble (again) with some object-oriented code. Currently I have a piece of code that populates a list with objects and its properties that goes like this: What I need is to add the baseID property to each of the objects. This is what I have currently: I want this loop to take the value…
How can I generate JSON results in older SQL Server versions?
I have a table which contains data similar to this. I want to get ItemNames in JSON format as I have mentioned in the expected output below. Expected output: Note: I know in latest SQL Server, there are functions FOR JSON to convert to JSON format but it won’t work in older SQL versions. Therefore, I am…