I am trying to calculate the number of users who have an active subscription on a particular day. I also want information related to the subscription plan they are on. I have a subscriptions table which includes the start date…
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 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…
I've mysql database where data is column: +----+-------+--------+--+ | ID | refID | data | | +----+-------+--------+--+ | 1 | 1023 | aaaaaa | | | 2 | 1024 | bbbbbb | | | 3 | 1025 | cccccc |…
I have a MySQL query that searches through a zipcode database for city names. The query takes about 4 seconds, to search through 620,000 zipcodes. I would like ideas how I can make this faster. First, the tables: I have…
Product prod_id prod_name 10 Orange 11 Apple 12 Carrot 13 Lettuce Category cat_id cat_name 20 Fruit 21 Vegetable Item item_id property_type property_value 30 fk_prod_id 10 30 fk_cat_id 20 31 fk_prod_id 11 31 fk_cat_id 20 32 fk_prod_id 12 32 fk_cat_id 21…
I have this very simple table: CREATE TABLE MyTable ( Id INT(6) PRIMARY KEY, Name VARCHAR(200) /* NOT UNIQUE */ ); If I want the Name(s) that is(are) the most frequent and the corresponding count(s), I can neither do this…
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…