sql

How do I limit a LEFT JOIN to the 1st result in SQL Server?

Hello, I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export. The database itself is old and has some integrity issues. My issue is that there should only ever be 1 type of...

Methods of visualizing joins

Just wondering if anyone has any tricks (or tools) they use to visualize joins. You know, you write the perfect query, hit run, and after it's been running for 20 minutes, you realize you've probably created a cartesian join. I sometimes have difficulty visualizing what's going to happen when I add another join statement and wondered i...

Unique constraint within a group of records where some value is the same

DBMS: MS Sql Server 2005, Standard I'd like to make a table constraint to have only one record have a particular value within a subset of the table (where the rows share a value in a particular column). Is this possible? Example: I have records in myTable which have a non-unique foreign key (fk1), and a bit column called isPrimary to ...

SQL 2008 - resultset order issue

We are using SQL Server 2008. We have a table called response which has a primary key called response_id. It also has a column called bid_id. When we execute the query ‘select * from response where bid_id = x’ without an ‘order by’ we are getting results in mostly ascending order (default), but once in a while in descending order (ve...

Using SQL to find the total number of customers with over X orders

Hey all, I've been roasting my brain with my limited SQL knowledge while attempting to come up with a query to run a statistic on my orders database. Table ORDERS is laid out like this: CustomerID ProductID (etc) 1 10 1 10 1 11 2 10 4 9 Each purchase is recorded with t...

complex'ish SQL joins across multiple tables with multiple conditions across all tables

Given the following tables: labels tags_labels |id |name | |url |labelid | |-----|-------| |/a/b |1 | |1 |punk | |/a/c |2 | |2 |ska | |/a/b |3 | |3 |stuff | |/a/z |4 | artists tags |id |name | |url |artistid |albumid ...

Compare 2 fields in 2 tables and extract data

How do i compare 2 fields in 2 tables, find a match and use it ? example: table1: data1(id_data1,name,address,phone,account) table2: data2(id_data2,name2) now in php: if (name2.table2 has a matching name in name.table1) { give me the address,phone,account } ...

SQL/ASP - Expected: " - But it's there?

hey all, can somebody please help me figure this out? It's giving me error after error after error and I have no idea what its problem is. My code: <% Dim cnnSimple ' ADO connection Dim rstSimple ' ADO recordset Set cnnSimple = Server.CreateObject("ADODB.Connection") ' DSNLess cnnSimple.Open "MY CONNECTIONS STRING INFO HERE" cnnSim...

Weakly typed LINQ to Relational Data

Strong typing is fine and dandy when you know what your schema is going to look like at compile time. But what if you're working on an application that lets the user define their own schema? Is there a reliable relational LINQ provider out there that operates at the same level of abstraction as LINQ to XML? Example: var whoIsJohnGalt ...

Trying to use a SQL function in MySQL

I'm trying to use this script (which extracts the domain from a URL): CREATE FUNCTION [dbo].[parseURL] (@strURL varchar(1000)) RETURNS varchar(1000) AS BEGIN IF CHARINDEX('http://',@strURL) > 0 OR CHARINDEX('https://',@strURL) > 0 SELECT @strURL = REPLACE(@strURL,'https://','') SELECT @strURL = REPLACE(@strURL,'http://','') SELECT @str...

Install script MySQL syntax error "You have an error in your SQL syntax; [...] "

I have an install script on my website, and when i run it, it gives me thsi error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE active_guests ( ip varchar(15) collate latin1_general_' at line 2 Here is the code where it seems to b...

MySQL trigger to update value in another table

What i want to is when updating/inserting to table x, increment a value in table y, by 1 example below, using a select statement, which i cant use as im selecting from the table im trying to update DELIMITER $$ create trigger occupancy after insert on tbl_attendence for each row begin set @course_id = new.course_id; set @attend...

What are the best uses of document stores?

I have been hearing a lot about document oriented data stores like CouchDB. I understand the uses of BigTable like stores such as Cassandra. After reading this question, I was wondering what the conditions would be to merit using a document store? ...

How Can I deduct Percentage from amount

Hi I have a situation where given a Column "TotalDue" and a Percentage(17,5%)Could be anything.I need to deduct the percentage and put results in a table CREATE TABLE #ResultTable (NettAmount money, GrossAmount money) For example sake lets take AdventureWorks database they have a totalDue Column on SELECT TotalDue from Sales.SalesOrd...

Replace SQLite with SQL Server?

Hi everyone. Does anyone know if it's good solution to use SQLite in multi-thread environment. I want to replace SQL Server with more simple and built-in database as there is no need to feed such big server DB. The supposed max size of DB would be 4 gigabyte after 4-5 years of usage. Is it normal for built-in DB? Could it affect performa...

sql doesn't wants to leave empty fields in a table empty when i call echo in PHP

Whenever i call a SQL function it fills out everything but empty fields accordingly to id number where id number says which record of name, account it takes. SQL instead fills the empty fields with data from previous id record. How to tell SQL to not fill the empty fields and leave them empty as they are ? ...

Calculate TF-IDF using Sql

Hello, I have a table in my DB containning a free text field column. I would like to know the frequency each word appears over all the rows, or maybe even calc a TF-IDF for all words, where my documents are that field's values per row. Is it possible to calculate this using an Sql Query? if not or there's a simpler way could you pleas...

Calculating total time duration in MySQL

i have a table data like this (int) (time) [mySQL datatype] Id Duration ------------------ 1 00:10:00 2 10:10:00 3 03:00:00 4 04:13:00 i want to calculate the total duration from this table, how can we do it. like 17:33:00. Could anybody please provide mysql query to calculate this. ...

SQL: how do i order by a field if its not null else use another field

i want to sort my Posts by dtModified (datetime modified) if its not null (post modified b4), else sort by dtPosted (datetime posted) ...

Why difference in performance between the TWO queries?

I am using SQL Server 2008, and I have two tables Table1 contains 3.5 million records +----+-------------+ | pk | dim1 | +----+-------------+ indexing applied on column **pk** Table2 contains 15 million records +----+-------------+ | fk | fact1 | +----+-------------+ indexing applied on column **fk** I ran 2 querie...