table alias query dilemma - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

table alias query dilemma

Author

Topic

a2l2en
Starting Member

11 Posts

Posted - 2007-06-27 : 18:13:54

I have a table with customer information.

CustID, Name, email, contract_id
222, Joy, joy@mail.com, Cash
222, Joy, joy@mail.com, Credit
444, Bob, bob@mail.com, Cash
123, Jon, jon@mio.com, Cash
123, Jon, jon@mio.com, Credit
123, Jon, jon@mio.com, Check

There can only be three values in the contract_id column - Cash, Credit or Check. All customers have at least "Cash" for contract_id, however, they can have additional records for Credit or Check.

I don't have write access to the database so I have to write a query that separates them out. I've tried to create table aliases and do outer joins but it results in only giving me back the customers that have cash, credit and check. I miss all other combinations...

Any suggestions?

Results need to look like this:

CustID| Cash| Credit| Check
******************************
123 cash credit check
444 cash null null
222 cash credit null

I only have read access and need a query to do this. Help :-)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-27 : 18:30:08

Here is one way:
[CODE]SELECT
CustID,
CASE
WHEN EXISTS (SELECT 1 FROM @T WHERE Contract_ID = 'Cash' AND CustID = t.CustID) THEN 'Cash'
END AS Cash,
CASE
WHEN EXISTS (SELECT 1 FROM @T WHERE Contract_ID = 'Credit' AND CustID = t.CustID) THEN 'Credit'
END AS Credit,
CASE
WHEN EXISTS (SELECT 1 FROM @T WHERE Contract_ID = 'Check' AND CustID = t.CustID) THEN 'Check'
END AS "Check"
FROM
@T t
GROUP BY
CustID [/CODE]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-27 : 18:39:12

Here is another way with joins (hehe, got bored wating on a job to complete):

SELECT
    DISTINCT(t.CustID),
    CASE
        WHEN t.Contract_ID = 'Cash' THEN 'Cash'
    END AS Cash,
    CASE
        WHEN t1.Contract_ID = 'Credit' THEN 'Credit'
    END AS Credit,
    CASE
        WHEN t2.Contract_ID = 'Check' THEN 'Check'
    END AS "Check"
FROM
    @T t
LEFT OUTER JOIN
	@T t1
	ON t.CustID = t1.CustID
	AND t.Contract_ID = 'cash'
	AND t1.Contract_ID = 'credit'
LEFT OUTER JOIN
	@T t2
	ON t.CustID = t2.CustID
	AND t.Contract_ID = 'cash'
	AND t2.Contract_ID = 'check'
WHERE
	t.Contract_ID = 'Cash'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-28 : 02:20:14

[code]SELECT CustID,
MAX(CASE WHEN Contract_ID = 'Cash' THEN 'Cash' END) AS Cash,
MAX(CASE WHEN Contract_ID = 'Credit' THEN 'Credit' END) AS Credit,
MAX(CASE WHEN Contract_ID = 'Check' THEN 'Check' END) AS [Check]
FROM CustomerInformationTable
GROUP BY CustID
ORDER BY CustID[/code]

Peter Larsson
Helsingborg, Sweden

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 02:58:28

We know that 'Cash' exists for each cutomer.

SELECT A.CustID, 'Cash',
CASE WHEN EXISTS (SELECT COUNT(*) FROM CustomerInformationTable Z WHERE Z.CustID = A.CustID AND Z.Contract_ID = 'Credit')THEN 'Credit' END,
CASE WHEN EXISTS (SELECT COUNT(*) FROM CustomerInformationTable Z WHERE Z.CustID = A.CustID AND Z.Contract_ID = 'Check')THEN 'Check' END
FROM CustomerInformationTable A
WHERE A.Contract_ID = 'Cash'
ORDER BY A.CustID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources