I have this query SELECT * FROM table1 as t1 WHERE (t1.phone != "" OR t1.sms_phone != "") and t1.docType in (1,2,3) and not exists (select id from table2 where product_id=1 and doc_id=t1.id); And I want to replace the part…
I tried to fetch 50 products from a database with the highest price but if i set the LIMIT at 50 it's just fetching the first 50 products order by the price. This is not what i want. How can…
i have query like this, but displays wrong last_message. $users = Message::join('users', function ($join) { $join->on('messages.from_id', '=', 'users.id') ->orOn('messages.to_id', '=', 'users.id'); }) ->where(function ($q) { $q->where('messages.from_id', auth()->user()->id) ->orWhere('messages.to_id', auth()->user()->id); }) ->where('users.id','!=',auth()->user()->id) ->select([ 'users.id', 'users.name', 'users.avatar', DB::raw('MAX(messages.created_at) max_created_at'), DB::raw('MAX(messages.body) last_message'), DB::raw('CASE…
I have the following table: CREATE TABLE table_one( person varchar(55), date_value date, proj varchar(2), value int, time varchar(2 ); INSERT INTO table_one VALUES ('A1',2020-10-01'W',10,'T1') ('A1',2020-10-01'A',5,'T2') ('A1',2020-10-01'P',6,'T3') ('A1',2020-10-01'A',9,'T4') ('A1',2020-10-01'P',11,'T5') ('A1',2020-10-01'A',4,'T6') ('A1',2020-10-01'P',2,'T7') ('A1',2020-10-01'A',1,'T8') ('A1',2020-10-01'P',10,'T9') ('A1',2020-10-01'A',8,'T10') I want an SQL query which creates…
I have below tables DEMO fiddle In x_table, I have different records. I want to fetch all currencies from x_table where continent is Asia which is straight forward as below, SELECT currency from x_table where continent='Asia' and it should return…
SELECT DISTINCT * FROM institutions LEFT JOIN units ON Unit_Institution = Institution_Key LEFT JOIN sites ON Site_Institution = Institution_Key OR Site_Key = Unit_Site This request has extremely bad performance (despite indexes) because of the OR. Removing one side of it…
I'm new to SQL and I would like to to group by ID and create another column based on value. Let me give an example: Foreign ID Type Value 1 'X' 10 1 'Y' 20 2 'X' 30 2 'Y'…
I need to add column in table called value which needs to store value from 0 to 1 up to 2 decimal points i.e. 0.25 0.50 0.75 0.80 etc.. Can anyone help with this? Thanks in advance
I have 2 tables, Customers and Countries, like so: Customers: +----+------+-----+---------------+----------------+ | ID | Name | ... | OfficeCountry | BillingCountry | +----+------+-----+---------------+----------------+ | 1 | Bill | ... | 1 | 1 | | 2 | Joe | ...…
I built this SQL: SELECT indexname FROM pg_indexes WHERE schemaname = 'foo' AND tablename = 'foo' It returns me all indexes of a table. However, I would like the index names and the type of the indexes to be displayed,…