Jump to content

Trying to understand query

fsmo

By fsmo
in MySQL Help

 Share

Recommended Posts

fsmo Newbie

fsmo

Posted
Posted (edited)

I am using the Henry Database schema which the code is down below.   But in the query I do not understand the t values(t1,t2,t3 and t4).  I have also included the question to the query.  I need someone to dumb it down for me.

 

Question - . For each book with coauthors, list the title, publisher code, type, and author names (in the order listed on the cover).
 

Answer:

SELECT 
     Book.Title, 
     Publisher.PublisherCode,
     Book.Type,
     t3.AuthorLast,
     t3.AuthorFirst,
     t4.AuthorLast,
     t4.AuthorFirst
FROM Book,
     Publisher,
     Wrote t1,
     Wrote t2,
     Author t3,
     Author t4 
WHERE 
     Book.BookCode=t1.BookCode AND 
     Book.BookCode=t2.BookCode AND 
     t1.AuthorNum=t3.AuthorNum AND 
     t2.AuthorNum=t4.AuthorNum AND
      Book.PublisherCode=Publisher.PublisherCode
      AND t3.AuthorNum != t4.AuthorNum 
GROUP BY Book.Title;

Code to make schema:

CREATE DATABASE HENRY;

USE HENRY;

CREATE TABLE Author
(AuthorNum DECIMAL(2,0) PRIMARY KEY,
AuthorLast CHAR(12),
AuthorFirst CHAR(10) )
;

CREATE TABLE Book
(BookCode CHAR(4) PRIMARY KEY,
Title CHAR(40),
PublisherCode CHAR(3),
Type CHAR(3),
Paperback CHAR(1) )
;

CREATE TABLE Branch
(BranchNum DECIMAL(2,0) PRIMARY KEY,
BranchName CHAR(50),
BranchLocation CHAR(50) )
;

CREATE TABLE Copy
(BookCode CHAR(4),
BranchNum DECIMAL(2,0),
CopyNum DECIMAL(2,0),
Quality CHAR(20),
Price DECIMAL(8,2),
PRIMARY KEY (BookCode, BranchNum, CopyNum) )
;

CREATE TABLE Publisher
(PublisherCode CHAR(3) PRIMARY KEY,
PublisherName CHAR(25),
City CHAR(20) )
;

CREATE TABLE Wrote
(BookCode CHAR(4),
AuthorNum DECIMAL(2,0),
Sequence DECIMAL(2,0),
PRIMARY KEY (BookCode, AuthorNum) )
;

