Search between a Gas Station

Sql Triggers-Implementation Issue Hi guys i am making 2 tables for implementing triggers-- create table emp(id int primary key identity(1,1), name char(40), salary varchar(50), gender char(40), departmentid int); insert into emp(name,salary,gender,departmentid) values ('jimmi',4800,'Male',4); create table emp_audit ( id int primary key identity(1,1), audit varchar(60) ); alter trigger trigger_em_update on emp for update as begin Declare @Id int Declare @oldname char(40),@newname char(40) Declare @oldsal int,@newsal int Declare @oldgen char(40),@newgen char(40) Declare @olddeptid int,@newdeptid int Declare @auditstring nvarchar(max) --select * from deleted; select * into #temptable from inserted; while(Exists(select id from #temptable)) --boolean condition if there are rows are not Begin set @auditstring ='' --if there are many rows we still select the first one select Top 1 @Id =id,@newname=name,@newgen=gender,@newsal=salary,@newdeptid=departmentid ...

Query string properties stored as XML I am using Entity Framework to query a db which is defined by a model: inside this model I have several classes having a #region dynamic values : #region dynamic values [DataContract] public class Job : AbstractEntity, IJob { [DataMember] public virtual Guid Id { get; set; } ... #region dynamic values [DataMember] public virtual string MetadataValue { get; set; } [DataMember] public virtual string ParametersValue { get; set; } [DataMember] public virtual string AttributesValue { get; set; } #endregion #region links ... #endregion } AttributesValue , MetadataValue and ParametersValue are declared as string but are stored inside the db as XML documents. I am aware that this is not consistent with the model and should be changed, but for some reasons it has been managed this way and I am not allowed to modify it. I have created a Unit Test in order to better handle the problem, and here is the ...

SQL-EX.ru Exercise #36 I am trying to solve exercise #36 which is List the names of head ships in the database (taking into account Outcomes table) . Head ships are the names of ships that were first in line for that class (i.e. 2 ships but 1 build before another so the first ship is the "head ship"). My result says I did says the following: Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database. * Wrong number of records (less by 1) My query is as follows: SELECT A.name FROM Ships A WHERE A.name = A.class AND A.launched = (SELECT MIN(B.launched) FROM Ships B WHERE A.name = B.name AND A.class = B.class) UNION ALL SELECT A.ship FROM Outcomes A WHERE A.ship IN (SELECT C.ship FROM Outcomes C, Classes D WHERE C.ship IN (SELECT ship FROM Outcomes) AND C.ship NOT IN (SELECT name FROM Ships) AND C.ship = D.class ...

Azure SQL DB causing connection time out for stored procedures We have hosted our database in Azure and are running stored procedures on this DB. The stored procedures had been running fine till last week but suddenly started giving error connection timeout. Our database size is 14 GB and the stored procedures in general return 2k to 20k records and we are using the S3 pricing tier (50 DTU) of Azure DB. What I found interesting was the first time the stored procedure is executed, it takes a lot of time 2 - 3 mins and this is causing the timeout. The later executions are fast (maybe it caches the execution plan). Also when I run on the same DB with the same number of records on a machine with the config of 8gb ram, Win10 it runs in 15 seconds . This is my stored procedure: CREATE PROCEDURE [dbo].[PRSP] @CompanyID INT, @fromDate DATETIME, @toDate DATETIME, @ListMailboxId as MailboxIds Readonly, @ListConversationType as ConversationTypes Readonly AS BEGIN ...

Combining “like” rows I have a table of locations_from, locations_to, and count of flights. I want to combine the rows where departure on one row equals arrival on the other (for example LA TO NY combined with NY TO LA) and SUM the like rows. I think it would be better explained with an example. BEFORE locations_from locations_to # of Flights -------------------------------------------------- San Francisco, CA Los Angeles, CA 29558 Los Angeles, CA San Francisco, CA 32389 New York, NY Los Angeles, CA 30389 Los Angeles, CA New York, NY 35484 Las Vegas, NV Los Angeles, CA 28363 Los Angeles, CA Las Vegas, NV 34455 Honolulu, HI Kahului, HI 46563 Kahului, HI Honolulu, HI 16879 San Francisco, CA New York, NY 44654 New York, NY San Francisco, CA 25882 AFTER From/To From/To # of Flights --------------------------------------------------- San Francisco, CA ...

