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.
| Author |
Topic |
|
boybles
Starting Member
23 Posts |
 Posted - 2007-07-22 : 13:52:09
|
I have 3 tables, a Store_Tbl for stores, a Store_Mall_Tbl to associate stores in mallsand a Store_ATM_tbl to associate ATM types in stores.Store_Tbl--------------StoreID|StoreName-----------------1|GAP2|Banana Republic3|Broadway4|May Company5|SearsStore_Mall_Tbl--------------------StoreID|MallID-----------------1|12|13|14|15|1Store_ATM_Tbl------------------StoreID|ATMID------------------1|12|13|1How can I run a query w/ appropriate outer joins to fetch StoreName and ATM availability given an ATMID and a MallIDEx:Input: ATMID=1, MallID=1Output:StoreName|ATM---------------GAP|YESBanana Republic|YESBroadway|YESMayCompany|NOSears|NOPlease Help!Boybles |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-22 : 14:10:15
|
| [code]-- Prepare sample dataDECLARE @Store TABLE (StoreID INT, StoreName VARCHAR(20))INSERT @StoreSELECT 1, 'GAP' UNION ALLSELECT 2, 'Banana Republic' UNION ALLSELECT 3, 'Broadway' UNION ALLSELECT 4, 'May Company' UNION ALLSELECT 5, 'Sears'DECLARE @Mall TABLE(StoreID INT, MallID INT)INSERT @MallSELECT 1, 1 UNION ALLSELECT 2, 1 UNION ALLSELECT 3, 1 UNION ALLSELECT 4, 1 UNION ALLSELECT 5, 1DECLARE @ATM TABLE (StoreID INT, ATMID INT)INSERT @ATMSELECT 1, 1 UNION ALLSELECT 2, 1 UNION ALLSELECT 3, 1-- Do the workDECLARE @ATMID INT, @MallID INTSELECT @ATMID = 1, @MallID = 1SELECT s.StoreName, CASE WHEN a.StoreID IS NULL THEN 'No' ELSE 'Yes' END AS ATMFROM @Store AS sINNER JOIN @Mall AS m ON m.StoreID = s.StoreIDLEFT JOIN @ATM AS a ON a.StoreID = s.StoreID AND a.ATMID = @ATMIDWHERE m.MallID = @MallID[/code]Peter LarssonHelsingborg, Sweden |
 |
|
|
boybles
Starting Member
23 Posts |
Posted - 2007-07-23 : 21:10:57
|
| Beautiful!!! Thank you so much, Peter! |
|
|
|
|
|
|
|
|
Help with SQL Join statement