I want to do something like: select a.a_ft,count(b.a_ft) FROM row a JOIN row b on match a.a_ft against (b.b_ft). However, this isn't legal. Basically, I just want to look for similar phrases in my database. What is the easiest way to do this? ...
Hello! I'm trying to create views that would accumulate all the needed data from joined sources: CREATE OR REPLACE VIEW dir AS SELECT dir_data.id, dir_data.parent_id, dir_data.name, (owner.*)::owner, -- owner_id FROM dir_data LEFT JOIN owner ON owner.id = dir_data.owner_id For example, this allows to select ow...
how to compare the values of same table(say for eg: Order table) each and every time the record get inserted , if the record with same values get inserted already in same table i should not insert the new record with same values. how to do that exactly in sql server 2008 ...
self join- how to use the aggregate functions select a.tablename, b.TableName,b.UserName from Employee a inner join Employee b on a.ColumnValue=b.ColumnValue and and a.TableName <> b.TableName and a.UserName=b.UserName and also to check whether the same user has count of records i.e Employee a = count of records of Employee b...
Based on the following table ID Date State ----------------------------- 1 06/10/2010 Complete 1 06/04/2010 Pending 2 06/06/2010 Active 2 06/05/2010 Pending I want the following ouptut ID Date State --------------------------- 1 06/04/2010 Complete 2 06/05/2010 Active So date is the ea...
I have a table like Employee ================== name salary ================== a 10000 b 20000 c 5000 d 40000 i want to get all the employee whose salary is greater than A's salary. I don't want to use any nested or sub query. It has been asked in an interview and hint was to use self join. I really c...
Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!! The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12...
I've got a query I wrote using a JOIN to display a summary using the part's Serial_Number. Obviously, it does not work! Using the direct search here: select Serial_Number, Op_ID, Date_Time as 'DecayDate', DECAY.System_ID AS 'DecayID', Test_Result as 'DecayResult' from Test_Results where serial_number='CP21295 1006 09' and system...
I have the following table: myTable: +----+----------+ | id | parentID | +----+----------+ | 1 | null | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 4 | ----------------- i would like to get all rows tracing back until there's no parentID anymore. So ".... WHERE id=5" would give me: 5, 4, 2, 1 ...
I have this table with pages, these pages have parents that are also pages in the same table. For this examples sake the table looks like: table: Pages PageId :Key PageParent :Foreign Key PageName Now my question is what would the SQL look like when creating a menustructure like: PageId PageParent PageName 1 NULL ...
This question relates to the schema I suggested in my original question regarding a stock control application. I'm trying to create a MySQL query that provides the current stock for a particular item. The query is working but I wondered whether there is a more efficient way of obtaining the information I require. SELECT 's'.*, '...
create table test( container varchar(1), contained varchar(1) ); insert into test values('X','A'); insert into test values('X','B'); insert into test values('X','C'); insert into test values('Y','D'); insert into test values('Y','E'); insert into test values('Y','F'); insert into test values('A','P'); insert into test values('P','Q'); i...
There are two tables: table A and table B. They have the same columns and the data is practically identical. They both have auto-incremented IDs, the only difference between the two is that they have different IDs for the same records. Among the columns, there is an IDENTIFIER column which is not unique, i.e. there are (very few) record...
I'm working with a 12-million record MyISAM table with surname, address, gender and birthdate fields: ID SURNAME GENDER BDATE COUNTY ADDRESS CITY 1 JONES M 1954-11-04 015 51 OAK ST SPRINGFIELD 2 HILL M 1981-02-16 009 809 PALM DR JONESVILLE 3 HILL F 1979-06-...
As part of the process of replacing some old code that used an incredibly slow nested select, I've ended up with a query that looks like this: SELECT r3.r_id AS r3_id, r2.r_id AS r2_id, r1.r_id AS r1_id FROM table_r r3 LEFT JOIN ( table_r r2 INNER JOIN ( table_r r1 INNER JOIN table_d d ON r1.r_id = d.r_id ) ON r2.r_id = r1...
I have an ActiveRecord called Name which contains names in various Languages. class Name < ActiveRecord::Base belongs_to :language class Language < ActiveRecord::Base has_many :names Finding names in one language is easy enough: Language.find(1).names.find(whatever) But I need to find matching pairs where both language 1 and l...
HI All I was hoping someone could help me improve a query I have to run periodically. At the moment it takes more than 40 minutes to execute. It uses the full allocated memory during this time, but CPU usage mostly meanders at 2% - 5%, every now and then jumping to 40% for a few seconds. I have this table (simplified example): CRE...
Take a look at the following mySQL query: SELECT fname,lname FROM users WHERE users.id IN (SELECT sub FROM friends WHERE friends.dom = 1 ) The above query first creates a set of ALL the friends.sub's via the inner query, and then the outer query selects a list of users where user ids are contained within the set created by the inner q...
Hi. I have a problem in T-SQL that I find difficult to solve. I have a table with groups of records, grouped by key1 and key2. I order each group chronologically by date. For each record, I want to see if there existed a record before (within the group and with lower date) for which the field "datafield" forms an allowed combination wit...
Hi friends, I have a self referential category table as you see: I want to parse this table to find out tree level for each category. for example if root node level is 0 then CPU and Hard Drive and VGA and RAM are in level 1 and so on. how can I handle that? I created a dictionary to put each category ID and its Level: Dictionary<int...