Assistance with SQL Query to break down bill of materials I am try to breakdown a BOM into its lowest items. I have got 2 tables. Table A: ITEM FORMULA -------------------- 7024 BDD39 7024 BDD94 Table B FORMULA RAW_ITEMS --------------------------------------- BDD94 BioxDD 94 RMW0005 BDD94 BioxDD 94 RMP0007 BDD94 BioxDD 94 RMD0010 BDD94 BioxDD 94 RMH0009 BDD39 BioxDD 39 RMA0005 BDD39 BioxDD 39 RMW0006 BDD39 BioxDD 39 RMS0005 Is there any hint as to how to go about to replace the item in Table A with all the 3 items in Table B? My expected output is: Item ----------------------------------------- 7024 BDD39 BioxDD 39 RMA0005 7024 BDD39 BioxDD 39 RMW00...

How to access db in phpmyadmin in xampp [on hold] I downloaded the madrasa management software from Google. And I extracted this file and copied it in htdocs in xampp. Then I imported the madrasa management software backup file in phpmyadmin in xampp. Import has successfully completed. But I can't access the database in localhost. So please guide me on how to access this software. This question appears to be off-topic. The users who voted to close gave these specific reasons: Please read stackoverflow.com/help/how-to-ask – Franck Gamess Jul 1 at 6:52 Try to elaborate more and show which error you're facing. stackoverflow.com/questions/30255567/… – Drashti Pandya Jul 1 at 6:56 ...

MDX Scope Total Calculation with condition I recently encounter an issue with using scope to calculate grand total. Example I've create a measure : CREATE MEMBER CURRENTCUBE.[Measures].[test Invoice 2 Amt] AS IIF(ISEMPTY([Measures].[Amount]), NULL, [Measures].[SalesInvoiceLine2_Amt]), FORMAT_STRING = "#,#.00", VISIBLE = 1; This measure to ensure amount2 (SalesInvoiceLine2_Amt) only appear when another measure (Amount) have value. But now the issue is my grand total for Amount2 doesn't total based on above condition, instead it sum up all amount2. I've try to use scope but seems doesnt work as well. CREATE MEMBER CURRENTCUBE.[Measures].[test Invoice 2 Amt] AS IIF(ISEMPTY([Measures].[Amount]), NULL, [Measures].[SalesInvoiceLine2_Amt]), FORMAT_STRING = "#,#.00", VISIBLE = 1; SCOPE ( [Measures].[test Invoice 2 Amt] ); THIS = SUM(IIF(ISEMPTY([Measures].[Amount]), NULL, [Measures].[SalesInvoiceLine2_Amt])) ; End Scope; [Item] [Amount] [test Invoice 2 Amt] [...

SQL - query tables related in two different ways Imagine I have a MySQL database for managing a library, and I want to know about all members that have either borrowed or reserved it. I am looking for a single query, sorted by book ID. The tables are book, borrow, reserve and member. Both the borrow and reserve tables contain a book ID and member ID. I only want one unique pair of book ID and member ID in the result. This means these cases need to be deduplicated a member reserved a book and borrowed it also a member borrowed or reserved the book multiple times Can anyone help with this? please show us sample table structure with data in it. – Mohit Kumar Jul 1 at 4:03 Will make an SqlFiddle – Ben E. Jul 1 at 4:20 ...

MySQL dynamic where clause with security focus I want to make a fully dynamic where clause as viewset. The where clause is fully implemented by the user and stored in a table. To get this working is easy but if have alook at security with this solution it's a big problem. This should be implemented as a stored procedure I have 2 tables: Table: Viewset (where clause stored): ID | Viewset<br> 1 | (a > 5) OR REGEX(...) etc. And a datatable. In the stored procedure it's working like this: This is ofc, fully vulnerable for SQL injections etc. Now I want to get this secure. I had 2 ideas. First: Just try to get any possible 'AND' and 'OR' connection and using a prepared statement at the end => Problem: I have around 100 possible combinations. Second idea: where clause through a whitelist of words and the values through prepared statements. Problem again: MySQL doesn't allow arrays and string splits etc. that makes this very hard to implement. Maybe th...

What is CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION…) I came across an odd bit of legacy code while going back through old code for cleanup, and I'm trying to pin down exactly what it does... CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) as b It's a technique I haven't seen before, including multiple union selects of numbers inside a cross join, and I'm honestly really confused about what it's doing and why someone would do it. Possible duplicate of SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN? – Alessandro Da Rugna Jun 30 at 23:31 1 Answer 1 Cross join creates a Cartesian product. Cross join Assuming the from clause looks like: from FROM t ...

Electron app with database I'm creating a web app for ticket reservation. The only problem is the database. I don't want to tell my client to install XAMPP or set a database, etc. Is there any way to package the app with the database? sqlite might be an option – David784 Jun 30 at 23:21 1 Answer 1 SQLite is a good option for a local database that you can ship with your application. It won't require the user to setup or install any extra dependencies. It's certainly not as full featured as a full on server, but it should be good enough for a local desktop app. By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie polic...

