sql

sql select statement giving more rows then records in tables

The error is very simple. There are 3 Tables, Lets name them A, B, C - All of them have 7 rows. A has the following data 1 2 3 4 5 6 7 with Id = 1 B has the following data 8 9 10 11 12 13 14 with Id = 1 C has the following data 15 16 17 18 19 20 21 with Id = 1 Now i am selecting them as : select A.col1,B.col1,C.col1 from...

Make sql where clause prioritize one prefix-like-search above another

I have an sql table with some nvarchar columns, lets call them 'Title' and 'Value'. I currently use a linqtosql query to make a prefix search to find rows in this table, like so var result = from _item in GetTable() where _item.Title.StartsWith( "hello" ) || _item.Value.StartsWith( "hello" ) select _item; return result.Take( 10...

C# SQLBULKCOPY import massive text file to one column, to a specific row

Does anybody have a an example of how to import a huge textfile to a specific row using SQLBULKCOPY and streamreader? Basically need to put all of the text file into one column in a specific row that has a value i am looking for. ...

SQL Selection of products in a single category

So here is what I am trying to do. My boss wants to put all vehicles that we have on our homepage and randomly pull 8 of them at a time. The way our database schema is setup has the products, and categories in separate tables using a cross reference to locate the category the product falls under. The table with the categories has a paren...

using max() and sum()

I need to find the 'name of the branch that has made the most money in 2009'. My tables are below: Rental (cid, copyid, outdate, returndate, cost) Copy (copyid, mid, bid) Branch (bid, bname, baddress) I have written the following code, and it outputs the sum of all branches, but I need the sum of the branch that made the most money...

Why does this MySQL query give "error 1136" when inserting values into table

Query: INSERT INTO Customer2 VALUES (1, 'Mrs','Jill','Hill','2 Step St','Hillington','Uxbridge', 'Middx''UB10 8XY','020 8999 684') ; ...

SQL Server 2005 Get First and Last date for any Month in any Year.

I have a stored procedure that has to accept a month as int (1-12) and a year as int. Given those two values, I have to determine the date range of that month. So I need a datetime variable to represent the first day of that month, and another datetime variable to represent the last day of that month. Is there a fairly easy way to get th...

MySQL Join Multiple Table

News nID nTitle ----------- 1 test Keyword kID kWord nID -------------- 1 abc 1 2 def 1 3 ghj 1 So i fetch it like $sql = mysql_query("SELECT * FROM news as n, keyword as k WHERE n.nID = k.nID"); PHP while($row = mysql_fetch_array($sql))...

SQL Server 2000 Enterprise Manager - Store nvarchar data greater than 4000 characters?

I'm using SQL Server 2000 Enterprise Manager. I'm having trouble with storing some data in a column. The column has a data type of nvarchar, with a length of 4000, which I've now learned is the max length you can have. I need to store data in the column that is longer than 4000 characters...is there a way to increase the size? Or is the...

Adding a type of heading to a query

Hey all, im not sure if this is possible but i am looking to add a heading to a query's output. The best way to describe this is just by showing what i would like the output to look like: [heading here] <skip row> [Another Header here] MemebersID | FirstName | LastName | Email | OrderDate 05466 Bob Barker ...

Generally, are string (or varchar) fields used as join fields?

We have two tables. The first contains a name (varchar) field. The second contains a field that references the name field from the first table. This foreign key in the second table will be repeated for every row associated with that name. Is it generally discouraged to use a varchar/string field as a join between two tables? When is t...

ASP.NET/SQL find the element ID and update database

Basically I've got an update query that runs when you click a submit button. I have an asp:Repeater in my page with a layer inside like this <asp:Repeater id="dgBookings" runat="server" OnItemDataBound="ItemDB"> <itemtemplate> <div class="bookingscontent"> <div class="bookingdetails" id="<%# DataBinder.Eval(Contain...

Help with sql query

Hello, I'm doing an sql query where I want to return a list that will contain the users / groups who will have access to an application. This is the scheme, we will see if a user have permissions, if not, we going to see the group. If both not, we see if there are any permissions on the application, if not, then will add the user to the ...

What's wrong with my SQL query? Syntax errors...

This SQL query worked fine until I tried adding the CASE statement in, but now I get syntax errors as described at the bottom of this post. Here's the SQL (pretty epic, but the syntax errors take place around the CASE, maybe with the SageAccount select prior) SELECT CurrentBalance.Value AS CurrentBalanceValue, Debt30Balance.Valu...

Database Design: replace a boolean column with a timestamp column?

Earlier I have created tables this way: create table workflow ( id number primary key, name varchar2(100 char) not null, is_finished number(1) default 0 not null, date_finished date ); Column is_finished indicates whether the workflow finished or not. Column date_finished is when the workflow was finished. Then I had ...

Recursive SQL query to speed up non-indexed query

This question is largely driven by curiosity, as I do have a working query (it just takes a little longer than I would like). I have a table with 4 million rows. The only index on this table is an auto-increment BigInt ID. The query is looking for distinct values in one of the columns, but only going back 1 day. Unfortunately, the Re...

where like over varchar(500)

I have a query which slows down immensely when i add an addition where part which essentially is just a like lookup on a varchar(500) field where... and (xxxxx.yyyy like '% blahblah %') I've been racking my head but pretty much the query slows down terribly when I add this in. I'm wondering if anyone has suggestions in terms of chan...

MySQL date range SELECT + JOIN query using column with CURRENT_TIMESTAMP

I am using this query: SELECT p.id, count(clicks.ip) FROM `p` LEFT JOIN c clicks ON p.id = clicks.pid WHERE clicks.ip = '111.222.333.444' To select clicks from table "c", that has "pid" = "p.id". The query seems to work fine, but now I want to use that query with date ranges. The "c" table has a column "time" that uses MySQL CURRENT_T...

How should I write this in LINQ?

I have three tables I'm getting info from: User, Field, FieldUserInput. I already know the userId which is what I use to find out what fields the user has input. Then I want to get the value of those fields that the user has input in FieldUserInput. I first wrote this query in plain old sql but I want to use LINQ as much as possible sin...

SQL Distinct Query for Two Tables

Table1: id - name - address ----------------------------- 1 - Jim - Some Street 2 - Adam - Some Street 3 - ABC - Some Street Table2: id - job - finished_by --------------------------- 1 - ABC - 2 2 - EFD - 3 3 - XYZ - 2 4 - BVC - 1 In the above two tables Table1.id an...