SQL QUERY help for newbie - 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 2008 Forums

Transact-SQL (2008)

SQL QUERY help for newbie

Author

Topic

scummings
Starting Member

2 Posts

Posted - 2012-01-26 : 05:56:29

Hi All,

I have a SQL query that I can‘t quite figure out and I wondered if anyone can help?

I need a query to select t1.ALTREF and t2.NOTIFY and t2.SENT from where t1.ALTREF equals the concatenated string of:
‘t2.PA_DISTRICT + '/' + t2.PA_YEAR + '/' + t2.PA_REF’.

Note on the third record in t2, the leading zeros are missing!

Here are extracts from the two tables:

t1
ALTREF
S/1995/1685
S/1993/0078
S/1994/0065

t2
PA_DISTRICT PA_YEAR PA_REF NOTIFY SENT
----------- ------- ------ ------ ----
S 1995 1685 sample1 1995-12-04 00:00:00.000
S 1993 9378 sample2 1993-01-27 00:00:00.000
S 1994 65 sample3 1994-01-26 00:00:00.000

Here's what I'm trying to achieve.

RESULTS REQUIRED
ALTREF NOTIFY SENT
----------- ------ ----
S/1995/1685 sample1 1995-12-04 00:00:00.000
S/1993/0078 sample2 1993-01-27 00:00:00.000
S/1994/0065 sample3 1994-01-26 00:00:00.000

Here’s what I’ve got so far, but it doesn’t allow me to retrieve the ALTREF from Table1.

SELECT t2.PA_District,
t2.PA_Year,
t2.PA_Ref,
t2.notify,
t2.Sent
FROM [TestDB].[dbo][Table2] t2
left join [TestDB].[dbo][Table1] t1
on t1.Altref = 't2.PA_District' + '\' + 't2.PA_Year' + '\' + 't2.PA_Ref'

If anyone can help it’d be much appreciated. I’ve get a few other similar requirements, but if you can show me the first then I’ll be able to figure out the rest (I think/hope!)

Thanks,

Simon.