INSERT INTO Author
VALUES
(1,'Morrison','Toni');
INSERT INTO Author
VALUES
(2,'Solotaroff','Paul');
INSERT INTO Author
VALUES
(3,'Vintage','Vernor');
INSERT INTO Author
VALUES
(4,'Francis','Dick');
INSERT INTO Author
VALUES
(5,'Straub','Peter');
INSERT INTO Author
VALUES
(6,'King','Stephen');
INSERT INTO Author
VALUES
(7,'Pratt','Philip');
INSERT INTO Author
VALUES
(8,'Chase','Truddi');
INSERT INTO Author
VALUES
(9,'Collins','Bradley');
INSERT INTO Author
VALUES
(10,'Heller','Joseph');
INSERT INTO Author
VALUES
(11,'Wills','Gary');
INSERT INTO Author
VALUES
(12,'Hofstadter','Douglas R.');
INSERT INTO Author
VALUES
(13,'Lee','Harper');
INSERT INTO Author
VALUES
(14,'Ambrose','Stephen E.');
INSERT INTO Author
VALUES
(15,'Rowling','J.K.');
INSERT INTO Author
VALUES
(16,'Salinger','J.D.');
INSERT INTO Author
VALUES
(17,'Heaney','Seamus');
INSERT INTO Author
VALUES
(18,'Camus','Albert');
INSERT INTO Author
VALUES
(19,'Collins, Jr.','Bradley');
INSERT INTO Author
VALUES
(20,'Steinbeck','John');
INSERT INTO Author
VALUES
(21,'Castelman','Riva');
INSERT INTO Author
VALUES
(22,'Owen','Barbara');
INSERT INTO Author
VALUES
(23,'O''Rourke','Randy');
INSERT INTO Author
VALUES
(24,'Kidder','Tracy');
INSERT INTO Author
VALUES
(25,'Schleining','Lon');
INSERT INTO Book
VALUES
('0180','A Deepness in the Sky','TB','SFI','Y');
INSERT INTO Book
VALUES
('0189','Magic Terror','FA','HOR','Y');
INSERT INTO Book
VALUES
('0200','The Stranger','VB','FIC','Y');
INSERT INTO Book
VALUES
('0378','Venice','SS','ART','N');
INSERT INTO Book
VALUES
('079X','Second Wind','PU','MYS','N');
INSERT INTO Book
VALUES
('0808','The Edge','JP','MYS','Y');
INSERT INTO Book
VALUES
('1351','Dreamcatcher: A Novel','SC','HOR','N');
INSERT INTO Book
VALUES
('1382','Treasure Chests','TA','ART','N');
INSERT INTO Book
VALUES
('138X','Beloved','PL','FIC','Y');
INSERT INTO Book
VALUES
('2226','Harry Potter and the Prisoner of Azkaban','ST','SFI','N');
INSERT INTO Book
VALUES
('2281','Van Gogh and Gauguin','WP','ART','N');
INSERT INTO Book
VALUES
('2766','Of Mice and Men','PE','FIC','Y');
INSERT INTO Book
VALUES
('2908','Electric Light','FS','POE','N');
INSERT INTO Book
VALUES
('3350','Group: Six People in Search of a Life','BP','PSY','Y');
INSERT INTO Book
VALUES
('3743','Nine Stories','LB','FIC','Y');
INSERT INTO Book
VALUES
('3906','The Soul of a New Machine','BY','SCI','Y');
INSERT INTO Book
VALUES
('5163','Travels with Charley','PE','TRA','Y');
INSERT INTO Book
VALUES
('5790','Catch-22','SC','FIC','Y');
INSERT INTO Book
VALUES
('6128','Jazz','PL','FIC','Y');
INSERT INTO Book
VALUES
('6328','Band of Brothers','TO','HIS','Y');
INSERT INTO Book
VALUES
('669X','A Guide to SQL','CT','CMP','Y');
INSERT INTO Book
VALUES
('6908','Franny and Zooey','LB','FIC','Y');
INSERT INTO Book
VALUES
('7405','East of Eden','PE','FIC','Y');
INSERT INTO Book
VALUES
('7443','Harry Potter and the Goblet of Fire','ST','SFI','N');
INSERT INTO Book
VALUES
('7559','The Fall','VB','FIC','Y');
INSERT INTO Book
VALUES
('8092','Godel, Escher, Bach','BA','PHI','Y');
INSERT INTO Book
VALUES
('8720','When Rabbit Howls','JP','PSY','Y');
INSERT INTO Book
VALUES
('9611','Black House','RH','HOR','N');
INSERT INTO Book
VALUES
('9627','Song of Solomon','PL','FIC','Y');
INSERT INTO Book
VALUES
('9701','The Grapes of Wrath','PE','FIC','Y');
INSERT INTO Book
VALUES
('9882','Slay Ride','JP','MYS','Y');
INSERT INTO Book
VALUES
('9883','The Catcher in the Rye','LB','FIC','Y');
INSERT INTO Book
VALUES
('9931','To Kill a Mockingbird','HC','FIC','N');
INSERT INTO Branch
VALUES
(1,'Henry Downtown','16 Riverview');
INSERT INTO Branch
VALUES
(2,'Henry on the Hill','1289 Bedford');
INSERT INTO Branch
VALUES
(3,'Henry Brentwood','Brentwood Mall');
INSERT INTO Branch
VALUES
(4,'Henry Eastshore','Eastshore Mall');
INSERT INTO Copy
VALUES
('0180',1,1,'Excellent',7.19);
INSERT INTO Copy
VALUES
('0180',1,2,'Excellent',7.19);
INSERT INTO Copy
VALUES
('0189',2,1,'Excellent',7.99);
INSERT INTO Copy
VALUES
('0189',2,2,'Good',5.99);
INSERT INTO Copy
VALUES
('0200',1,1,'Excellent',8.00);
INSERT INTO Copy
VALUES
('0200',2,1,'Excellent',8.00);
INSERT INTO Copy
VALUES
('0200',2,2,'Fair',3.50);
INSERT INTO Copy
VALUES
('0200',2,3,'Poor',2.25);
INSERT INTO Copy
VALUES
('0378',3,1,'Excellent',24.50);
INSERT INTO Copy
VALUES
('0378',3,2,'Excellent',24.50);
INSERT INTO Copy
VALUES
('079X',2,1,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',3,1,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',3,2,'Good',19.95);
INSERT INTO Copy
VALUES
('079X',4,1,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',4,2,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',4,3,'Good',19.95);
INSERT INTO Copy
VALUES
('0808',2,1,'Excellent',7.99);
INSERT INTO Copy
VALUES
('1351',2,1,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',2,2,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',2,3,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',2,4,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',3,1,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',3,2,'Good',13.95);
INSERT INTO Copy
VALUES
('1382',2,1,'Good',34.50);
INSERT INTO Copy
VALUES
('138X',2,1,'Excellent',12.95);
INSERT INTO Copy
VALUES
('138X',2,2,'Excellent',12.95);
INSERT INTO Copy
VALUES
('138X',2,3,'Good',6.95);
INSERT INTO Copy
VALUES
('2226',1,1,'Excellent',14.96);
INSERT INTO Copy
VALUES
('2226',1,2,'Excellent',14.96);
INSERT INTO Copy
VALUES
('2226',1,3,'Good',8.95);
INSERT INTO Copy
VALUES
('2226',3,1,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2226',3,2,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2226',4,1,'Fair',3.95);
INSERT INTO Copy
VALUES
('2281',4,1,'Excellent',21.00);
INSERT INTO Copy
VALUES
('2766',3,1,'Excellent',7.95);
INSERT INTO Copy
VALUES
('2766',3,2,'Good',3.95);
INSERT INTO Copy
VALUES
('2908',1,1,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2908',1,2,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2908',1,3,'Good',8.50);
INSERT INTO Copy
VALUES
('2908',4,1,'Good',8.50);
INSERT INTO Copy
VALUES
('3350',1,1,'Excellent',10.40);
INSERT INTO Copy
VALUES
('3350',1,2,'Excellent',10.40);
INSERT INTO Copy
VALUES
('3743',2,1,'Excellent',5.99);
INSERT INTO Copy
VALUES
('3906',2,1,'Excellent',12.16);
INSERT INTO Copy
VALUES
('3906',3,1,'Excellent',12.16);
INSERT INTO Copy
VALUES
('3906',3,2,'Good',4.50);
INSERT INTO Copy
VALUES
('5163',1,1,'Excellent',7.95);
INSERT INTO Copy
VALUES
('5790',4,1,'Excellent',12.00);
INSERT INTO Copy
VALUES
('5790',4,2,'Good',5.95);
INSERT INTO Copy
VALUES
('6128',2,1,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',2,2,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',2,3,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',2,4,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',3,1,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',3,2,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',3,3,'Good',4.75);
INSERT INTO Copy
VALUES
('6328',2,1,'Excellent',9.95);
INSERT INTO Copy
VALUES
('6328',2,2,'Excellent',9.95);
INSERT INTO Copy
VALUES
('669X',1,1,'Excellent',39.95);
INSERT INTO Copy
VALUES
('669X',2,1,'Excellent',39.95);
INSERT INTO Copy
VALUES
('6908',2,1,'Excellent',5.99);
INSERT INTO Copy
VALUES
('6908',2,2,'Excellent',5.99);
INSERT INTO Copy
VALUES
('7405',3,1,'Good',5.00);
INSERT INTO Copy
VALUES
('7405',3,2,'Fair',2.95);
INSERT INTO Copy
VALUES
('7443',4,1,'Good',9.25);
INSERT INTO Copy
VALUES
('7559',2,1,'Fair',3.65);
INSERT INTO Copy
VALUES
('7559',2,2,'Good',8.00);
INSERT INTO Copy
VALUES
('8092',3,1,'Good',9.50);
INSERT INTO Copy
VALUES
('8720',1,1,'Excellent',6.29);
INSERT INTO Copy
VALUES
('8720',1,2,'Excellent',6.29);
INSERT INTO Copy
VALUES
('8720',1,3,'Good',3.95);
INSERT INTO Copy
VALUES
('9611',1,1,'Excellent',18.81);
INSERT INTO Copy
VALUES
('9611',1,2,'Good',8.25);
INSERT INTO Copy
VALUES
('9627',3,1,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,2,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,3,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,4,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,5,'Good',6.50);
INSERT INTO Copy
VALUES
('9627',4,1,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',4,2,'Good',6.50);
INSERT INTO Copy
VALUES
('9701',1,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',1,2,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',2,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',3,1,'Fair',4.00);
INSERT INTO Copy
VALUES
('9701',3,2,'Fair',4.00);
INSERT INTO Copy
VALUES
('9701',3,3,'Good',7.25);
INSERT INTO Copy
VALUES
('9701',4,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',4,2,'Poor',1.55);
INSERT INTO Copy
VALUES
('9882',3,1,'Excellent',6.99);
INSERT INTO Copy
VALUES
('9882',3,2,'Good',3.75);
INSERT INTO Copy
VALUES
('9882',3,3,'Excellent',6.99);
INSERT INTO Copy
VALUES
('9883',2,1,'Excellent',5.99);
INSERT INTO Copy
VALUES
('9883',2,2,'Excellent',5.99);
INSERT INTO Copy
VALUES
('9883',2,3,'Fair',1.95);
INSERT INTO Copy
VALUES
('9883',4,1,'Good',3.99);
INSERT INTO Copy
VALUES
('9883',4,2,'Excellent',5.99);
INSERT INTO Copy
VALUES
('9931',1,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9931',1,2,'Excellent',13.00);
INSERT INTO Publisher
VALUES
('AH','Arkham House','Sauk City WI');
INSERT INTO Publisher
VALUES
('AP','Arcade Publishing','New York');
INSERT INTO Publisher
VALUES
('BA','Basic Books','Boulder CO');
INSERT INTO Publisher
VALUES
('BP','Berkley Publishing','Boston');
INSERT INTO Publisher
VALUES
('BY','Back Bay Books','New York');
INSERT INTO Publisher
VALUES
('CT','Course Technology','Boston');
INSERT INTO Publisher
VALUES
('FA','Fawcett Books','New York');
INSERT INTO Publisher
VALUES
('FS','Farrar Straus and Giroux','New York');
INSERT INTO Publisher
VALUES
('HC','HarperCollins Publishers','New York');
INSERT INTO Publisher
VALUES
('JP','Jove Publications','New York');
INSERT INTO Publisher
VALUES
('JT','Jeremy P. Tarcher','Los Angeles');
INSERT INTO Publisher
VALUES
('LB','Lb Books','New York');
INSERT INTO Publisher
VALUES
('MP','McPherson and Co.','Kingston');
INSERT INTO Publisher
VALUES
('PE','Penguin USA','New York');
INSERT INTO Publisher
VALUES
('PL','Plume','New York');
INSERT INTO Publisher
VALUES
('PU','Putnam Publishing Group','New York');
INSERT INTO Publisher
VALUES
('RH','Random House','New York');
INSERT INTO Publisher
VALUES
('SB','Schoken Books','New York');
INSERT INTO Publisher
VALUES
('SC','Scribner','New York');
INSERT INTO Publisher
VALUES
('SS','Simon and Schuster','New York');
INSERT INTO Publisher
VALUES
('ST','Scholastic Trade','New York');
INSERT INTO Publisher
VALUES
('TA','Taunton Press','Newtown CT');
INSERT INTO Publisher
VALUES
('TB','Tor Books','New York');
INSERT INTO Publisher
VALUES
('TH','Thames and Hudson','New York');
INSERT INTO Publisher
VALUES
('TO','Touchstone Books','Westport CT');
INSERT INTO Publisher
VALUES
('VB','Vintage Books','New York');
INSERT INTO Publisher
VALUES
('WN','W.W. Norton','New York');
INSERT INTO Publisher
VALUES
('WP','Westview Press','Boulder CO');
INSERT INTO Wrote
VALUES
('0180',3,1);
INSERT INTO Wrote
VALUES
('0189',5,1);
INSERT INTO Wrote
VALUES
('0200',18,1);
INSERT INTO Wrote
VALUES
('0378',11,1);
INSERT INTO Wrote
VALUES
('079X',4,1);
INSERT INTO Wrote
VALUES
('0808',4,1);
INSERT INTO Wrote
VALUES
('1351',6,1);
INSERT INTO Wrote
VALUES
('1382',23,2);
INSERT INTO Wrote
VALUES
('1382',25,1);
INSERT INTO Wrote
VALUES
('138X',1,1);
INSERT INTO Wrote
VALUES
('2226',15,1);
INSERT INTO Wrote
VALUES
('2281',9,2);
INSERT INTO Wrote
VALUES
('2281',19,1);
INSERT INTO Wrote
VALUES
('2766',20,1);
INSERT INTO Wrote
VALUES
('2908',17,1);
INSERT INTO Wrote
VALUES
('3350',2,1);
INSERT INTO Wrote
VALUES
('3743',16,1);
INSERT INTO Wrote
VALUES
('3906',24,1);
INSERT INTO Wrote
VALUES
('5163',20,1);
INSERT INTO Wrote
VALUES
('5790',10,1);
INSERT INTO Wrote
VALUES
('6128',1,1);
INSERT INTO Wrote
VALUES
('6328',14,1);
INSERT INTO Wrote
VALUES
('669X',7,1);
INSERT INTO Wrote
VALUES
('6908',16,1);
INSERT INTO Wrote
VALUES
('7405',20,1);
INSERT INTO Wrote
VALUES
('7443',15,1);
INSERT INTO Wrote
VALUES
('7559',18,1);
INSERT INTO Wrote
VALUES
('8092',12,1);
INSERT INTO Wrote
VALUES
('8720',8,1);
INSERT INTO Wrote
VALUES
('9611',5,2);
INSERT INTO Wrote
VALUES
('9611',6,1);
INSERT INTO Wrote
VALUES
('9627',1,1);
INSERT INTO Wrote
VALUES
('9701',20,1);
INSERT INTO Wrote
VALUES
('9882',4,1);
INSERT INTO Wrote
VALUES
('9883',16,1);
INSERT INTO Wrote
VALUES
('9931',13,1);
Edited by Zane
Link to comment
https://forums.phpfreaks.com/topic/298581-trying-to-understand-query/
Share on other sites
Zane Prolific Member

Zane

Posted
Posted 

SELECT
    Book.Title,
    Publisher.PublisherCode,
    Book.Type,
    t3.AuthorLast,
    t3.AuthorFirst,
    t4.AuthorLast,
    t4.AuthorFirst
The t1,t2,etc.. are variables (so to speak) referring to a table. You can see from the pattern above that the notation for a field name is

[tableName].[fieldName]

 

The database name can also be prepended, but it's not necessary unless you're doing queries with several databases.

[databaseName].[tableName].[fieldName]

 

FROM Book,
    Publisher,
    Wrote t1, 
    Wrote t2,
    Author t3,
    Author t4
This is where those variables or aliases have been declared. t1 is an alias for the Wrote table. For some reason your query queries from two different tables twice, (Wrote and Author), then Publisher, and Book

 

Notice how there are not t's after Publisher and Book. It only means that they have not been aliased. If the t1 and t2 were not present in the above code, you would have to change your reference t1.BookCode to Wrote.BookCode

 

WHERE
     Book.BookCode=t1.BookCode AND
     Book.BookCode=t2.BookCode AND
     t1.AuthorNum=t3.AuthorNum AND
     t2.AuthorNum=t4.AuthorNum AND
     Book.PublisherCode=Publisher.PublisherCode
     AND t3.AuthorNum != t4.AuthorNum

GROUP BY Book.Title;
benanamen Prolific Member

benanamen

Posted
Posted (edited)

The t1, t2 etc are specifically ALIASES. Say your name is mike(table_name) but your wife calls you bigdaddy(alias).

 

It is a shortcut so you dont have repeatedly type out the whole table name.

 

Example:

SELECT shortname.id, shortname.firstname FROM mysuperlongtable AS shortname

 

Instead of

SELECT mysuperlongtable.id, mysuperlongtable.firstname FROM mysuperlongtable

 

And in practice

SELECT s.id, s.firstname FROM mysuperlongtable AS s

 

The ALIAS can be anything you want it to be and is generally only one or two characters

 

I have always found the t1, t2 way to generic and undescript. If the table is users, I would use the ALIAS u, if the table is books. I would use the ALIAS b

 

 

Alias used to confuse me too, especially because of t1, t2

Edited by benanamen
Jacques1 Prolific Member

Jacques1

Posted
Posted

The query is nonsensical and very poorly written, so I'd consider just ignoring it and moving to a better learning resource. In any case, don't adopt this style.

  • The result set isn't even deterministic, because the query groups by the book title and then selects columns which aren't functionally dependend on the title (like the publisher). For example: If two books have the same title but different publishers, who is “the” publisher of the title? This obviously makes no sense, and a properly configured MySQL server will actually throw an error. If you do not get an error, that's because MySQL ignores your mistake and selects any publisher. Nondeterministic results are generally a bad thing when dealing with data.
  • The GROUP BY clause is meant for aggregate functions (like SUM or AVG). Since there are no aggregate functions anywhere in the query, the clause simply doesn't belong there. It's appearently being abused to hide the problem just mentioned.
  • Do not use FaNcY identifiers with both lowercase and uppercase letters. They may be pretty, but they're extremely confusing and error-prone, because different operating systems handle them differently. While I tested the query, I got this wrong all the time. Make your identifiers all-lowercase and separate them with underscores. The database name should be all-lowercase as well.
  • Never, ever use cryptic aliases like “t1” and “t2” (like benanamen already said). Once the query becomes more complex, you'll hate yourself for it. And should you ever work in a team, everybody else will hate you as well.
  • Using comma-separated tables in the FROM clause is obsolete and comes with a lot of limitations. Always use explicit joins like “book JOIN publisher ON book.publisher_code = publisher.publisher_code”.

A sanitized version of the query might look like this:

SELECT
  book.code,
  book.title,
  book.type,
  publisher.code,
  author.last_name,
  author.first_name
FROM
  book
  JOIN publisher ON book.publisher_code = publisher.code
  JOIN wrote ON book.code = wrote.book_code
  JOIN author ON wrote.author_number = author.number
;

This would select all books together with the publisher and the authors. Of course you need to fix your column names before you can actually run the query, but you get the idea.

  • Like 1
Barand Prolific Member

Barand

Posted
Posted

Agreed, the solution is poorly written and does not address the final part of the question (order listed on the cover)

 

My solution would be

SELECT 
    b.Title
  , b.PublisherCode
  , b.Type
  , GROUP_CONCAT(a.AuthorFirst,' ',a.AuthorLast ORDER BY w.Sequence SEPARATOR ', ') as WrittenBy
 FROM 
    book b
  INNER JOIN
    wrote w ON b.BookCode = w.BookCode
  INNER JOIN
    author a ON w.AuthorNum = a.AuthorNum
 GROUP BY b.BookCode
 HAVING COUNT(a.AuthorNum) > 1;
+----------------------+---------------+------+---------------------------------------+
| Title                | PublisherCode | Type | WrittenBy                             |
+----------------------+---------------+------+---------------------------------------+
| Treasure Chests      | TA            | ART  | Lon Schleining, Randy O'Rourke        |
| Van Gogh and Gauguin | WP            | ART  | Bradley Collins, Jr., Bradley Collins |
| Black House          | RH            | HOR  | Stephen King, Peter Straub            |
+----------------------+---------------+------+---------------------------------------+
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.