I have a SQL query with three SELECT statements. A picture of the data tables generated by these three select statements is located at www.britestudent.com/pub/1.png. Each of the three data tables have identical columns. I want to combine these three tables into one table such that: (1) All rows in top table (Table1) are always includ...
I have a question about how MS SQL evaluates functions inside CTEs. A couple of searches didn't turn up any results related to this issue, but I apologize if this is common knowledge and I'm just behind the curve. It wouldn't be the first time :-) This query is a simplified (and obviously less dynamic) version of what I'm actually doing...
Hi. I need to do what to me is an advanced sort. I have this two tables: Table: Fruit fruitid | received | basketid 1 20100310 2 2 20091205 3 3 20100220 1 4 20091129 2 Table: Basket id | name 1 Big Discounts 2 Premium Fruit 3 Standard Produce I'm not even s...
Suppose I have the following query: select * from A, B, C, D where A.x = B.x and B.y = C.y and A.z = D.z I have indexes on A.x and B.x and B.y and C.y and D.z There is no index on A.z. How can I give a hint to this query to use an INDEX hint on A.x but a USE_HASH hint on A.z? It seems like hints only take the table name, not the sp...
So, I have three tables: The class defenitions: engine = create_engine('sqlite://test.db', echo=False) SQLSession = sessionmaker(bind=engine) Base = declarative_base() class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = ...
I created an ssis package. It runs fine when i run it from command prompt. But when i run it from query analyser using xp_cmdshell, it gives the error below Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this informat...
I have been using MySQL for 2 years now, yet I still don't know what you actually do with the JOIN statement. I really didn't come across any situation where I was unable to solve a problem with the statements and syntax I already know (SELECT, INSERT, UPDATE, ordering, ...) What does JOIN do in MySQL? (Where) Do I need it? Should I ge...
I've just stated developing with vsdb, and while it seems to be functional, it seems like it introduces unnecessary restrictions. Effectively what I'd LIKE to do is use it to manage database creation, as well as upgrades etc. In creating, nothing special, just a if db not exists create db, and add a couple logins. I'd like to push out...
UPDATED QUESTION: Ok, I am going to simplify my question since I don't really know how to answer your questions. Say I create a new Windows Forms Application, then Project->Add New Item->Local Database. Then in Database Explorer I create a table ("testtable") and give it an "ID" column and "VALUE" column. Can you provide me with the ...
I am creating a webpage which is using asp .net as its backend. I need to grab categories from my database which is SQL Server. I am using this code right now For Each row00 As DataRow In f_oDatatable(7).Rows sCategories += row00.Item("Category").ToString & " / " Next If sCategories.Length > 0 Then sCategories = sCategories.Su...
I have two MS Access databases (with identical table structures), and I'd like to use a SQL statement (programatically in VB.NET) to copy records from one to the other. Both databases are locally stored, in the same directory (and this will always be the case). Suggestions? Thanks! ...
We are in the process of moving databases from older 32 bit hardware running sql 2005 to newer hardware with sql 2008 64 bit. My question is if the database is automatically converted to 64bit after it is reattached on the new server or if it is running in 32bit mode on a 64bit instance. Is there a way to tell? ...
I've been looking at fast ways to select a random row from a table and have found the following site: http://74.125.77.132/search?q=cache:http://jan.kneschke.de/projects/mysql/order-by-rand/&hl=en&strip=1 What I want to do is to select a random url from my table 'urls' that I DON'T have in my other table 'urlinfo'.The query I am...
I was just reading an update from a friend's project, mentioning the use of memtables to store data temporatily and then flush to a table on disk. Up to now, I have never faced a situation where I would use a memtable, or a situation where I would think the use of a mem table would be beneficial; so I wonder, when would someone use mem t...
Say I have "user". It's the key. And I need to keep "user count". I am planning to have record with key "user" and value "0" to "9999+ ;-)" (as many as I'll have). What problems I will drive in if I use Cassandra, HBase or MySQL for that? Say, I have thousand of new updates to this "user" key, where I need to increment the value. Am I i...
I'm using Oracle object data types to represent a timespan or period. And I've got to do a bunch of operations that involve working with collections of periods. Iterating over collections in SQL is significantly faster than in PL/SQL. CREATE TYPE PERIOD AS OBJECT ( beginning DATE, ending DATE, ... some member functions...); C...
I need to compare the value of a column (LASTNAME) with a system variable (:VARIABLE), but the variable is an email address, so I need to trim off the "@email.com" and "firstname." Some things I've tried: select * from TABLENAME where LASTNAME LIKE :VARIABLE select * from TABLENAME where LASTNAME IN :VARIABLE I've been abl...
I have a table a with a list of id's, and a user-defined function foo(id) that takes the id and returns a VARCHAR(20). What I am trying to do is: SELECT id, foo(id) AS 'text field' FROM a However, instead of calling the function for each ID number, like I desired, the text comes back the same for every row. I have tested the foo() f...
Hi i've wrote a query that works: SELECT `comments`.* FROM `comments` RIGHT JOIN (SELECT MAX( id ) AS id, core_id, topic_id FROM comments GROUP BY core_id, topic_id order by id desc) comm ON comm.id = comments.id LIMIT 10 I want know if it is possible (and how) to rewrite it to get better performance. Thanks ...
Take the following query: select * from ( select a, b from c UNION select a, b from d ) where a = 'mung' order by b Will the optimizer generally work out that I am filtering a on the value 'mung' and consequently filter mung on each of the queries in the subquery. OR will it run each query within the subquery union and r...