Question on Joint - 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 2000 Forums

Transact-SQL (2000)

Question on Joint

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-06-02 : 13:58:57

Hi,

I have 2 tables, one containing the customer info and the other containing their monthly promotion info, like so:

CREATE TABLE tblCustomer (customerID smallint PRIMARY KEY IDENTITY NOT NULL, customerName nvarchar (20) NOT NULL);

INSERT INTO tblUser (username) VALUES ('ABC Shop');
INSERT INTO tblUser (username) VALUES ('DEF Shop');
INSERT INTO tblUser (username) VALUES ('XYZ Shop');

CREATE TABLE tblColor (promoID smallint PRIMARY KEY IDENTITY NOT NULL, customerID smallint, promoYear smallint, promoMonth smallint, promoDesc nvarchar (20) NOT NULL);

INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (1, 2006, 4, '10% Off');
INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (1, 2006, 5, '$10 Off');
INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (1, 2006, 6, '20% Off');
INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (1, 2006, 7, 'Buy one get one free');
INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (2, 2006, 4, '10% Off');
INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (2, 2006, 1, '10% Off');
INSERT INTO tblColor (customerID, promoYear, promoMonth, promoDesc) VALUES (2, 2006, 2, '10% Off');

I would like to write a query that returns a list of ALL the customers, and shows the monthly promotion of each customer if he has it in the CURRENT MONTH, otherwise show null. So in the case of the data above, 'ABC Shop' will have '10% Off', while 'DEF Shop' & 'XYZ Shop' have null in their promotion.

I'm wondering if I can do this without using UNION...

Thanks,
ywb.

Kristen
Test

22859 Posts

Posted - 2006-06-02 : 14:22:18

[code]
SELECT *
FROM tblCustomer AS CUST
LEFT OUTER JOIN tblColor AS COLOR
ON COLOR.customerID = CUST.customerID
AND COLOR.promoYear = DATEPART(YEAR, GetDate())
AND COLOR.promoMonth = DATEPART(MONTH, GetDate())
[/code]
Kristen