Q&A

SQL flavor is Mode's unique variation, that said SQL flavor doesn't really matter. If you can show me how to do it in one variety of SQL, I'll at least know what to Google to figure it out in this variation. I'm joining two tables and trying to identify columns where the status of an item is the same, however the statuses are written differently between the two tables. Table 1 columns: Name Number Status (available, unavailable, inactive) Table 2 columns: Number Status (unassigned, unavailable, retired) Available = unassigned, unavailable = unavailable, inactive = retired. I am trying to first compare available/unassigned line up, inactive/retired line up, etc. Then I'm trying to return only the results where both status columns do not match, but since they use different words for the same idea I just don't know how to do it. by Ryan Mercer in StackOverflow on July 05, 2022 . Answer I'd simply...

I have a table Items(ID, Name, Price). For every item in Items, I‘d like to receive an output of the form: (ID, Name, Count), where Count is the number of items in Items which are cheaper in price. I have tried running a subquery with the same table but found no solution. It seems so simple, but I‘m a bit stuck. by William Bomb in StackOverflow on July 05, 2022 . Answer SELECT t1.ID, t1.Name, t1.Price, COUNT(t2.id) FROM Items t1 LEFT JOIN Items t2 ON t1.price > t2.price GROUP BY t1.ID, t1.Name, t1.Price I add Price column to the output because you have not told that the presence of the same item name with different prices is not possible. by Akina on July 05, 2022 .

I want to show the users the amount of posts, comments etc they have created in their profile section, just like any other forums that exist. I am doing this with php and MYSQLI. Post table: 1 post_id Primary int(11) AUTO_INCREMENT 2 title varchar(255) 3 users_id int(11) 4 content varchar(500) 5 type int(11) 6 imagepath varchar(50) 7 date_created datetime I have tried to add another column to the post table and increment its value by 1 each time the user makes a post in php by an INSERT statement but it's value only stays at 1, even though the user continues to create more posts. Here is what I tried: function createPost($conn, $content, $title, $users_id, $date_created, $type, $total_post){ $sql = "INSERT INTO post (title, users_id, content, date_created, type, total_post) VALUES (?,?,?,?,?,?);"; $stmt = mysqli_stmt_init($conn); if (!mysqli_stmt_prepare($...

Here is how my collection looks: What I would like to is to get a first non-null value from it, something like json-coalesce(my_collection,"$") returing 5 in the first case and true in the second. What's the best way to do this without handling each key individually? by Nik in StackOverflow on July 01, 2022 . Answer Consider below approach create temp function values(input string) returns array<string> language js as """ return Object.values(JSON.parse(input)); """; select my_collection, ( select val from unnest(values(my_collection)) val with offset where not val is null order by offset limit 1 ) as first_not_null_value from your_table if applied to sample data in your question - output is by Mikhail Berlyant on July 01, 2022 . Other helpful answers Not a general solution, but using a regex, SELE...

I am building a database using Oracle SQL Developer. I want to add a foreign key integrity constraint that cascades on update. However, when I run the following query I get an error. There is also a red line below the word UPDATE and when I hover over it, it says "Syntax Error: Partially recognized rules" ALTER TABLE JOURNAL ADD CONSTRAINT fk_journal_Doc FOREIGN KEY (Doc_id) REFERENCES DOCUMENTS(Doc_id) ON UPDATE CASCADE; / Error: Error starting at line : 92 in command - ALTER TABLE JOURNAL ADD CONSTRAINT fk_journal_Doc FOREIGN KEY (Doc_id) REFERENCES DOCUMENTS(Doc_id) ON UPDATE CASCADE Error report - ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: I am using Oracle version 21.4.3.063 Any help with solving this issue would be very much appreciated! by Shriya in StackOverflow on July 02, 2022 . Answer Oracle supports only ON DELETE CASCADE . As simple as that. If you want to...

I am working within Snowflake and have a column with a timestamp format like this: timestamp 2021-12-13T21:52:58.656216349+0000 2021-12-13T18:22:01.194783523+0000 2021-12-13T21:03:55.224874997+0000 2021-12-13T21:02:37.075422427+0000 2021-12-13T15:54:26.268433672+0000 my desired output is in a format with: 2021-12-13 Searching from past questions, I found this answer and attempted to modify it for my use by: SELECT timestamp AS original_ts, to_timestamp(REPLACE(REPLACE(timestamp,'T',' '),'+',''),'YYYY-MM-DD') AS modified_ts FROM table but get the following error: Can't parse '2022-01-26 00:06:11.1851022090000' as timestamp with format 'YYYY-MM-DD' How can I resolve this error and format the timestamp column into a more familiar date column ignoring the time entirely? by JLuu in StackOverflow on July 03, 2022 . Answer I think you want to use TO_DATE her...

My table in MySQL 8.0.29 looks like this Name Appointment Bob 2022-06-01 Bob 2022-06-03 John 2022-06-02 I'm trying to get the latest appointment for every person with the names in descending order The query should return Name Appointment John 2022-06-02 Bob 2022-06-03 I've tried SELECT * FROM (SELECT * FROM Table ORDER BY Appointment DESC) AS temp GROUP BY Name ORDER BY Name DESC; But it doesn't return the latest dates for each person Name Appointment John 2022-06-02 Bob 2022-06-01 by SomebodyOnEarth in StackOverflow on July 02, 2022 . Answer You should select the max appointment date for each person: SELECT name, MAX(appointment) FROM mytable GROUP BY name ORDER BY name DESC by Mureinik on July 02, 2022 . Other helpful answers You can use GROUP By SELECT NAme, MAX(Appointment) FROM mytable GROUP B...

