I have a table named "person". It contains person's id and it's parent id (only one parent is possible). As a result of a query, I want a table with first column - a person id, and a second column - a list of it's children id's. How exactly to do this? I've read about listagg function, but I'm not sure if it is appropriate for my purpose.  And this query produces an empty second column:  select t1.id,  (select t2.id from person t2  where t2.parent_id = t1.id)  from person t1  where t1.status = 'parent';
23656
I have a simple query:  select * from countries   with the following results:  country_name ------------ Albania Andorra Antigua .....   I would like to return the results in one row, so like this:  Albania, Andorra, Antigua, ...   Of course, I can write a PL/SQL function to do the job (I already did in Oracle 10g), but is there a nicer, preferably non-Oracle-specific solution (or may be a built-in function) for this task?  I would generally use it to avoid multiple rows in a sub-query, so if a person has more then one citizenship, I do not want her/him to be a duplicate in the list.  My question is based on the similar question on .  UPDATE: My function looks like this:  CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is ret varchar2(4000) := ''; TYPE cur_typ IS REF CURSOR; rec cur_typ; field varchar2(4000); begin      OPEN rec FOR sqlstr;      LOOP          FETCH rec INTO field;          EXIT WHEN rec%NOTFOUND;          ret := ret || field || sep;      END LOOP;      if length(ret) = 0 then           RETURN '';      else           RETURN substr(ret,1,length(ret)-length(sep));      end if; end;
36635
I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference.  desc urllist; +-------+---------------------+------+-----+---------+----------------+ | Field | Type                | Null | Key | Default | Extra          | +-------+---------------------+------+-----+---------+----------------+ | id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | | url   | text                | NO   |     | NULL    |                | +-------+---------------------+------+-----+---------+----------------+   and  desc wordlocation; +----------+---------------------+------+-----+---------+-------+ | Field    | Type                | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | urlid    | bigint(20) unsigned | NO   |     | NULL    |       | | wordid   | bigint(20) unsigned | NO   |     | NULL    |       | | location | int(10) unsigned    | NO   |     | NULL    |       | +----------+---------------------+------+-----+---------+-------+   The software app is a web spider. It crawls a list of urls, extracts those urls, and inserts these into the urllist table. Then, an indexer checks to see what urls have not yet been indexed, and then proceeds to index said urls.  Here is the query I am using to find items in the left table (urllist) that have not yet been indexed in the right table (wordlocation). This query is as suggested on the :  select * from urllist ul  left join wordlocation wl on ul.id = wl.urlid  where wl.urlid IS NULL;   As of this writing my test database has only 600 indexed urls, and the wordlocation table has 1.3 million rows.  However, my CPU is at 100%, and the longest I've waited to see if the query would complete is a half hour (which, it never did by the way).  To be thorough, here is the explanation of the query:  explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                   | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ |  1 | SIMPLE      | ul    | ALL  | NULL          | NULL | NULL    | NULL |   50364 |                         | |  1 | SIMPLE      | wl    | ALL  | NULL          | NULL | NULL    | NULL | 1351371 | Using where; Not exists | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+   I need this query to finish in seconds, not minutes. Also, I'm worried about scalability. I have 40,000 unique urls that are waiting to be added to the index, so how do I take that into consideration with my table and query design? 400,000 urls?  Just a couple notes about my decisions on the current table structure.  I have no intention of stopping at 400,000 urls, but perhaps bigint(20) is a bit overzealous?   Url as text is for more practical reasons. I index a lot of Asian and other foreign language domains which do not appear as their equivalent Kanji or other characters in the database and frequently take more than 255 characters.  I'm using MySQL. I'm definitely open to suggestions as to better table and query design. Please let me know if I can provide more information.
20387
I have two tables A &amp; B. Table A id | name | num  Table B id | date | roll  id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id is given and also along with that data, I need the name whose id is given from table A. NOTE: cannot use multiple queries separated by commas; need to do both the SELECTs in one query.
12844
Using MySQL, I can do something like:  SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;   My Output:  shopping fishing coding   but instead I just want 1 row, 1 col:  Expected Output:  shopping, fishing, coding   The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.  I've looked for a function on  and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets.  So does anyone here know how to do this?
81924
I'm trying to find the best way for a basic security system for a website.  I know I want users and groups.  I thought I'd have:  user_table user_id user_name ...  group_type group_id group_name parent_id ...  group_table id user_id group_id   First one is the user, second one is the group, and the third one is the intermediary table that connects the two.  One user has many groups.  Does this sound ok?
25277
I need an aggregate function that MySQL doesn't provide.  I would like it to be in MySQL's flavor of SQL (that is, not in C).  How do I do this?  What I'm stuck on is creating an aggregate function -- the  don't seem to mention how this is done.  Examples of desired usage of a product function:  mysql&gt; select product(col) as a from `table`; +------+ | a    | +------+ |  144 | +------+ 1 row in set (0.00 sec)  mysql&gt; select col, product(col) as a from `table` group by col; +-----+------+ | col | a    | +-----+------+ |   6 |   36 | |   4 |    4 | +-----+------+ 2 rows in set (0.01 sec)
112394
Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 and Table2, and Table2 needs to have the inserted id (PK) from Table1...  If I were to do this...  INSERT INTO Table1 SELECT A, B, C FROM MyTable INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable   I'd get the ID of the last inserted record into Table1.  Is a CURSOR or WHILE loop the only ways to do this?
91339
I am running the below query in ideone   CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) );  INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');     INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');  --------------------------------- CREATE TABLE BOOK  ( BOOK_ID NUMBER(5) PRIMARY KEY, BOOK_NAME VARCHAR2(15), AUTHOR_ID NUMBER(5), CONSTRAINT author_fk FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ON DELETE CASCADE );  INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10002, 'HARRY POTTER', 1235); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10003, 'TO BE OR NOT TO BE', 1237); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10004, 'PROJECT MANAGEMENT', 1234);   After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:  DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;   Select * from book ;  10001|SOFTWARE DEVELOPEMENT|1234 10002|HARRY POTTER         |1235 10003|TO BE OR NOT TO BE   |1237 10004|PROJECT MANAGEMENT   |1234   and  Select * from author ;  1235|J K ROLLING 1236|WILLAM 1237|SHAKESPEAR   the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table). Can someone help me what I am doing wrong.  Thanks,
58142
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.  CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford   Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.  1, Dan, Mazda+Suzuki 2, John, Ford 3, Dasha, Volvo+Ford   Thanks
81977
I'm having trouble understanding how to access each individual result returned from a subquery. The picture shows the code which:   takes fields and objects chosen by a user and assigns them to String variables, the string vars are used to query a dynamic query string, the query string is feed into a database.query and the result is stored in an array I can individually access each of the query results from this array (parent object results),  But I have not been able to individually access the subquery results.   If I feed the subquery results into an array...something like:  SObject[] c = p.get(i).getSObjects(childO);   I still can't get to each individual child record per parent. I can only see all child records per each parent. I need to get to each child record per adult, so that I can roll up results.  Can anyone help?  // These strings will be populated dynamically from a VF page String parentO  = 'Account'; String parentF  = 'Products_Purchased__c'; String childO   = 'Opportunity';     if(childO  == 'Opportunity'){ childO = 'Opportunities'; } String childF   = 'Product_Type__c';  // create dynamic SOQL query string String query = 'SELECT id, (select ' +childF+ ' from ' +childO+') FROM ' +parentO;  // query and store 33 results in variable p SObject[] p = Database.query(query);  for(Integer i = 0; i &lt; p.size(); i++){     system.debug('Parent result from SOQL query: ' +p[i]);     if(p.get(i).getSObjects(childO) &lt;&gt; null){         system.debug('Child result(s) from SOQL query: ' +p.get(i).getSObjects(childO));         system.debug('Child field: ' +c.get(i).get(childF));     } }
4453
I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following:   Table father has a composite PK (col1, col2) Table closure has ancestor(a-col1, a-col2), descendant(d-col1, d-col2) and length. Ancestor and Descendant are composite FKs, each pointing to the PK of father. The 4 columns (a-col1, a-col2,d-col1, d-col2) are a composite PK.   Look at the following fiddle:   I am working with MySQL.  I have constraints in CASCADE, but when I try to update the father table, the constraints give me errors. I can't do the following update to father table row:  UPDATE father  SET `col1` = "UPDATE_MY_ID_PLEASE"  WHERE `col1` = 'A' AND `col2` = 1;   Is the design wrong? How should this be done?
70260
I have a bunch of objects in a flat structure. These objects have an ID and a ParentID property so they can be arranged in trees. They are in no particular order. Each ParentID property does not necessarily matches with an ID in the structure. Therefore their could be several trees emerging from these objects.  How would you process these objects to create the resulting trees ?   I'm not so far from a solution but I'm sure it is far from optimal...  I need to create these trees to then insert Data into a database, in proper order.  There are no circular references. A Node is a RootNode when ParentID == null or when ParentID can't be found in the other objects
110989
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?    In the example shown, the query should return count as 4 rows.
75219
My issue is that I have two lists that I would like to move with their data.  While I am able to save both as templates and then upload to the new site, none of the lookup columns in my parent table are populated with data now.  Can this be done?
116532
Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ParentId column.  What if a Parent Task could also be a Sub Task, does this change the design?
78562
I'm trying to return parent opportunities that only have 1 or more related child opportunities, but I can't get my query right.   List &lt;opportunity&gt; oppsWithPotentialChildrenToBeUpdated = [Select id, name, stagename, (Select Id, name, stagename From Opportunities__r where stagename = 'Pre-Renewal' and type = 'Renewal')                                                                                                              From Opportunity o where id in  (Select opportunityId from Opportunities__r where stagename = 'Pre-Renewal' and type = 'Renewal') ];   Error:   Compile error at line 1 column 59  sObject type 'Opportunities__r' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names.   I checked the schema in eclipse, and Opportunities ought to work.... I tried Opportunities__r, Opportunities__c, and Opportunity
32424
I have a table with the following structure:  CREATE TABLE `Rings` (     ID_RingType CHAR(2),     Number MEDIUMINT UNSIGNED,     ID_User INT(11) );   and with data:  INSERT INTO `Rings` VALUES   ('AA',1,1),   ('AA',2,1),   ('AA',3,1),   ('AA',11,1),   ('AA',12,1),   ('AA',13,1),   ('AA',14,1),   ('AA',15,1),   ('AB',16,1),   ('AB',17,1),   ('AB',18,1),   ('AB',19,1),   ('AB',20,2),   ('AB',21,2),   ('AB',22,2);   I wish to group the data based on ID_User and ID_RingType and for each contiguous range of numbers list the MIN and MAX.  The results should look like:  ID_User | ID_RingType | MIN  | MAX 1       | 'AA'        | 1    | 3 1       | 'AA'        | 11   | 15 1       | 'AB'        | 16   | 19 2       | 'AB'        | 20   | 22   I went through several posts on this topic but was not able to tweak them to fit my data.  Any help would be appreciated.