[RESOLVED] Remove duplicate row

[bumbar]

Hallo!
I am new in MySQL, and I want ask some question.
I have three tables. This is my very simple Catalog.
I have tables for Authors, their Books and map table - Authors-Books.

Here how looks tables structure:

CREATE TABLE author (
id int(4) NOT NULL auto_increment,
name text NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO author (id, name) VALUES
(1, 'Brad Phillips'),
(2, 'Don Charles'),
(3, 'Kur Silver');

CREATE TABLE book (
id int(4) NOT NULL auto_increment,
name text NOT NULL,
PRIMARY KEY (id)
)

INSERT INTO book (id, name) VALUES
(1, 'MySQL in a bucket '),
(2, 'Databases for Delinquents'),
(3, 'Design Patterns'),
(4, 'PHP Professional'),
(5, 'Java Script Programming');

CREATE TABLE book_author (
book_id int(4) NOT NULL default '0',
author_id int(4) NOT NULL default '0'
);

INSERT INTO book_author (book_id, author_id) VALUES
(1, 1), (1, 2), (2, 3), (4, 1), (3, 1), (5, 2);

When I use select:

$select = "
SELECT DISTINCT
a.name AS author,
b.name AS book,
a.id AS author_id,
b.name AS book_id

FROM
author AS a,
book AS b,
book_author AS ba

WHERE
a.id = ba.author_id
AND
b.id = ba.book_id

GROUP BY
a.name,
b.name

";

I retrieve this:

Brad Phillips-----Design Patterns
Brad Phillips-----MySQL in a bucket
Brad Phillips-----PHP Proffesional
Don Charles-----Java Script Programming
Don Charles-----MySQL in a bucket
Kur Silver-------Databases for Delinquents

I want remove duplicate author and get output like this:

Brad Phillips-----Design Patterns
------------------MySQL in a bucket
------------------PHP Proffesional
Don Charles-----Java Script Programming
------------------MySQL in a bucket
Kur Silver--------Databases for Delinquents

Thankx!

[bradgrafelman]

Sounds like your SQL query is fine.. you just need to do some display formatting in PHP.

I don't know how you're displaying this, but the general idea would be to loop through the MySQL result set after you've initialized an $author variable in this manner:

1. Check if the current author (e.g. $row['author']) is equal to the previous author displayed ($author).
   --- If different, output their name (in whatever formatting you want) and update $author with $row['author'] (current author)
   --- If it's the same, continue...
2. Output the rest of the data you want from that row.. book name, whatever
3. Repeat loop.

Basically, you're telling PHP to display the author's name only if it there's a change in authors.

[bogu]

U can do it this way if your mysql version is >= 4.1

SELECT
	a.name, GROUP_CONCAT(DISTINCT b.name ORDER BY b.name SEPARATOR "\r\n") as books
FROM book_author ba
LEFT JOIN author a ON ba.author_id = a.id
LEFT JOIN book b ON ba.book_id = b.id
GROUP BY a.name

[bumbar]

Thank you, mans!

Incredibly, Bogu! This work perfect!