sql

condense logic down to one Sql query

first is the disclaimer, I did not design the database I am now supporting and I would not have designed it this way, but here I am. Problem: My client has a table in his database that contains listings for rentals. In this table is a text field to store the city of the listing. What my client is hoping to see is a dropdown list of all ...

Subquery and selecting oldest rows for multiple foreign keys in MySQL

I have two tables: product (idproduct, name, description, tax) product_storage (idstorage, idproduct, added, quantity, price) for each product it could be different price and oldest are "first-to-sell" for example in storage I have: 1, 1, 2010-01-01, 0, 10.0 2, 1, 2010-01-02, 0, 11.0 3, 1, 2010-01-03, 10, 12.0 4, 2, 2010-01-04, ...

Placing variable number of variables into fields in table

I am trying to make a generic notInList function that is called when the user types a value in a combobox that is not part of the list. In middle of that function, I want to insert the new value(s) into a table. For some of the combo-boxes, more than one field has to be filled out in the table. (The user is asked a follow-up question ...

Problem with simple select query to find dates in SQL Server

I use simple query to get dates of contract for a person: SELECT [KlienciUmowyDataPoczatkowa], IsNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa' FROM [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy] WHERE [PortfelID] = 3 This is what I get: KlienciUmowyDataPoczatkowa KlienciUmowyDataKoncowa 2005-11-...

Removing string duplication in a SQL Server 2008 database.

Background: Professional tools developer. SQL/DB amateur. Setup: .Net 3.5 winforms app talking to MS SQL Server 2008. Scenario: I am populating a database with information extracted from a large quantity of files. This amounts to about 60M records, each of which has an arbitrarily sized message associated with it. My original plan w...

Select the same column multiple times

Hello, hope someone can help. I have two tables: Users -UserID -UserName UsersType -UserTypeID -UserID Possible values for UsersTypeID is 1 to 6. In that scenario Users may have multiple types and I need to retrieve a distinct row for each user with the columns described below. UserName - Type1 - Type2 - Type3 - Type4 Joe ...

How to delete rows from tables which is referenced each other?

I want to do this: delete from table1 a,table2 b, table3 c where a.col1 = b.col1 and b.col2 = c.col2 and a.co3 <> 8001; But its giving me an error. ...

Can you change the ConnectionString configuration value at runtime?

Is it possible to change the ConnectionString value in a app.config at runtime? According to the MSDN documentation it should be possible as the ConnectionString property "Gets or sets the connection string." My code looks like this: ConnectionStringSettings mainConnection = ConfigurationManager.ConnectionStrings["mainConnection"]; mai...

Column has a number in it, very few rows have text mixed in, can I create a new number column?

I have a table that has a column that has the cost of a product, but some (very few) rows have text mixed in the column. Would it be possible to create a new column, of a decimal type, and populate it with the other column and just ignore the rows with the 'bad' data in it? I know I can do this via code, but was hoping I could do it wi...

php/mysql zip code proximity search

Hi, I'm just looking for suggestions on the best way to do this... I need to create a search function that searches for "users" within a 50 mile radius of a zip code. I have a zip code table that contains all the U.S. zip codes with their latitude/longitude but I'm just trying to figure out the best way to structure and query my data....

Primary Key / Clustered key for Junction Tables

Let's say we have a Product table, and Order table and a (junction table) ProductOrder. ProductOrder will have an ProductID and an OrderID. In most of our systems these tables also have an autonumber column called ID. What is the best practice for placing the primary key (and therefor clustered key)? Should I keep the primary key o...

Add date to SQL database backup filename

I'm using the below to backup a db from a SQL job. Can someone tell me how to add the current date to the output filename? Preferably in YYYYMMDD format. BACKUP DATABASE [myDB] TO  DISK = N'\\myPath\myDB.bak' WITH NOFORMAT, INIT,  NAME = N'myDB', SKIP, REWIND, NOUNLOAD,  STATS = 10 GO Thanks! ...

How to Reset an MySQL AutoIncrement using a MAX value from another table?

I know this won't work, tried it in various forms and failed all times. What is the simplest way to achieve the following result? ALTER TABLE XYZ AUTO_INCREMENT = (select max(ID) from ABC); This is great for automation projects. Thank you! SELECT @max := (max(ID)+1) from ABC; -> This works! select ID from ABC where ID = (@max-...

Hibernate - Avoiding unnecessary join when using foreign key in where clause

Hi I try to optimize the database queries in Hibernate, but I found a blocker: <class name="SupportedLanguageVO" table="AR_SUPPORTED_LANG" > <cache usage="read-only"/> <id name="Id" type="java.math.BigInteger"> <column name="ID" sql-type="NUMBER(20)" not-null="true"/> <generator class="assigned"/> </id> <property name="Ord...

need help to figure out average price markup on products, I have cost and selling price

I have a database that has products in it, with both the cost and selling price. I want to create a simple report that will group similar costing, and figure out how much markup there is. I will then use this to apply the markup on new products. So my database looks like: productid, costPrice, sellingPrice 123, 9.99, 14.99 I have n...

MySql displaying results in same order no matter "array-order"

I am using "solr" search engine to query an index for classifieds that match a given criteria. The results are the ID:numbers of the classifieds, which I then use to find all matches in a MySql database with those ID:s. The ID:s returned are put into an array. As you can see below the array is imploded. Then I use the "IN" to find all m...

How to debug PDO database queries?

Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code. Prepared PDO statements are faster and better and safer, but one thing bothers m...

Finding a sql query to get the latest associated date for each grouping.

I have a sql table of payroll data that has wage rates and effective dates associated with those wage rates, as well as hours worked on various dates. It looks somewhat like this: EMPID DateWorked Hours WageRate EffectiveDate 1 1/1/2010 10 7.00 6/1/2009 1 1/1/2010 10 7.25 6/10/2009 1 1/1/201...

Postgres: Issue Creating a User

I have a database called ADB. When I create a new user/role, that user cannot, by default SEE ADB and query against the tables in it. How can I change that default? ...

how do I query sql for a latest record date for each user

I have a table that is a collection entries as to when a user was logged on. username, date, value -------------------------- brad, 1/2/2010, 1.1 fred, 1/3/2010, 1.0 bob, 8/4/2009, 1.5 brad, 2/2/2010, 1.2 fred, 12/2/2009, 1.3 etc.. How do I create a query that would give me the latest date for each user?...