Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have 2 tables having the following columns

Person
------
person_id,
first_name, 
middle_name,
last_name

Items
-----
person_item_id,
person_id,
item_type,
status,
ordered_date

The query is 

select 
    Person.person_id as PID,
    Group_concat(Items.item_type) AS ITYPE,
    Group_concat(Items.status) AS STATUS,
    Group_concat(Items.orderd_date)AS ODATE,
    Group_concat(Items.person_item_id) AS IID 
from 
    Persons inner join 
    Items ON Person.person_id = Items.person_id 
group by person_id;

The resultset returned is as follows

PID ITYPE      STATUS                ODATE                     IID  
1   A,A,B,C    Y,N,Y,Y         2/5/2012,5/5/2012,17/5/2012     1,1,2
2   A,B        Y,N             5/5/2012,15/6/2012              1,2

One Person can have many item_type and can order same item many times,I want to show the record set in such a manner that no matter the item is purchased by person or not , if the item is present in the record set i am drawing a column and have to show the result corresponding to that as such if the item is purchase 3 times then i want to show the item and it's corresponding record i.e order_date,status etc in sequential manner.

For eg :-

If there are 3 items A, B,C then output should be shown on the screen as :-

PID           A           B                 C

1             Y           N                 Y
          2/5/2012      5/5/2012         7/5/2012  
         -----------        
              Y
          17/5/2012     


2             Y           N
          12/6/2012    15/6/2012

Now Suppose there is another item so what i am doing is first fetching the result set from above query and then traversing through that result set to check the existence of that item & if that item is present i am showing it & it's corresponding values , so 1st i have to traverse the whole result set to get the item_type and then have to show it .

share|improve this question
I am a bit confused: which MySQL version do you use? – dezso May 2 at 18:12

1 Answer

up vote 0 down vote

I think you need a third table, orders, which connects the items and persons.

Tables

CREATE TABLE IF NOT EXISTS `persons` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`person_id`)
);

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(4) NOT NULL AUTO_INCREMENT,
  `item_type` varchar(50) NOT NULL,
  PRIMARY KEY (`item_id`)
);

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` int(12) NOT NULL AUTO_INCREMENT,
  `item_id` int(4) NOT NULL,
  `person_id` int(4) NOT NULL,
  `status` varchar(50) NOT NULL,
  `ordered_date` date NOT NULL,
  PRIMARY KEY (`order_id`)
);

Data

INSERT INTO `persons` (`name`) VALUES
('Walter White'),
('John Locke');

INSERT INTO `items` (`item_type`) VALUES
('A'),
('B'),
('C');

INSERT INTO `orders` (`person_id`, `item_id`, `status`, `ordered_date`) VALUES
(1, 1, 'Y', '2012-5-2'),
(1, 2, 'N', '2012-5-5'),
(1, 3, 'Y', '2012-5-7'),
(1, 1, 'Y', '2012-5-17'),
(2, 1, 'Y', '2012-6-12'),
(2, 2, 'N', '2012-6-15');

Query

SELECT orders.person_id AS PID, GROUP_CONCAT(items.item_type) AS ITYPE, GROUP_CONCAT(status), GROUP_CONCAT(ordered_date)
FROM orders
INNER JOIN items ON
items.item_id = orders.item_id
GROUP BY person_id, orders.item_id

Result

| PID | ITYPE | GROUP_CONCAT(STATUS) | GROUP_CONCAT(ORDERED_DATE) |
-------------------------------------------------------------------
|   1 |   A,A |                  Y,Y |      2012-05-02,2012-05-17 |
|   1 |     B |                    N |                 2012-05-05 |
|   1 |     C |                    Y |                 2012-05-07 |
|   2 |     A |                    Y |                 2012-06-12 |
|   2 |     B |                    N |                 2012-06-15 |

SQL Fiddle

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.