sql

JOINS in MYSQL and missing rows

I have a query like this one : SELECT DISTINCT `a`.*, `b`.*, `c`.* FROM `a` INNER JOIN `b` ON (`b`.`a_id` = `a`.`id` ) INNER JOIN `c` ON (`c`.`id` = `b`.`c_id`) WHERE ( (`a`.`id` = 12345) AND (`b`.`foo`= "bar") ) Basically, this takes the row from a with id=12345, and the rows from b that ...

run sql query from two tables with where clause from one table

I want to get records from one table based on a record from another table. They both have SSN fields and I want to link them by SSN. Here is a rough example what I want to get: SELECT SUM(Table1.Payments) FROM Table1 WHERE Table1.SSN = Table2.SSN AND Table2.City = 'New York' I want to get the sum of the payments by a variable...

Help with asp login SQL

I have a form which goes to the following login script when it is submitted. <% Dim myConnection As System.Data.SqlClient.SqlConnection Dim myCommand As System.Data.SqlClient.SqlCommand Dim requestName As String Dim requestPass As String requestName = Request.Form("userName") requestPass = Request.Form("userPass"...

stored proc recursion in SQL Server

I have a situation where I want to have a stored proc returning a table that calls itself recursively as part of its calculation. Unfortunately SQL Server is having none of this and gives me an error along the lines of both being unable to declare a cursor that already exists and about not being able to nest and insert exec statement....

BC3016: Variable 'myConnection' hides a variable in an enclosing block.

I'm am not quite sure why I am getting this error. Dim numUsers as Integer Using myConnection as New System.Data.SqlClient.SqlConnection("Data Source=(local);InitialCatalog=dbtest;Integrated Security=True") Dim queryString As String = "SELECT COUNT(*) AS Num_Of_User FROM tblusers WHERE username=@username AND password=@password" Us...

two triggers on insert of same table

Here is one very interesting problem. I am using SQL Server 2008. I have two triggers on one common table say 'CommonTable'. one trigger is on update and other one is on insert/update/delete. In first trigger "Trigger1", I do the checks/rollback sometime change the new inserted value based on business logic. here is sample code - CR...

MS Access Pass Through Query find duplicates using multiple tables

I'm trying to find all coverage_set_id with more than one benefit_id attached summary_attribute (value=2004687). The query seems to be working fine without the GROUP BY & HAVING parts, but once I add those lines in (for the COUNT) my results are incorrect. Just trying to get duplicate coverage_set_id. Pass-Through Query via OBDC datab...

Sending Email to Multiple Recipients from MySql Recordset

I want to send an email to multiple recipients using PHP mail() function. The email message is simply a reminder that a membership is due to expire so email addresses will come from MySql database query. There would anywhere from 2-10 at any given time. I found the following code but it generate errors. The problem is not with my que...

modify sql function, delete first character in string

Hi, I want to modify the following "extract numbers" function below to check if the first number in the output is "1", if so, delete the the "1" before displaying the output. THanks! Ex. Input: QW 1 RT 309 Output: 309 create function [dbo].[ExtractNumbers](@Numbers nvarchar(2000)) returns nvarchar(2000) as BEGIN declare @NonN...

Insert record only if record does not already exist in table

I'm wondering if there is a way to insert a record into a table only if the table does not already contain that record? Is there a query that will do this, or will I need a stored procedure? ...

SQLite for Android custom table view (SQL VIEW, not Android view) discrepancy?

I've created a custom view in an SQLite database for an Android application. I'm using Sqliteman on Ubuntu to test my SQL statements before I put them in my app. I'm trying to do a simple select statement on my view. The select statement works fine in SQLiteman but when I put the same statement in my code it throws an error. The state...

MySQL syntax problem in "SELECT * FROM INTO FILE"

I'm trying to move tables between two databases and I'm using this command that is given by MySQL : SELECT * INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table; As it is written in the MySQL Dev Manual. I'm using MySQL 5.1. Error : Error Code:...

SQL based data diff: longest common subsequence

I'm looking for research papers or writings in applying Longest Common Subsquence algorithm to SQL tables for obtaining a data diff view. Other sugestions on how to resolve a table diff problem are also welcomed. The challenge being that SQL tables have this nasty habit of geting rather BIG and applying straightforward algorithms designe...

Easy SQL Syntax NOT LIKE with AND operators seem to be ignored

Thank you so much for helping! Nothing I seem to do works here. What I want to do is remove rows with a certain value in a certain column. Like so: Where SegStart_Date between getdate()-90 and getdate()-1 And q.Center not like 'Collections Center' And q.Center not like 'Cable Store' And q.Center not like 'Business Services C...

Concatenated range descriptions in MySQL

I have data in a table looking like this: +---+----+ | a | b | +---+----+ | a | 1 | | a | 2 | | a | 4 | | a | 5 | | b | 1 | | b | 3 | | b | 5 | | c | 5 | | c | 4 | | c | 3 | | c | 2 | | c | 1 | +---+----+ I'd like to produce a SQL query which outputs data like this: +---+-----------+ | a | 1-2, 4-5 | | b | 1,3,5 | |...

SQl: Group by month and year

Hi my code doesn't work, Im trying to group my blogentries by year and month here's my sql SELECT * FROM( SELECT WP_BlogEntries.BlogEntryID, WP_BlogEntries.AddedDate, WP_BlogEntries.AddedBy, WP_BlogEntries.BlogID, WP_BlogEntries.Title, WP_BlogEntries.Description, WP_BlogEntries.Body, WP_BlogEntries.ReleaseDa...

User Messages Database Schema?

This is probably very subjective and I have my own thoughts on this, but how would one go about constructing a database that supports user to user messages (one to one or one to many). My original thought was to have a MESSAGE table and MESSAGE_PARTIES table MESSAGE would include the following fields: -ID, Subject, Body, Create...

CREATE Trigger help Oracle

I'm trying to create a trigger that will do the following. After insert on Table A, query Table B based on an id (TableA.id=TableB.id) and insert corresponding info into TableA I have a feeling I'm way off so far so any help would be appreciated CREATE OR REPLACE TRIGGER myTrig AFTER INSERT ON TABLEA BEGIN INSERT INTO TABLEA SELEC...

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

I am getting following error when i am trying to connect Production DB from Local Environment. I was able to connect Production DB before, but suddenly i am getting following error, any idea? A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, er...

SQL query producing duplicate rows and I can't see why

My query always produces duplicate results. How best do I go about troubleshooting this query with a database > 1 million rows. Select segstart ,segment ,callid ,Interval ,dialed_num ,FiscalMonthYear ,SegStart_Date ,row_date ,Name ,Xferto ,TransferType ,Agent ,Sup ,Manager ,'MyCent...