I am trying to write an Eloquent query for a BelongsToMany relationship and where the results return only one distinct result based on the pivot table. See below for example. Installer: Id Name 1 Installer 1 2 Installer 2 State:…
I'm currently working on a PostgreSQL query to extract specific values from a JSONB column. Here's the query I'm using: select a.id, (jsonb_array_elements(a.info->'attribute1')->>'value') as attribute1, (a.info->>'attribute2') as attribute2, (a.info->>'attribute3') as attribute3, (jsonb_array_elements(a.info->'attribute4')->>'value') as attribute4 from a_table a where (cast(a.info->>'attribute3' as…
I have a problem as follows; Let's say I have 3 tables like this: tableA ID_name Name Email 1 Alice xx 2 Betty xx 3 Chris xx tableB Id_task id_name Tasks_submitted 1 1 1 2 2 1 3 1 1…
I want to select elements that are not present in the database, but exist in a postgres array. To visualize: Assume that I have this data in my table: I have come close after some research, but I do not…
I'm trying to write a query that would allow you to insert a new Room in a Building, but fail if that building ID does not exist INSERT INTO room ( building_id, room_name ) VALUES ( $1, CONCAT('Room ', (SELECT…
I have a table that looks like (never mind the database vendor as I need this in ANSI SQL): create table edge ( parent_id int not null, child_id int not null, value float not null, start date not null, end…
My database has a column that contains a JSON array like this: WITH dataset AS ( SELECT * FROM (VALUES ('1', JSON'[{ "name" : "foo" }, { "name" : "bar" }]'), ('2', JSON'[{ "name" : "fizz" }, { "name" :…
I would like to have a query with a result like below: 1000 2000 3000 or 1 3 5 so on..I want to control results from lists for instance. SELECT 1 — I know that I can use SELECT like…
Given the table below, I'm trying to find a way to get the group matching a pre-defined subset of tokens BUT the group should not contain any tokens outside of the subset. group token 3 37 1 34 1 32…
If multiple entries have the same ID (tag_num), I would like the columns to be the same. For example, if one row may have a description and another one is null but both have the same ID(tag_num), I'd like the…