I'm trying to find a more elegant or old-school solution to a SQL problem:

I have 3 tables defined as follows:

CREATE TABLE Customer (CustomerID int, CustomerName varchar(50));
CREATE TABLE Customer_Account (CustomerID int, AccountID int, AccessLevel varchar(50));
CREATE TABLE Account (AccountID int, AccountName varchar(50));

So basically, the Customer_Account table explains the M:M relationship between Customer & Account.  A customer can have access to multiple accounts at my bank with various levels of access.

Because he likes wasting trees & postage, the bank manager has decided to send a report to each Customer that lists the Customer's access level to EVERY Account at the bank.

Answer #1:

DECLARE @CustomerID int; --Not needed in MySql, but definitely in T-SQL
SET @CustomerID = 5;

SELECT c.CustomerName, a.AccountName, (SELECT ca.AccessLevel FROM Customer_Account ca WHERE ca.CustomerID = c.CustomerID AND ca.AccountID = a.AccountID) AS AccessLevel
FROM Customer c, Account a
WHERE c.CustomerID = @CustomerID;

Example Output:

CustomerName	AccountName	AccessLevel
Bob Jones	BJones Checking	Owner
Bob Jones	TJones Checking	Parent
Bob Jones	SSimpson Savings	(NULL)

Anyone care to offer a better solution?  Whenever I use an inner SELECT I feel like I'm cheating because I don't know a smarter way to solve the problem.

DECLARE @CustomerID int; --Not needed in MySql, but definitely in T-SQL
SET @CustomerID = 5;

I think you put the Account & Customer_Account tables in the wrong order.  So assuming that, your query won't work because the result set wouldn't include the "SSimpson Savings" account, since Bob Jones has no relationship with that account.

Remember that our bank manager is an idiot who wants to mail each customer a report of every account.  That's why I have to do that lame inner SELECT query, to force the non-JOIN of the Customer & Account tables.

I have this nagging feeling that this would be a great time to use the EXISTS or NOT EXISTS clause, but I can't seem to get that to work.

It uses a Union but not an inner select on each row.  I will think more about it later and see if I can get it into one without a Union

You could do this

SELECT     c.CustomerName, a.AccountName, ca.AccessLevel
FROM         Customer c INNER JOIN
                      Customer_Account ca ON c.CustomerID = ca.CustomerID INNER JOIN
                      Account a ON ca.AccountID = a.AccountID
WHERE     (c.CustomerID = @CustomerId)
UNION
SELECT c.CustomerName, a.AccountName, NULL
FROM  Customer c, Account a
WHERE c.CustomerId = @CustomerId AND a.AccountId NOT IN (SELECT AccountId FROM CustomerAccount WHERE CustomerId = @CustomerId)

I've not done "development" in quite some time and certainly not with a database as nice as SQL server. With that being said, my greatly diminished SQL sense also tells me that NOT EXISTS is faster than NOT IN.

Here is a pretty cool little page that talks about SQL Optimization

http://www.bcarter.com/optimsql.htm

Here you go, one without a nested select

SELECT     c.CustomerName, a.AccountName, ca.AccessLevel
FROM         Customer c INNER JOIN
                      Customer_Account ca ON c.CustomerID = ca.CustomerID INNER JOIN
                      Account a ON ca.AccountID = a.AccountID
WHERE     (c.CustomerID = @CustomerId)
UNION
SELECT     c.CustomerName, a.AccountName, NULL
FROM         Customer c CROSS JOIN
                      Account a LEFT OUTER JOIN
                      Customer_Account ca ON c.CustomerID = ca.CustomerID AND a.AccountID = ca.AccountID
WHERE     (ca.AccessLevel IS NULL) AND (c.CustomerID = @CustomerId)

Awesome, Alex --  CROSS JOIN is the missing link I'd been looking for!!!

Clearly I wanted to query 3 tables & only do a LEFT JOIN on two of the tables.  In my SQL experience, I've never seen anyone use CROSS JOIN, so I had no idea that it mirrored the illegal syntax I wanted to use ("Account a, Customer c LEFT JOIN Customer_Account ca")

Now that you've shown me a good use of the CROSS JOIN, I think I can do you one better:

SELECT c.CustomerName, a.AccountName, ca.AccessLevel
FROM Customer c CROSS JOIN Account a LEFT JOIN Customer_Account ca ON a.AccountID = ca.AccountID AND c.CustomerID = ca.CustomerID
WHERE c.CustomerID = @CustomerID

This code has been tested & it works beautifully.

YOU MEAN, YOU DIDN'T KNOW!!!!

Seriously though, you were using one, you just didn't know, it is just a join without a where or ON qualifier that links them through a FK.

Wihtout rewrite it is this

SELECT c.CustomerName, a.AccountName, ca.AccessLevel
FROM Customer c, Account a
   LEFT JOIN Customer_Account ca ON a.AccountID = ca.AccountID AND c.CustomerID = ca.CustomerID
WHERE c.CustomerID = @CustomerID

I knew there was a way with one query.  This is extreme coding at its best.

Huh, I didn't realize you could mix the comma & JOIN syntaxes in the same query...

Thanks for the input, all.  Hopefully we all learned something today...

Name

E-mail (optional)

Website (optional)