Sql distinct selective row after a join question

Question

Let's say i have 2 tables Customer and Books.

Table Books can have multiple rows that pertain to a row in Customer.

Ex: customer John Doe has an id of 1000. Table Books has 2 rows with a member_id column with 1000 (John Doe).

These 2 rows are identical EXCEPT one of the fields (book title) is empty. The other row has a value for a title.

Question: How can i query this so that I retrieve the row with the valid title value, BUT, if both title values are empty, then it just returns a single row?

I certainly hope that made sense.

Solution

You should be able to use a distinct operator:

SELECT DISTINCT Customers.member_id, Books.book_title
FROM Customers
INNER JOIN Books ON Customers.member_id = Books.member_id

If that does not work correctly you could use an inner select:

SELECT DISTINCT *
FROM (SELECT Customers.member_id, Books.book_title
      FROM Customers
      INNER JOIN Books ON Customers.member_id = Books.member_id) As newTable

Also, if this is a frequently used query I would avoid a UNION because they are known to have performance problems.

In response to the edit:

SELECT Customers.member_id, Customer_Info.Name, ISNULL(newTable.book_title, '')
FROM Customers
INNER JOIN Customer_Info
LEFT JOIN (SELECT DISTINCT member_id, book_title FROM Books) newTable
    ON newTable.member_ID = Customers.member_id

This should return all books associated with a customer (but only one time for each title and if no books are found then it will return an empty string.) If this does not answer the question please include some additional information about the tables and an example of the result you would like and I will update.

OK, now I think I know what you are looking for:

Here is a possible query based on your original question using the tables provided. However, it will not work if the customer has two distinct e-mail addresses set; in that case, you could add a TOP(1) to ensure only one result but you won't know if it is the "right result."

SELECT Customers.member_id, Office.Office_Name, ISNULL(newTable.email, '')
FROM Customers
INNER JOIN Office
LEFT JOIN (SELECT DISTINCT member_id, email 
           FROM Books 
           WHERE email IS NOT NULL AND email <> '') newTable
    ON newTable.member_ID = Customers.member_id

Here is another query based on the data you provided and the example output.

SELECT Member_Name, Email
FROM thridTable
WHERE Member_Name = @SomeInputParameter

I'm not sure how representative your sample data is but why would you be storing the member name in more than one table? That is a guaranteed headache in the future.