Query with GROUP_CONCAT 1

[whosrdaddy]

I have 3 tables:

Articles
(id, name, ...)
sample data
(1, 'intel E8400')
OrderLines
(id, OrderId, ArticleId, number_of_articles, price...)
sample data
(1, 1, 1, 2, 10)
(2, 1, 1, 2, 20)

Serials
(id, ArticleId, OrderId, serialnumber)
sample data
(1, 1, 1, '1234')
(2, 1, 1, '4567')
(3, 1, 1, '8901')
(4, 1, 1, '2345')


for my invoice I need to get the serialnumbers for each orderline.

Based on the sample data, I want this output

2x Intel 8400, (Serials: 1234, 4567)
2x Intel 8400, (Serials: 8901, 2345)

This is what I have at the moment:

SELECT s.ArticleId, o.number_of_articles AS x, GROUP_CONCAT(s.serialnumber) AS sn
 FROM Serials s
 INNER JOIN Ordelines o ON o.OrderId = s.OrderId AND o.ArticleId = s.ArticleId
WHERE o.OrderId = 1

And produces this output:

[tt]
ArticleId x sn
--------- - --
1 2 1234,4567,8901,2345
[/tt]

And I want
[tt]
ArticleId x sn
--------- - --
1 2 1234,4567
1 2 8901,2345
[/tt]

How can I do this?

Greetings,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

ooops, query was not complete:

SELECT s.ArticleId, o.number_of_articles AS x, GROUP_CONCAT(s.serialnumber) AS sn 
FROM Serials s 
 INNER JOIN Ordelines o ON o.OrderId = s.OrderId AND o.ArticleId = s.ArticleId 
WHERE o.OrderId = 1
ORDER BY o.ArticleId

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[r937]

GROUP_CONCAT, like other aggregate functions, requires a GROUP BY clause whenever the SELECT clause contains non-aggregate expressions

if you omit the GROUP BY clause, mysql does something unusual -- it runs the query anyway!!! (other databases will barf if the GROUP BY clause is required but missing)

does this help? enough of a hint?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[whosrdaddy]

no its helps not .
I posted an error (again), the ORDER BY clause must be GROUP BY offcourse, sorry for that.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[r937]

SELECT o.number_of_articles AS x
     , a.name
     , GROUP_CONCAT(s.serialnumber) AS sn 
  FROM Orderlines AS o  
INNER 
  JOIN Articles AS a   
    ON a.id = o.ArticleId
INNER
  JOIN Serials AS s  
    ON s.OrderId = o.OrderId 
   AND s.ArticleId = o.ArticleId 
 WHERE o.OrderId = 1
[red]GROUP 
    BY o.id
     , o.OrderId
     , o.ArticleId[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[whosrdaddy]

close:

[tt]
ArticleId x sn
--------- - --
1 2 1234,4567,8901,2345
1 2 1234,4567,8901,2345
[/tt]

But I need:
[tt]
ArticleId x sn
--------- - --
1 2 1234,4567
1 2 8901,2345
[/tt]

Is there even a simple way to do this?

Oh, and I have no control over the schema, so modifying the DB is out of the question.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[r937]

you're right, it won't work

there's no way you can distinguish which Serials go with which OrderLines

your OrderLines data is bad, there is no way to do what you want

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[whosrdaddy]

yes,
I am aware of that.

I was hoping for some string cutting magic that would use the value of x.

I implemented a fix at application level but I was hoping I could do this on db level.

thanks anyway rudy!

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!