sql, join two tables

SELECT user.login, book.name FROM user LEFT JOIN book ON user.login = book.author WHERE user.login = 'peter'

Now i get:

peter book1
peter book2
peter book2

Bu i wish get:

peter book1
      book2
      book2

Database: MySQL T开发者_如何转开发hanks

---

I agree with others that while this is something that can be performed in SQL, it really shouldn't be.

The following assumes that there can be only one author per book, which is not the case in reality:

SELECT x.author,
       x.book_name
  FROM (SELECT CASE 
                 WHEN @author != u.login THEN u.login
                 ELSE ''
               END AS author, 
               b.name AS book_name,
               @author := u.login AS set_variable  
          FROM USER u
     LEFT JOIN BOOK b ON b.author = u.login
          JOIN (SELECT @author := '') r
         WHERE u.login = 'peter'
      ORDER BY u.login) x

It uses a variable (only MySQL supports this, to my knowledge) to store the author/login value. The CASE expression is set to return the column value if it does not match what is currently stored in the variable. If the variable and the column value match, a zero length string will occupy the column value - you could change this to be NULL if you like.

I included an ORDER BY in the likelihood you'd want this to happen for multiple names, because there's no way to guarantee data order without an ORDER BY.

I had to use a subquery, otherwise, the column where the variable gets set would appear in your resultset.

---

You can't do it that way...

Book 2 has an author, why would it show a blank?

Why exactly do you want your output to be like that? There might be a better way to do what you need and if you explain it a little better we could probably help.

:-)

---

Each line of a SQL request should be seen as a independent line. The way you want your output implies that book2 and book3 have no author (which is not true).

Since you don't want to repeat data in the author column and this column is always the same, you could not print this column

SELECT book.name
FROM book
WHERE user.login = 'peter'

you would get

book1
book2
book2

---

In MySQL you can do.

SELECT user.login, group_concat(book.name  SEPARATOR '\n') AS book
FROM user
LEFT JOIN book ON
user.login = book.author
WHERE user.login = 'peter'
GROUP BY user.login

Is this what you need?

---

i have seen this as a common request from end users that do not want to repeat column values.

you do not specify what database you are using, so i will offer an Oracle answer.

you can use the LAG function to check if the LAG value equals the current value, and if so, replace it with a NULL.

---

It depends on how your database is structured and the data that is actually held in the database. From the looks of the query, I am not sure that the result you would like is possible.

I think the problem is that you are selecting user.login from the user table, this will always exist because if it doesn't, the query will return no results at all (ie. the join is dependent on the user.login containing a record). Even if you tried using other JOIN methods, I think that you will always get the user.login returned. If you try to change the query so that you are looking for book.author instead, this will have the same effect since the query is dependent on the fact that user.login and book.author are the same.

There very well could be someone that is much more experienced with SQL that can give an answer, but to my knowledge, the name will always be returned.

Maybe if you give some insight into what else is happening, we might be able to suggest an alternative.

TL;DR: Because user.login and book.author are the same, it isn't possible to get the desired result.