SELECT cities.cityname, states.statename, countryname FROM cities JOIN states ON cities.states_id = states.id JOIN country ON states.country_id = country.id WHERE cities.id=3; Assuming 3 is the id of the city you are searching for… solved I need a sql query to view data
use a recursive cte that starts with elements whose frompoint matches no topoint in a self join so as to identify the starting points. then eleminate the intermediate results by joining again and use only those data whose topoint match no frompoint in another self join. with cte as ( select r.name, r.frompoint, r.topoint from … Read more
This can be done with a single update statement. delete from question where id = 2; with new_order as ( select row_number() over (partition by survey_id order by question_no) as new_question_no, question_no as old_question_no, id from question ) update question set question_no = nq.new_question_no from new_order nq where nq.id = question.id and survey_id = 44; … Read more
select distinct pt.tagname from post_tag pt where not exists ( select * from post_category pc where pc.post_id = pt.post_id ) solved SQL Inner Join not exist
Try this Query:- SELECT CMPI_PRCINX AS CURRENT_PRICE, (SELECT MAX(CMPI_PRCINX) FROM CMD_MTRL_PRICE_INF WHERE CMPI_PRCINX<(SELECT MAX(CMPI_PRCINX) FROM CMD_MTRL_PRICE_INF)) AS PRIVIOUS_PRICE FROM CMD_MTRL_PRICE_INF WHERE CMPI_PRCINX = (SELECT MAX(CMPI_PRCINX) FROM CMD_MTRL_PRICE_INF GROUP BY CMI_CODE) here is the SQL Fiddle Code. http://sqlfiddle.com/#!4/46a23/2 solved getting previous data (one less running column)
I would try with this : SELECT [CaseID], STUFF( (SELECT CONCAT(‘; ‘, A.[AssignedPathologist]) FROM CTE1 A WHERE A.[CaseID] = B.[CaseID] FOR XML PATH(”) ),1, 1, ” ) As [AssignedPathologist] FROM (SELECT DISTINCT CaseID CTE1 B) B; For newer versions you can use string_agg() : SELECT CASEID, STRING_AGG(AssignedPathologist, ‘; ‘) AS AssignedPathologist FROM CTE1 C1 GROUP … Read more
just set the unique value with the original value, (Assuming ProductID is unique) eg, INSERT INTO CART (ProductID, Quantity) VALUES (1, 100) ON DUPLICATE KEY UPDATE ProductID = ProductID; solved Update nothing on duplicate key exist [closed]
With a comma between the columns… SELECT date, isValid FROM yourTable ORDER BY isValid, date 5 solved SQL – How to order by two columns [closed]
Your last query SELECT 0, department, department_name, ”, ‘NP’ as acc_code, ‘NET PROFIT/(LOSS)’ as acc_name,1, SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*yr_balance ELSE yr_balance END)), SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*ly_balance ELSE ly_balance END)), SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*mn_balance ELSE mn_balance END)), SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*lm_balance ELSE lm_balance END)), … Read more
You have a User table with a User ID as the primary key. You have a Step table with a Step ID as the primary key. Something like this. Step —- Step ID Step Name Then you have a junction table that captures the many to many relationship between users and steps. UserStep ——– UserStep … Read more
I think it is better store it in a database’s table. If you store it in a enum, when you add, change or delete a fruit you have to compile your code again. In case of change, you will have to update all places (tables columns) where this fruit’s name is store. In the database … Read more
Your syntax suggests SQL Server. In that database, you would phrase the query as: select firstname + ‘ ‘ + lastname as fullname, birthdate from student where len(firstname) between 6 and 9 and len(lastname) > len(firstname) 0 solved Name and birthdate for all the students whose first name is between 6 and 9 characters long
You can take advantage of SQL Server’s flexibility to recognize various date formats. If you have the right regional settings, this should just work: cast(mycol as date) This gives you back a value of date datetype that corresponds to the input string; Demo on DB Fiddle: select cast(‘Jan 18 2019 12:00AM’ as date) | (No … Read more
add an order by clause –at the end of the query order by ChatDate, ChatTime 0 solved Sql Server sorting query result [closed]
The query in the parenthesis is called a correlated sub-query. That is because, if you look at the first FROM, it has an alias for the salary table FROM salary a, which in this case is a. The sub-query references the outer query in the WHERE b.salary > a.salary condition, where it checks the condition … Read more