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.

Answer 1

If I understand what you're saying, this query will return all records with a non-null book title plus all customer records that have no valid book records, valid being defined as a book record with a non-null title.

SELECT c.id, c.name, b.title
FROM customer c
JOIN book b ON c.id = b.customer_id
WHERE b.title IS NOT NULL
UNION ALL
SELECT c.id, c.name, NULL
FROM customer c
WHERE NOT EXISTS (SELECT title FROM book b WHERE customer_id = c.id AND title IS NOT NULL)

Answer 2

You can use:

isnull(BOOKTITLE,'') = ''

on that field and a distinct, and it should give one result

Answer 3

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.

Answer 4

Let me try to clarify:

I currently have a query that joins all tables and does a filter by checking to see if the title <> ''. This query returns a single row (the book row with the valid title).

This is perfectly fine, EXCEPT in cases where all books' rows don't have a title. Then I get zero rows returned since that filter (mentioned above) filters everything out.

SO... what I want is to be able to retrieve a single row (a book row with an empty title field)... but I only want to do this IF there is not row with a valid title.

Answer 5

The three tables are below. I'm essentially trying to JOIN all columns from all tables with respect to Member_ID (or Member_Name). There are 2 members, Jane and Bryce. Basically I don't want multiple rows returned - I want a single row with all columns.

The problem is that Bryce has no email defined, yet Jane has exactly one defined and one not defined. This is causing me lots of headaches!

 First_Name  Member_ID  Office_ID
 Jane       3          1
 Bryce     4          2

Office_ID  Office_Name
1          Office Depot
2          Cemetery Hill

Member_Id  Member_Name  Email
3          Jane        [email protected]
3          Jane 
4          Bryce    
4          Bryce

Desired results:

A) If I search for jane: Row: Jane, [email protected]

B) If I search for Bryce: Row: Bryce, 'no email' (or possibly even empty string)