SQL explain plan: what is Materialize?
I asked PostgreSQL to explain my query. Part of the explanation was: table_name --> Materialize What does materialize do? I'm joining two tables, not views or anything like that. ...
sql
unique id in more than one table [MySQL]
I want to make auto increment id in two MySQL tables (if in first table there is id=4 then it cannot be id=4 in the second table). My question is how can I do that in the best way? ...
UNIQUE CONSTRAINT on a column from foreign table in SQL Server 2008
I have two tables: create table [dbo].[Main] ( [ID] [int] identity(1,1) primary key not null, [No] [int] not null, [Sign] [char](1) not null ) create table [dbo].[Names] ( [ID_Main][int] primary key not null, [Name][nvarchar](128) not null, constraint [FK_Main_Users] foreign key ([ID_Main]) references [dbo].[Ma...
What's the best way to calculate similarity between rows in a table based on association?
Suppose each Person has a collection of favorite Books. So I have a table for: Person Book The association between Person and Book (joint table for MxN) I want to fetch the Persons that are similar to a Person1 based on the favorite Books overlaping. That is: The more books they have in common, the more they are similar. I don't ha...
How to find all records that share the same field value as some other record?
I need to extract all records which have a field which does NOT have a unique value. I can't figure out an elegant way to do it - using annotation or some other way. I see a "value_annotate" method to the object manager but it's unclear if it's at all related. Currently I'm using the inelegant way of simple looping through all values a...
Multiple OR Clauses in MySQL
I'm trying to grab content where id = 3 OR id = 9 OR id = 100... Keep in mind, I can have a few hundred of these ids. What is the most efficient way to write my query? $sql = "SELECT name FROM artists WHERE (id=3 OR id=9 OR .... id-100)" ...
SQL View with Data from two tables
Hello! I can't seem to crack this - I have two tables (Persons and Companies), and I'm trying to create a view that: 1) shows all persons 2) also returns companies by themselves once, regardless of how many persons are related to it 3) orders by name across both tables To clarify, some sample data: (Table: Companies) Id Name 1 Ba...
mysql query for getting all messages that belong to user's contacts
So I have a database that is setup sort of like this (simplified, and in terms of the tables, all are InnoDBs): Users: contains based user authentication information (uid, username, encrypted password, et cetera) Contacts: contains two rows per relationship that exists between users as (uid1, uid2), (uid2, uid1) to allow fo...
help me with the following sql query
could somebody correct my following query, i am novice to software development realm, i am to a string builder object in comma separated form to my query but it's not producing desired result qyery is as follows and string cmd = "SELECT * FROM [placed_student] WHERE passout_year=@passout AND company_id=@companyId AND course_id=@courseId...
Simple Database normalization question...
Hi all, I have a quick question regarding a database that I am designing and making sure it is normalized... I have a customer table, with a primary key of customerId. It has a StatusCode column that has a code which reflects the customers account status ie. 1 = Open, 2 = Closed, 3 = Suspended etc... Now I would like to have another f...
SQL Query Help - Return row in table which relates to another table row with max(column)
I have two tables: Table1 = Schools Columns: id(PK), state(nvchar(100)), schoolname Table2 = Grades Columns: id(PK), id_schools(FK), Year, Reading, Writing... I would like to develop a query to find the schoolname which has the highest grade for Reading. So far I have the following and need help to fill in the blanks: SELEC...
Is it possible to capture data from a WHERE clause?
I have a scenario where I'm calculating something in the WHERE clause of my SQL, but I also want to get that calculation - since it's expensive. Is it possible to get the results of something done in the WHERE clause, like this: SELECT `foo` FROM `table` WHERE (foo = LongCalculation(`column`)) Wishful thinking, or possible with MySQL?...
Consolidating separate Loan, Purchase & Sales tables into one transaction table.
INFORMIX-SE with ISQL 7.3: I have separate tables for Loan, Purchase & Sales transactions. Each tables rows are joined to their respective customer rows by: customer.id [serial] = loan.foreign_id [integer]; = purchase.foreign_id [integer]; = sale.foreign_id [integer]; I would like to c...
Multiple conditions with CASE statements
I need to query some data. here is the query that i have constructed but which isn't workig fine for me. For this example I am using AdventureWorks database. SELECT * FROM [Purchasing].[Vendor] WHERE PurchasingWebServiceURL LIKE case // In this case I need all rows to be returned if @url is '' or 'ALL' or NULL when (@url IS null OR @u...
SQL function to get count of how many times string appears in column?
Is there a function for MySQL that will count the number of times a string occurs in another string or column? Basically I want: SELECT SUB_COUNT('my word', `my_column`) AS `match_count` FROM `table` Thanks! EDIT: I need to know how many times the string appears in a column for each row in a SELECT. ...
SQL SELECT multiple INNER JOINs
its Access database.. i have a Library table, where Autnm Topic Size Cover Lang are foreign Keys each record is actually a book which has its properties such as author and stuff. i am not quite sure i am even using the correct JOIN.. quite new with "complex" SQL :) SELECT Library.Bknm_Hebrew, Library.Bknm_English, Library.Bknm_Russia...
How to write stored procedure in SQL Server 2008 that generates 3 million random no
How to write stored procedure in SQL Server 2008 that generates 3 million random no in two columns in integer datatype. ...
Problem with autocommit in ANT SQL task
I have an SQL script and want to apply it witn ANT task. This script clears out schema, creates new tables and views. The ANT defined task as follows: <sql driver="com.mysql.jdbc.Driver" url="jdbc:mysql://host:3306/smth" userid="smth" password="smth" expandProperties="false" autocommit="true" ...
Can I execute SQL statements directly in a JDO environment?
I am using Datanucleus JDO on top of HSqlDb. I would like to execute the following SQL statement to tell HsqlDb to set the write delay to 0: "SET WRITE_DELAY 0" Is there a way I can do this from a JDO PersistenceManager or a PersistenceManagerFactory? On a sidenote: I have tried to modify write_delay by using the following connection ...
Keep history in django to draw graphs.
Hello, I am looking for the best way to have an history of my models (interger & float fields) in Django. I read http://stackoverflow.com/questions/1952913/keeping-a-history-of-data-changes-in-database and it seems that triggers are the best option. My idea is to stay database agnostic if possible. How do you approach this issues in y...