sql

SqlCe odd results why? -- Same SQL, different results in different apps. Issue with

When I run this SQl in my mobile app I get zero rows. select * from inventory WHERE [ITEMNUM] LIKE 'PUMP%' AND [LOCATION] = 'GARAGE' When I run the same SQL in Query Analyzer 3.5 using the same database I get my expected one row. Why the difference? Here is the code I'm using in the mobile app: SqlCeCommand cmd = new SqlCeCommand(Q...

Please tell me how to get primary key name in SQL Server 2005

I need a primary key name and primary key column name of a table please tell me what query should I write.. ...

SQL duplicates with different primary keys

I have a table in this form: id | firstname | lastname ---+-----------+---------- 1 | alex | marti 2 | mark | finger 3 | alex | marti 4 | ted | port Need to return the firstname, lastname duplicates in this form: 1 | alex | marti 3 | alex | marti I tried doing select firstname, lastname from t g...

Accessing the join table in a hql query for a many-to-many relationship in grails

I have 2 domain classes with a many-to-many relationship in grails: decks and cards. The setup looks like this: class Deck { static hasMany = [cards: Card] } class Card { static hasMany = [decks: Deck] static belongsTo = Deck } After I delete a deck, I want to also delete all cards which no longer belong to a deck. The easiest way t...

Nested sql joins process explanation needed

Hi, I want to understand the process of nested join clauses in sql queries. Can you explain this example with pseudo codes? (What is the order of joining tables?) FROM table1 AS t1 (nolock) INNER JOIN table2 AS t2 (nolock) INNER JOIN table3 as t3 (nolock) ON t2.id = t3.id ON t1.mainId = t2.mainId ...

SQL group by LIKE pattern

I have to do a group by firstname and address on this sort of data | firstname | address +-----------+---------- | alex | 338 s. michigan | mark | finger | alex | 338 south michigan | ted | port But the group by will not return similar values of the address field. Is there a pattern I can apply to the group by?...

Use tnsnames.ora in Oracle SQL Developer

I am evaluating Oracle SQL Developer. My tnsnames.ora is populated, and a tnsping to a connection defined in tnsnames.ora works fine. Still, SQL Developer does not display any connections. Oracle SQL Developer Soars mentions, that if you have Oracle client software and a tnsnames.ora file already installed on your machine, Oracle S...

Is it possible to 'store search criteria' and the reuse it at some other time?

I have a classifieds website where users may search ads. I wonder if I can somehow store search criteria and whenever the users wishes, reuse that search criteria to make another search again, exactly the same... ? This would make it easier for users to search for a specific car for instance, and not have to fill in all the details suc...

Why does 'MSysNavPaneGroupCategories' show up in a .NET OleDbProvider initiated query to an MS Access 2K database when the criteria specifies "WHERE MSysObjects.Name NOT LIKE 'MSys*'"?

I want to list all tables and their row count, in an MS Access database, in a grid view. I am using a query as follows: SELECT MSysObjects.Name, CLng(DCount('*',[name])) AS RecordCount FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND (MSysObjects.Name NOT LIKE 'MSys*')) ORDER BY MSysObjects.Name; In MS Access Query pane this works j...

Sql Syntax: Update All values in table based on a value in a different table

Hi, I have three tables and I want to update all values for a particular type to the same value: table1: id, ValueType table2: id, Value table3: id, fkValueTypeId, fkValueId fkValueType references ID in table1. fkValue references ID in Table2 I am trying to set all Speed values to the same value: i.e. Table1: 0, speed 1, age 2, c...

Problem with Full Outer Join not working as expected.

I have a query to subtract current balance from one table with previous balance from another history table. When a particular product has no current balance but has previous balance, I am able to subtract it correctly...in this case it will be like 0 - 100. However, when the product has current balance but no previous balance, I am unab...

How to store search criteria or search results?

I have a php classifieds website (mostly) and I am currently using MYSQL as a database. Later on I will use SOLR or maybe Sphinx as a "search engine". I want to make it possible for users to view "results" of searches they have made before, but I don't know where to start... How is this done? Currently I have a form which is filled i...

JOINS, EXISTS or IN which is better? Few questions on SQL....

I have few questions on SQL.. How to analyze the performance of a query? Any software, inbuilt features of MSSQL server 2005/2008? What should be used in place of inin queries so that the performance is better? Eg: SELECT * FROM enquiry_courses WHERE enquiry_id IN ( SELECT enquiry_id FROM enquiries WHERE session_id = '4cd3420a1...

What is “E153 Updatable queries with subqueries” in the SQL standard?

I don't have the (expensive) SQL standard at hand; what are updatable queries in SQL core/foundation? I see that PostgreSQL doesn't support them, but some other databases do; can you point me to the documentation on how they work in those databases? PostgreSQL has query rewriting and updatable views with the rule system; is this very d...

Slow Update after Truncate

I've got a relatively simple update statement: update sv_konginfo ki set AnzDarl = 1 where kong_nr in ( select kong_nr from sv_darlehen group by kong_nr having count (*) = 1); which runs okay on its own (about 1 second for about 150.000 records). However, if I truncate the table and then re-insert the records: trun...

What are Parent-Child relationships?

Hi, What is the parent and what is the child in a sql relationship? In my case, I have a part (Say screw), and product material. For argument's sake, a product material (eg steel) can only belong to one part (but not in the real world). So this is 1:n. The part will have its pk as a fk in the ProductMaterial table. Which is parent and...

What is wrong with this nested WHILE loop in SQL

I ran into a weird situation today doing some one-time sql code. This nested loop doesn't seem to run the outer loop: it prints (0,0),(0,1),(0,2) and (0,3) declare @i int, @j int select @i = 0, @j = 0 while @i < 3 begin while @j < 3 begin select @i as i, @j as j set @j = @j + 1 end set @i = @i + 1 end Am I ...

Given a column name how can I find which tables in database contain that column ?

Given a column name how can I find which tables in database contain that column ? or alternatively How can I find that particular column exists for all tables in Database ? Note: Kindly explain answers with Examples as that I get most knowledge from the answer. Edit: I am using MySQL Database. ...

Use of null values in related tables with foreign key constraints

I have the following tables: Cateogories CategoryID (int) Primary Key CategoryName (varchar) Items ItemID (int) Primary Key CategoryID (int) ItemName (varchar) There is a foreign key constraint on Items.CategoryID. There is a chance that when a new item is created that there will be no category assigned. Is it better to set It...

c sharp and sql

I have a employee table which contains employee information and a Contact details table which contains the phone numbers of the employees. the employees have more than 2 phone numbers. now, to display the employee information, i have a datagrid. what i want to do is display the first 2 numbers along with the employee information in the ...