Hello, I have a MySQL query in which I want to include a list of ID's from another table. On the website, people are able to add certain items, and people can then add those items to their favourites. I basically want to get the list of ID's of people who have favourited that item (this is a bit simplified, but this is what it boils dow...
(NOTE: This question is not about escaping queries, it's about escaping results) I'm using GROUP_CONCAT to combine multiple rows into a comma delimited list. For example, assume I have the two (example) tables: CREATE TABLE IF NOT EXISTS `Comment` ( `id` int(11) unsigned NOT NULL auto_increment, `post_id` int(11) unsigned NOT NULL, `na...
Somehow GROUP_CONCAT is able to round up six "pages" of an article (each stored as TEXT) and toss them into a single MEDIUMTEXT without losing any data, but there are some one-page articles that are longer than normal (but still obviously fit within the TEXT data type) that lose a significant amount of data. Anyone know what's up? Origi...
BACKGROUND:I am running MS2005. I have a MASTER table (ID, MDESC) and a DETAIL table (MID, DID, DDESC) with data as follows 1 MASTER_1 2 MASTER_2 1 L1 DETAIL_M1_L1 1 L2 DETAIL_M1_L2 1 L3 DETAIL_M1_L3 2 L1 DETAIL_M2_L1 2 L2 DETAIL_M2_L2 If I join the tables with SELECT M.*, D.DID FROM MASTER M INNER JOIN DETAIL D on M.ID = D.MID I...
I'm looking for the LINQ equivalent to the Sybase's LIST() or MySQL's group_concat() It'll convert: User Hobby -------------- Bob Football Bob Golf Bob Tennis Sue Sleeping Sue Drinking To: User Hobby -------------- Bob Football, Golf, Tennis Sue Sleeping, Drinking ...
SELECT *, GROUP_CONCAT(rating) AS rating_total, SUM(rating_total) AS rating_sum, AVG(rating_sum) AS rating_avg FROM ratings GROUP BY pid For some reason the sum and average don't execute....how do you make this statement work? ...
In short: Is there any way to sort the values in a GROUP_CONCAT statement? Query: GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = l.competence AND parent.id != 1 ORDER BY parent.lft) SEPAR...
The Query: SELECT MemberId, a.MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName Table a MemberID MemberName -------------- ---------- 1 Al 1 Al 3 A2 Table b MemberName...
I am trying to use PHP to return SQL values into an HTML table. I am able to get every column to populate without a problem except for the last column, "GROUP _ CONCAT (provision_id)." Relevant code: <?php global $wpdb; $wpdb->show_errors(); $contents = $wpdb->get_results( $wpdb->prepare("SELECT salaries.id, name, remaining, contract...
Hello, I currently have the following query: SELECT group_concat(DISTINCT usrFirst, usrLast) as receiver_name //etc When using PHP, it outputs my list of names as follows: <?php echo $row['receiver_name']; //Outputs: JohnDoe,BillSmith,DaveJones //Desired ouput: John Doe, Bill Smith, and Dave Jones Basically, I nee...
The question is to get table column data and use it as a value list for IN function; For this example I created 2 tables: movies and genres Table "movies" contains 3 columns: id, name and genre. Table "genres" contains 2 columns: id and name. +- movies-+ | |- movie_id - int(11) - AUTO_INCREMENT - PRIMARY | |- movie_nam...
For performance,I need to set a limit for the GROUP_CONCAT, and I need to know if there are rows not included. How to do it? EDIT Let me provide a contrived example: create table t(qid integer unsigned,name varchar(30)); insert into t value(1,'test1'); insert into t value(1,'test2'); insert into t value(1,'test3'); select ...
I somehow need this feature,but MySQL doesn't support it at this moment. I'm using GROUP_CONCAT(CONCAT(...)) to generate a xml-like stuff. But when the size exceeds the limit,the xml is just broken! So I have to somehow make it only retrieve 5 rows ! ...
I have a MySQL database in which each user has an account, and each account can have multiple permissions. My ultimate goal is to end up with the account's username, and a comma-delimited list of permissions ids. There are two ways I can accomplish this: SELECT a.username, GROUP_CONCAT(rp.permission_id) as permission_ids FROM account ...
hi, i have a mysql table set up like so: id uid keywords -- --- --- 1 20 corporate 2 20 corporate,business,strategy 3 20 corporate,bowser 4 20 flowers 5 20 battleship,corporate,dungeon what i WANT my output to look like is: 20 corporate,business,strategy,bowser,flowers,battleship,dungeon b...
I want to order the results in a GROUP_CONCAT function. The problem is, that the selection in the GROUP_CONCAT-function is another function, like this (fantasy select): SELECT a.name, GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY b.id ASC) AS course FROM people a, stuff b, courses c GROUP BY a.id I want to get a resu...
I have an expression that returns the following data ID Name Date 1 BRN 01/12/2009 00:00:00 1 BRN 01/06/2009 00:00:00 2 WTI 01/12/2009 00:00:00 2 WTI 01/06/2009 00:00:00 I'd like to add a group by ID and Name and then concat the two dates together with a hyphen seperating. My only solution so far is to get all the data th...
How can I select and concat every field in a row? I want to do something similar to this: SELECT concat(SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name') as single FROM tbl_name ..but obviously the above doesn't work. Any suggestions? ...
Let's say my DB Scheme is as follows: T_PRODUCT id_product (int, primary) two entries: (id_product =1) , (id_product =2) T_USER id_user (int, primary) id_product (int, foreign key) name_user (varchar) two entries: (id_product=1,name_user='John') , (id_product=1,name_user='Mike') If I run a first query to get all products with their...
I would like to ask for opinion on making aggregate data by concatenating strings. If I have a column aggregate but I want to concatenate then in an aggregate column, which is faster in terms of performance? Doing one SQL then just aggregating then in the CODE. Or selecting the main data then querying one at a time. For Example: TABLE_...