Get non-null columns in a SQL join – Mysql
I have a query that gets the data from two tables (TABLE_ONE left join TABLE_TWO) that have the same columns. I need to get the data from TABLE_ONE, but if the data is null and in TABLE_TWO it is not…
MySQL join based on where from 2nd table
I have two tables as follows: docs id carid name 1 1 doc1 2 1 doc2 3 2 doc3 4 1 doc4 5 5 doc5 cars carid parentid name 1 4 car1 2 5 car2 3 4 car3 4 4…
Search Query by Child field Eloquent relation – Laravel
My Product Model is public function IncomeRepo(){ return $this->hasMany(Income::class,'Product'); } My Income Report Model Is public function ProductData(){ return $this->belongsTo(Product::class,'Product','id'); } My Query is public function SearchIncomeData(Request $request){ $GetFromDate = $request->FromDate; $GetToDate = $request->ToDate; $ProductData = Product::with('IncomeRepo')->whereBetween('created_at', [$GetFromDate, $GetToDate])->get(); return…
Insert into a table only those records which doesn't exists found by a join – Postgresql
Below is the query which finds the matching record and returns those "main"."item"."id" which matches the values inside IN clause. select main.item_vendor.item_id from main.item_vendor join main.item on item.id = main.item_vendor.item_id WHERE "main"."item"."id" IN ('188646', '200000699') Above query returns only one…
MYSQL Get Value If Condition is met
I am struggling to figure this out, I have a simple MySQL table (test_scores) that is three columns, id(students), date and test grade. id date grade 1 10/20/22 A 2 10/20/22 B 1 11/1/22 F 2 11/1/22 C 3 11/1/22…
Using WHERE with multiple columns with different data types to satisfy a single input in bash and postgressql – Postgresql
please assist with the following. i m trying to run a script that accepts one argument $1. The argument can either be a string or character or an integer. I want to use the argument in there where clause to…
Count records from two tables with foreign keys – Mysql
Suppose I have these MySql tables: TABLE_PEOPLE id name 1 John 2 Albert 3 Joanna 4 Mike 5 Norton TABLE_COLOR id people_id colors 1 1 Green 2 1 Red 3 3 Yellow 4 3 Blue 5 2 Green 6 4…
Why the count alias column is not returned as part of ActiveRecord_Relation? – Postgresql
I have two tables. StudentGroup and StudentGroupsMembers. StudentGroup.id = StudentGroupMembers.group_id is the foreign key relation. sg = StudentGroup.joins(:student_group_members).select(" student_groups.id, student_groups.name, count(student_group_members.id) as cnt ").group('student_group_members.group_id, student_groups.name, student_groups.id').where(id: [3,4,5,6,6,7,7,8,8]).where("student_group_members.member_type='ra'") This return ActiveRecord_Relation [#<StudentGroup:0x00007fc11253d088 id: 3, name: "Blah">, #<StudentGroup:0x00007fc11253cc78 id: 4, name: "Foo">,…
Using table columns as a filter/regexp for another table – Postgresql
Need to figure out an efficient way to query a table using another table as a filter/config (postgres 14.5). The filter table has 4 levels that match 4 levels in the data. Each level can be read as a RegExp…
How to optimize an UPDATE and JOIN query on practically identical tables? – Mysql
I am trying to update one table based on another in the most efficient way. Here is the table DDL of what I am trying to update Table1 CREATE TABLE `customersPrimary` ( `id` int NOT NULL AUTO_INCREMENT, `groupID` int NOT…