sql

calculate rank in highscore from 2 tables

i have a trivia game and i want to reward users for 2 events: 1) answering correctly 2) sending a question to the questions pool i want to query for score and rank of a specific player and i use this query: SELECT (correct*10+sent*30) AS score, @rank:=@rank+1 AS rank FROM ( trivia_players JOIN ( SELECT COUNT(*) AS sent,...

MySQL bulk value insert

I have a bunch of emails, each separated by a comma (,) (not csv). I want to upload them to a database table (with single field email) such that each email goes into separate record entry. what could be the most easiest way to do that? I have an idea of using grep to replace commas with my sql syntax.. but searching for any other workaro...

How to list custom types using Postgres information_schema

I am trying to find the equivalent SQL of \dT using the information_schema and can't seem to find anything. Does such a thing exist? Example: If I add the following custom type enum, how can I see it in the information_schema? CREATE TYPE communication.channels AS ENUM ('text_message', 'email', 'phone_call', 'broadcast'...

SQL Server LIKE containing bracket characters

Using SQL Server 2008. I have a table with the following column: sampleData (nvarchar(max)) The value for this column in some of these rows are lists formatted as follows: ["value1","value2","value3"] I'm trying to write a simple query that will return all rows with lists formatted like this, by just detecting the opening bracket....

Easiest way to duplicate rows in a table, subtable, and subsubtable

I am implementing a "Save As Copy" function for a small web app that uses MySQL. Let's say I have three tables, like so... TABLE Doc ID, title, text TABLE DocAttributes ID, DocID -> Doc(ID) title, text TABLE DocSubAttributes DocAttrID -> DocAttributes(ID) title, text What we have here is a situation where a single Documen...

How to build Excel macro with date range

Hi, I hv created a From Date & To Date in excel (Sheet1). Under cell A3, I hv From Date :: 01-01-2010 and cell A4 To Date :: 31-08-2010. How do I link the Date Range to the below macro:- Sub Sales() Dim StrSQl As String Con = "Provider=IBMDA400;Data Source=XXX.XXX.XXX.XXX;User Id=yyyy;Password=zzzz" Set Db = CreateObject("ADODB.Con...

Efficient retrieval of overlapping IP range records via a single point.

I have a table with millions of IP range records (start_num, end_num respectively) which I need to query via a single IP address in order to return all ranges which overlap that point. The query is essentially: SELECT start_num , end_num , other_data_col FROM ip_ranges WHERE :query_ip BETWEEN start_num and end_num; Th...

Is it possible to use sql server bulk insert without a file?

Curious if this is possible: The app server and db server live in different places (obviously). The app server currently generates a file for use with sql server bulk insert. This requires both the DB and the app server to be able to see the location, and it makes configuration more difficult in different environments. What I'd like to...

len of varbinary

hello, would someone please explain why select len(0x0a000b) returns 3? does len count bytes? and why select left(0x0a000b, 1) returns nothing? i expected 0x0a (if len counts bytes)... i am using mssql 2005 thanks konstantin ...

Write stored procedure parameters to an XML Column

I have a need to take the parameters passed in to a Stored Procedure (SQL 2005) and write those values into an XML column as one xml document. Looking for an idea on how to start it. ...

TSQL Comparing two Sets

When two sets are given s1 ={ a,b,c,d} s2={b,c,d,a} (i.e) TableA Item a b c d TableB Item b c d a How to write Sql query to display "Elements in tableA and tableB are equal". [Without using SP or UDF] Output Elements in TableA and TableB contains identical sets ...

Sophisticated JPQL String Query

I am trying to execute a pretty-sophisticated query on a string field in the database. I am not very experienced at JPQL, so I thought I would try to get some help. I have a field in the database called FILE_PATH. Within the FILE_PATH field, there will be values such as: 'C:\temp\files\filename.txt' 'file:\\\C:\testing\testfolder\inn...

SQL Stored Procedures failing to return values

I am working on a Tag system for a news page designed in ASP.NET. For the system I require a TagExists method to check for tags within the database. The stored procedure I have written is below. ALTER PROCEDURE [dbo].[Tags_TagExists]( @Tag varchar(50)) AS BEGIN If (EXISTS(SELECT * FROM dbo.Tags WHERE LOWER(@Tag) = LOWER(Tag))) ...

In MySQL, ' results in error, ` works fine. why?

$query = "SELECT * FROM `users` WHERE `username` = 'admin'";#works $query = "SELECT * FROM 'users' WHERE 'username' = 'admin'";#does not work Is this yet another quirk Im going to have to get used to, or is something funny going on? ...

Pattern for searching entire DB record, not specific field

More and more, I'm seeing searches that not only find a substring in a specific column, but they appear to search in all columns. An example is in Amazon, where you can search for "Arnold" and it finds both the movie Running Man starring Arnold Schwarzeneggar, and the Gund toy Arnold the Snoring Pig. I don't know what the term is for t...

SQL only want to return a single row for each note

I have the following tables: Orders, Notes, Permits The following columns are in each table: Orders = ID Notes = ID, RelatedID, Note, Timestamp Permits = ID, OrderId I have the following query SELECT o.id , op.id , n.timestamp FROM [tblOrders] o INNER JOIN [tblNotes] n ON n.[RelatedID] = o.[ID] INNER JOIN [tblPermits] op...

SQL Server 2005: Insert missing records in table that is in another reference table

Hi to all, I need help with the following. I have 2 tables. The first holds data captured by client. example. [Data] Table PersonId Visit Tested Done 01 Day 1 Eyes Yes 01 Day 1 Ears Yes 01 Day 2 Eyes Yes 01 Day 3 Eyes Yes 02 ...

write stored proc for the two queries, truncating and inserting two different tables.

Hi All, I have two queries below. how do i put them in one stored proc. both the queries are truncating and inserting records in two different tables. QUERY 1 truncate table [PlanFinder].[InvalidAwps] go INSERT INTO [PlanFinder].[InvalidAwps] (Ndc, AwpUnitCost) SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost FROM PlanFi...

How do I get the position of the match in a FORMSOF INFLECTIONAL full-text search?

I'm using a CONTAINSTABLE query with MS SQL Server's full-text indexing engine to search within a textual column; e.g.: SELECT * FROM MyTable INNER MERGE JOIN CONTAINSTABLE(MyTable, sDescription, 'FORMSOF(INFLECTIONAL, "brains")') AS TBL1 ON TBL1.[key]=MyTable.ixKey This does a great job of finding rows with a description includi...

SQL Server value help

Hey all, is there any SQL Server 2005 guru that can tell me a type of trick to get the values of what is being queried? Ex: UPDATE l SET Inactive = 1 FROM tbl1 e JOIN tbl2 l ON l.CID = e.CID JOIN tbl3 p ON p.PID = e.PID JOIN tbl4 c ON c.PID = e.PID LEFT JOIN tbl5 g ON g.EID = e.ID AND g.PID = e.PID WHERE e....