group-concat

Complicated query - help NEEDED!

Hi, really hope someone can help me on this one! I have 6 tables: Products prodid Prodequipment prodid equipclassmain equipclassor Equipclasses classid Equipfunctions equipid classid Equipment equipid Workshopequipment workshopid equipid Products – a list of some products Equipment – a list of some equipment Prodequipment – lists...

Sqlite group_concat ordering

In Sqlite I can use group_concat to do: 1...A 1...B 1...C 2...A 2...B 2...C 1...C,B,A 2...C,B,A but the order of the concatenation is random - according to docs. I need to sort the output of group_concat to be 1...A,B,C 2...A,B,C How can I do this? Thank you ...

Sqlite group_concat select with "special needs"

I know how to use group_concat with Sqlite, to do the following: id - f1 - f2 - f3 1 - 1 - a - NULL 2 - 1 - b - NULL 3 - 2 - c - NULL 4 - 2 - d - NULL select id, f1, group_concat(f2), f3 from table group by f1 result: 2 - 1 - a,b - NULL 4 - 2 - c,d - NULL as you can see, the ID's 1 and 3 are dropped, which is the ...

MYSQL selecting users with specific skills

Hi, I'm muddling my way through by far the most complex SQL query i've ever done, which is probably extremely simple for most of you (: I have three tables, User, Skills and User_Skills. The fields of which should be fairly self explanatory. I want to choose people who have one or more skills that meet my criteria. I can select a use...

Combine contents of multiple rows in 3NF mysql tables

Having dutifully normalised all my data, I'm having a problem combining 3NF rows into a single row for output. Up until now I've been doing this with server-side coding, but for various reasons I now need to select all rows related to another row, and combine them in a single row, all in MySQL... So to try and explain: I have three tab...

using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

I have a query which uses the GROUP_CONCAT of mysql on an integer field. I am using PHPMYADMIN to develop this query. My problem that instead of showing 1,2 which is the result of the concatenated field, I get [BLOB - 3B]. Query is SELECT rec_id,GROUP_CONCAT(user_id) FROM t1 GROUP BY rec_id (both fields are unsigned int, both are n...

How to use sum() within a group_concat() ?

Question revised Really wanted a group_concat of sums... Table: shops +---------+--------+--------+ | shop_id | name | state | +---------+--------+--------+ | 0 | shop 0 | 5 | | 1 | shop 1 | 5 | | 2 | shop 2 | 5 | | 3 | shop 3 | 2 | +---------+--------+--------+ Table: items +---------...

mysql GROUP_CONCAT

I want to list all users with their corropsonding user class. Here are simplified versions of my tables CREATE TABLE users ( user_id INT NOT NULL AUTO_INCREMENT, user_class VARCHAR(100), PRIMARY KEY (user_id) ); INSERT INTO users VALUES (1, '1'), (2, '2'), (3, '1,2'); CREATE TABLE classes ( class_id INT NOT N...

SQL Grouping with multiple joins combining results incorrectly

Hi I'm having trouble with my query combining records when it shouldn't. I have two tables Authors and Publications, they are related by Publication ID in a many to many relationship. As each author can have many publications and each publication has many Authors. I want my query to return every publication for a set of authors and i...

Postgresql GROUP_CONCAT equivalent?

I have a table and I'd like to pull one row per id with field values concatenated... In my table, for example, I have this: TM67 | 4 | 32556 TM67 | 9 | 98200 TM67 | 72 | 22300 TM99 | 2 | 23009 TM99 | 3 | 11200 And, I'd like to output: TM67| 4,9,72 | 32556,98200,22300 TM99 | 2,3 | 23009,11200 In MySQL, I was able to use GROUP_CONCA...

MySQL and GROUP_CONCAT() maximum length

Hello everyone, I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters. I'm very well aware that I can change the param group_concat_max_len to increase this limit: SET SESSION group_concat_max_len = 1000000; However, on th...

MySQL GROUP_CONCAT + IN() = missing data :-(

Example: Table: box boxID color 01 red 02 blue 03 green Table: boxHas boxID has 01 apple 01 pear 01 grapes 01 banana 02 lime 02 apple 02 pear 03 chihuahua 03 nachos 03 baby crocodile I want to query on the contents of each box, and return a table with each ID, colo...

mysql to active record

hi! having the following models, Contact has_many :group_contact_classifications has_many :groups, :through => :group_contact_classifications GroupContactClassification belongs_to :group belongs_to :contact Group has_many :group_contact_classifications has_many :contacts, :through => :group_contact_classifications i just want to con...

Sql Select Query Question. Merge Rows

I have a single MYSQL Question and need help ASAP. Database: ------------------------------------------------------------------- Email | Name | Tag ------------------------------------------------------------------- [email protected] |Test Person | TagOne [email protected] |Test...

Join one row to multiple rows in another table

I have a table to entities (lets call them people) and properties (one person can have an arbitrary number of properties). Ex: People Name Age -------- Jane 27 Joe 36 Jim 16 Properties Name Property ----------------- Jane Smart Jane Funny Jane Good-looking Joe Smart Joe Workaholic Jim Funny Jim Young I wo...

Workaround for missing group_concat on MySQL 3.23

I developed a script on MySQL 5.1, not realising the client is stuck with MySQL 3.23. It makes significant use of GROUP_CONCAT() and particularly GROUP_CONCAT(DISTINCT), to reduce a column of values down to a succinct, comma-separated list. So, are there any workarounds? The best I can think of is doing a separate query to just get the ...

Subquery with multiple results combined into a single field?

Assume I have these tables, from which i need to display search results in a browser: Table: Containers id | name 1 Big Box 2 Grocery Bag 3 Envelope 4 Zip Lock Table: Sale id | date | containerid 1 20100101 1 2 20100102 2 3 20091201 3 4 20091115 4 Table: Items id | name ...

MySQL DISTINCT on a GROUP_CONCAT()

Hi. I am doing SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table. Sample data below: categories ---------- test1 test2 test3 test4 test1 test3 test1 test3 However, I am getting test1 test2 test3 test4 test1 test3 back and I would like to get test1 test2 test3 test4 back. Any ideas? Many thanks! ...

SQL Query to return values of a particular column concactenated with comma (,)

Possible Duplicate: How do I Create a Comma-Separated List using a SQL Query? ItemName | DepartmentId --------------------- Item1 | 21 Item1 | 13 Item1 | 9 Item1 | 36 Item2 | 4 Item2 | 9 Item2 | 44 I need to display all the department Id's that require a particular Item using SQL Querie...

Using multiple GROUP_CONCAT in MySQL 4

Is it possible to use multiple GROUP_CONCAT in the SELECT clause? I'm asking because when I run my query, I do not get all the results, and the data in my GROUP_CONCAT columns are doubled. My GROUP_CONCAT columns are the last two in my SELECT clause. This is what I have: SELECT cases_details.id, cases_details.agency_number, cases_relati...