I am using mysql. I am trying to join two queries by != condition. For this example it should return empty result set. But it seems the condition is not applied. I am not understanding why. Can anybody please help? My attempts are below: SELECT today_student.* FROM ( SELECT scd.student_id, sc.transaction_date FROM student_collection_details scd INNER JOIN student_collection sc ON (scd.student_collection_id = sc.id) WHERE 1=1 AND sc.transaction_date BETWEEN DATE('2022-06-01 00:00:00') AND DATE('2022-06-27 00:00:00') AND scd.admission_year_id = 2 AND scd.month_id = 21 AND scd.collection_head_id = 9 GROUP BY scd.student_id ) prev_student, ( SELECT scd.student_id, sc.transaction_date FROM student_collection_details scd INNER JOIN student_collection sc ON (scd.student_collection_id = sc.id) WHERE 1=1 AND sc.transaction_date = DATE('2022-06-28 00:00:00') AND scd.admission_year_id = 2 ...

I am looking for the syntax to change the name of salesperson Inga to Annette, but only where Inga's SALES are <50 I have attached a screenshot of the database - I have tried every syntax I can think of, I am positive it will be simple - I have just been stuck on this for a while. by CodeGirl2022 in StackOverflow on June 05, 2022 . Answer You can use UPDATE statement, like this: UPDATE tablename SET SalesPerson="Annette" WHERE SalesPerson="Igna" AND SalesAmount>50; by Vad Sim on June 05, 2022 .

i want to see all the data of a particular table of sql database world entered by user, The below code will give me desired output for city table, but i want table name to be entered by user and wants to make my code work for all the cases. from sqlite3 import connect import mysql.connector mydb=mysql.connector.connect(host="localhost",user="root",password="",database='world') mycursor=mydb.cursor() mycursor.execute("select * from city") for i in mycursor: print(i) I thought to declare city as string variable table_name=str(input("enter table name:")) mycursor=mydb.cursor() mycursor.execute("select * from table_name") for i in mycursor: print(i) But i'm getting an error world.table_name doesn't exist, which i understand. is there any way to evolve at solution of my case. looking for kind help, thanks a lot. by def __init__ in StackOverflow on June 05, 2022 . ...

I am trying to combine columns between two tables and concat them. While I am able to use group_concat on the columns which are specific to just one table but there is also a column for which I need to combine from table1.role_id and table2.role_id and join the result with specific delimiter. I am using sqlite db. SQL Fiddle: link Table1 name role_id dep_id a 1 d1 a 1 d2 b 2 d3 b 3 d5 c 3 d4 Table2 name role_id ipv6 ipv4 a 1 ipv6_1 ipv4_1 a 2 ipv6_2 ipv4_1 b 7 ipv6_3 ipv4_2 b 7 ipv6_7 ipv4_8 e 2 ipv6_4 ipv4_3 The query I have come up with select t1.name, -- combine role_id column from both the tables and do group_concat on it?? group_concat(DISTINCT t1.dep_id), group_concat(DISTINCT t2.ipv6), group_concat(DISTINCT t2.ipv4) from table1 t1 inner join table2 t2 on t1.name = t2.name group by t1.name Current query output name group_conca...

I'd like to add/create three columns from table column based on this particular column results. This is the records I've in a tblexammarks: SELECT exm.ID, exm.Admission_No, exm.Subject AS 'SID', sb.Name AS 'Subject Name', exm.Term, exm.Test, exm.Marks FROM tblexammarks exm INNER JOIN tblsubjects sb ON exm.Subject=sb.ID ORDER BY exm.ID ASC; Output: ID Admission_No SID Subject Name Term Test Marks 1 KBA-2022-003 6 Science Term One Test One 94 2 KBA-2022-003 6 Science Term One Test Two 88 3 KBA-2022-003 6 Science Term One Test Three 78 Here's what I'm trying to achieve: Admission_No SID Subject Name Term Test One Test Two Test Three Total Marks KBA-2022-003 6 Science Term One 94 88 78 260 What I've tried and the output: SELECT exm.ID, exm.Admission_No, exm.Subject AS 'SID', sb.Name AS 'Su...

I am looking to populate a table with some data and normally I would run some insert scripts or upload via a csv file. The requirements I have is that the data can only be populated in the 2nd table as long as the order_id is within the orders table. From what I know I probably need to write some PL/SQL code to check if the order_id exists in the orders table before running the insert scripts but not sure how to write this. I would appreciate it if somebody could get me started. This is the create statement for the Orders Table: CREATE TABLE ORDERS ( ORDER_ID NUMBER NOT NULL, STATUS VARCHAR2(9) NOT NULL, ORDER_DATE DATE NOT NULL, PRIMARY KEY(ORDER_ID), CONSTRAINT CHK_STATUS CHECK (STATUS = 'OPEN' OR STATUS = 'CLOSED') ); The create statement for the 2nd table is: CREATE TABLE ORDER2 ( ORDER_ID NUMBER NOT NULL, PRODUCT_ID NUMBER NOT NULL, ORDER_DATE DATE NOT NULL, PRIMARY KEY(PRODUCT_ID) ); Thanks. by Michael Owen ...