How can I Count in multiple table? I have some problem to get the ID which use on another table row. I have three tables. One is category, articles, video. In articles and video there is a column which have category ID. This is the example: Table Categories : id | category_name ------------------ 1 | News 2 | Sports 3 | Art 4 | Horror Table Articles : id | category_id | title ---------------------------------- 1 | 1 | title content 1 2 | 1 | title content 2 3 | 3 | title content 3 4 | 3 | title content 4 5 | 2 | title content 5 Table Video : id | category_id | video_title ------------------------------ 1 | 1 | video title 1 2 | 2 | video title 2 3 | 3 | video title 3 I want to get each category ID already use what time in two other databases. Like this : Category ID 1 is use 3 times Category ID 2 is use 2 times Category ID 3 is use 3 times Category ID 4 is use 0 times What query do I need to use...

Randomly generate True or False on BigQuery I am trying to randomly generate a boolean (true or false) for all the rows of one BQ table and insert into another table along with the boolean column. I am right now doing the following: #standardSQL select (case when rand() > 0.5 then True else False end) as A I am not sure how to generate this for every row: Table 1 Name XXX YYY ZZZ Now I want to generate True or False randomly for each name and insert it into Table 2 which looks like the following: Table 2 Name | True_or_False XXX | True YYY | True ZZZ | False Any pointers will be helpful. 2 Answers 2 Below is for BigQuery Standard SQL - assuming table2 already exists #standardSQL INSERT `project.dataset.table2` (Name, True_or_False) SELECT Name, RAND() > 0.5 True_or_False FROM `project.dataset.table1` Just add the logic to a select: select t.*, (rand() < 0.5) as flag from table1 t; ...

Transaction (Process ID) was deadlocked errors I am having an issue with my code and the numerous SQL calls that it makes with deadlocks. I pasted the code into PasteBin here: https://pastebin.com/p1YDkKsB. Can someone help me out here? It happens most often in the CheckClanActivity task here: using(SqlCommand cmd = new SqlCommand(string.Format("select * from ClanMembers where MembershipId={0} and IsActive = 1", entry.Player.DestinyUserInfo.MembershipId), conn)) , but it also happens all over the place. Edit: Okay, the involved SQL statements are as follows: if not exists(select * from ClanMembers where MembershipId={0}) begin insert into ClanMembers(ID, MembershipId, BattleNetId, ClanId, DateLastPlayed, IsActive, LastUpdated) select ISNULL(MAX(ID) + 1, 0),{0},'{1}',{2},'{3}', 1, GETDATE() from ClanMembers end else begin update ClanMembers set DateLastPlayed='{3}', LastUpdated=GETDATE() where MembershipId={0} end if not exists(select * from ClanMemberC...

What difference it makes if I use OR statements instead of IN in SQL What difference it makes if I use, winner IN ('Subject1','Subject2'); & winner='Subject1' OR winner='Subject2'; winner IN ('Subject1','Subject2'); winner='Subject1' OR winner='Subject2'; Queries for the table 17 in the below link: https://www.w3resource.com/sql-exercises/sql-retrieve-from-table.php#SQLEDITOR The expression reads different. Other than that: None. – sticky bit Jun 30 at 18:23 I am not familiar with MySQL, but with IN you can probably use a table, e.g. winner IN (SELECT columnX FROM tableY WHERE somePredicate) . – Andrew Morton Jun 30 at 18:34 wi...

Returning multiple records using first/earliest date This is a simple concept but the LINQ/SQL solution has me stumped! In the example data below, for each ID I need to retrieve one ISRC , taking the earliest dated one for each. ISRC The data is sorted by ID/ReleaseDate/ISRC , so I could read the first/top record for each ID. ie ID 1 = GBMNA1600001 , ID 2 = GBMNA1600002 , ID 3 = GBMNA1600003 , ID 20 = GBMNA1680058 ... ID/ReleaseDate/ISRC ID ProductID ISRC ReleaseDate 1 16 GBMNA1600001 2016-03-27 00:00:00.0000000 1 26 GBMNA1680038 2016-04-24 00:00:00.0000000 1 32 GBMNA1680057 2016-05-01 00:00:00.0000000 1 132 GBMNA1680482 2016-11-13 00:00:00.0000000 1 223 GBMNA1781107 2017-03-26 00:00:00.0000000 2 5 GBMNA1600002 2016-02-14 00:00:00.0000000 2 32 GBMNA1680049 2016-05-01 00:00:00.0000000 3 13 GBMNA1600003 2016-03-13 00:00:00.0000000 3 38 GBMNA1680095 2016-05-29 00:00:00.0000000 3 485 GBMNA1880099 2018-06-26 00:00:00.0000000 20...