I have 3 related tables: business id name inspection id business_id date violation id inspection_id title I'd like to get out: [business.id, Count(Inspections), Count(Violations)] Where Count(Inspections) is the total number of inspections this business has had and Count(Violations) is the total number of violations...
MySQL Bug #11472 confirms that when rows of a tableB are updated/deleted indirectly as a result of a FK constraint (relating tableA), triggers on tableB are not executed. How can I workaround this bug? I found a messy workaround, but would like to hear the SO communities suggestions as well. In this example joins has a FK to users a...
What's the easiest way to fix this sqlite3 query so that wins and losses show up as columns in a single row instead of in two separate rows? http://0890db80061d7d2b33eb4606a4c301c1.conquerclub.db.94y.info/ I can think of hard ways involving subselects, but surely there's an easy/efficient/beautiful solution here? I want to avoid so...
When I programming with the ScalaQuery, how to build a "select count(*) from table" statement? I used a Query(TestTable.count) but the generated select statement is: select count(*) from (select column1 from TestTable t2) t1 I want the: select count(*) from TestTable sorry for my poor english. import org.scalaque...
I am by no means fluent in MYSQL. What I am trying to do is I have a table that has a list of Galleries with unique ID numbers. I have another table ImageData that has images that may be as many as 1000 that are related to each GalleryID that is in the first table. I want to do a select that will get 5 random records from each Gallery...
Hi, The table consists of columns column_1, column_2, column_3. Given the set of string values (string1, string2,... string 10) I have to create a query to return all rows which contain string1 or string2 or ... or string10 in column_1 or in column_2. I would appreciate if anyone suggested a good way to write an appropriate query stat...
I have a table: points it has 2 filds: X, Y each row represents a dot on 2d field I want to be capable of performing search of dots with in some radius R like and is square with side A like BTW: I use PHP to access my DB. My main point is to get nearest to center of figure point as first result in the quqe like How to do su...
The problem in a nutshell: This is what happens when trying to insert a row with a few null columns using a prepared statement and groovy.sql.Sql: groovy:000> val ===> [123123123, 2, null, 0, 0, , null, , 1213020112511801, 1283425009158952, 1, 2, null, 0, 0, , null, , 1213020112511801, 1283425009158952] groovy:000> destSql.execute "ins...
I've got a table of trades which include fields for amount, tradeDate and settlementDate I want to return a table with the summed unsettled amount field grouped by all dates. For a specific date the query would be: select sum(amount) from trades where tradeDate <= @Date and settlementDate > @Date One way to do this is (simplified)...
I would like to query for all databases (in SQL Server 2008 instance) date when the last transaction log backup has been taken. How to do that? I know that this information is somewhere, but I don't know where. ...
I'm writing a game server to which many players connect. I load up the DB into a DataSet(strong typed). Every time a player logs in, I add a new row to Messages table using a TableAdapter. code var newRow = _db.Messages.NewMessagesRow(); // _db is a strong-typed-dataset { newRow.Title = title; new...
I am looking for software can show SQL scripts (MySQL in my case) in visual way. Example every script is a circle in the diagram. Similar to doxygen but for SQL scripts. ...
My understanding is that GREATEST() and LEAST() are not part of the SQL standard, but are very common. I'm wondering, is there a way to clone the functionality of GREATEST keeping within the SQL standard? SELECT id, GREATEST(1,2,3,4,5,6,7) AS number FROM table The fully query: SELECT SUBSTR(section,1,2) AS campus, AVG(...
I have a simple contact form with name, email, a select list, and textarea. In my mailer php script I'm trying to add a simple filter to prevent SQL injection or other forms of hacking. For example, I'm using $name = filter_input(INPUT_POST, 'name', FILTER_SANITIZE_SPECIAL_CHARS); Is this good? ...
Hello, i try to convert this kind of sql query into linq in visual basic, but i get stuck on how to make a percent.. I also don't know how to use linqpad for creating this linq Please help. SELECT CASE RIGHT(PICName, 3) WHEN '(P)' THEN 'Problem' WHEN '(R)' THEN 'Request' ELSE 'Other' END AS [Reque...
I have a table called "images": CREATE TABLE images ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL UNIQUE, caption TEXT ); On inserting a row, I'd like the URL column to be unique. At the same time, I'd like to find out the id of the row with that URL. INSERT OR IGNORE images (url, caption) VALUES ("h...
When parsing and inserting data, I accidentally inserted a string (ip address) into a bigint column in a MySQL table without using the INET_ATON function. Is there any way I can recover the original string? ...
Hi all I am sure these has been asked before but cannot find clear instruction how to create a batch file lets call it "Update Database" this batch file should Execute sql scripts located in different folders Execute another 3 bat files. Any quick examples how to do it?Never done it before thanks a lot EDITED Can I do this? :On ...
I have two tables: locations and listings. locations id title address latitude longitude listings id location info status SELECT locations.title, locations.address, ( 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians...
Hello, I have a table called RESULTS with this structure : resultid,winner,type And a table called TICKETS with this structure : resultid,ticketid,bet,sum_won,status And I want to show each row from table RESULTS and for each result I want to calculate the totalBet and Sum_won using the values from table TICKETS I tried to make some...