Noobie to GROUP_CONCAT...

[geudrik]

Alright, it's a been a while since I've posted for help here, but I suppose new learning brings new challenges

 

I'm trying to learn how to use GROUP_CONCAT (which I think is what I should be using to get, using the tables below, a list of names of people who do NOT own an a4).

 

CREATE TABLE IF NOT EXISTS `Owns` (
  `cid` int(11) NOT NULL DEFAULT '0',
  `make` varchar(15) NOT NULL DEFAULT '',
  `model` varchar(15) NOT NULL DEFAULT '',
  `color` char(10) DEFAULT NULL,
  `marketValue` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`cid`,`make`,`model`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Owns`
--

INSERT INTO `Owns` (`cid`, `make`, `model`, `color`, `marketValue`) VALUES
(1, 'bmw', '323i', 'red', '20000.00'),
(1, 'audi', 'q7', 'black', '40000.00'),
(1, 'audi', 'a4', 'white', '30000.00'),
(2, 'audi', 'q7', 'pink', '40000.00'),
(3, 'audi', 'a4', 'silver', '30000.00'),
(5, 'subaru', 'outback', 'green', '10000.00'),
(5, 'ford', 'f100', 'green', '25000.00'),
(6, 'mercedes', 'ml', 'white', '50000.00'),
(7, 'mercedes', 'ml', 'brown', '40000.00'),
(8, 'mercedes', 'ml', 'brown', '20000.00'),
(8, 'subaru', 'outback', 'brown', '10000.00'),
(8, 'aston martin', 'bb9', 'yellow', '10000.00');


-- Table structure for table `Customers`
--

CREATE TABLE IF NOT EXISTS `Customers` (
  `cid` int(11) NOT NULL,
  `cname` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Customers`
--

INSERT INTO `Customers` (`cid`, `cname`, `age`) VALUES
(1, 'john', 20),
(2, 'mary', 18),
(3, 'jane', 28),
(4, 'ann', 40),
(5, 'joyce', 33),
(6, 'terry', 25),
(7, 'claire', 80),
(8, 'bob', 60);

 

The query that I have been screwing around with is:

SELECT Customers.cname, GROUP_CONCAT(Owns.model) AS Cars
FROM Customers
INNER JOIN Owns on Customers.cid=Owns.cid
WHERE Owns.model != "a4"

Which dumps out a single-row table with ALL models in one cell, and just the first owner...

What am I doing wrong here? :s

[mikosiko]

GROUP_CONCAT(expr)

 

This function returns a string result with the concatenated non-NULL values from a group

 

does it ring a bell?

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[fenway]

You should be using LEFT JOIN... IS NULL.