1

I am looking for an answer for the following question

I have a database containing 3 following tables:

TB-001_ISB
TB-002_ISDB
TI-003_ISBDB

The data under table TB-001_ISB are 

ISB_No                  ISB_Ad
121XS9060301            ANKARA CANKAYA
122CV9061501            ANKARA YILDIZ
123BN9062081            ANKARA KIZILAY
124GH8709034            ANKARA MAMAK

The data under TB-002_ISDB are

ISDB_ID                ISDB_Sit
F-10-01-MM             Factory Production
F-20-01-MG             Factory Quality
F-30-01-FM             Site

The data under TI-003_ISBDB are

ISBDB_No              ISBDB_ISDB_ID         ISBDB_Pers          ISBDB_Date
121XS9060301          F-10-01-MM            1234                01.01.2010
121XS9060301          F-20-01-MG            1234                02.01.2010
121XS9060301          F-30-01-FM            4321                03.01.2010
121XS9060301          F-20-01-MG            6785                04.01.2010
122CV9061501          F-10-01-MM            1234                01.03.2010
122CV9061501          F-20-01-MG            1234                02.03.2010
123BN9062081          F-20-01-MG            4321                03.10.2010
123BN9062081          F-30-01-FM            6785                04.10.2010

I need a query to get the following output:

ISBDB_No              ISB_Ad              ISDB_Sit         ISBDB_Pers          ISBDB_Date
121XS9060301          ANKARA CANKAYA      Factory Quality  6785                04.01.2010
122CV9061501          ANKARA YILDIZ       Factory Quality  1234                02.03.2010
123BN9062081          ANKARA KIZILAY      Site             6785                04.10.2010

So basically, I want the results to be sorted by ISBDB_Date and taking the max (ISBDB_Date) In order to do so, I wrote the following query 

SELECT  [ISB_No], [ISB_Ad], [ISDB_Sit], [ISBDB_Pers], 
(SELECT (Max([ISBDB_Date]) 
FROM [TI-003_ISBDB]
GROUP BY [ISBDB_No])

FROM  (([TB-001_ISB] INNER JOIN [TI-003_ISBDB] ON [ISB_No]=[ISBDB_No]) 
INNER JOIN   [TB-002_ISDB] ON [ISDB_ID]=[ISBDB_ISDB_ID])

However, there are problems with this query. Can anybody help me? All of the efforts are highly appreciated...

Improve this question
6
  • 2
    You should really specify what the problems are with the query before you can reasonably expect anyone to be able to help you... Commented Sep 15, 2011 at 18:49
  • What happens if you hav two TI-003_ISBDB records with the same SBDB_Dateand ISDB_ID ? Commented Sep 15, 2011 at 18:50
  • Actually, RedFilter you are right, but for ISBDB_Date data type, I use datetime and getdate() for the default value before entering values under the row. Commented Sep 15, 2011 at 19:07
  • That doesn't mean you can't get duplicates. It makes it difficult, but not impossible. Commented Sep 15, 2011 at 19:23
  • Then, how do I overcome this problem? Aaron, can you suggest any solution? Commented Sep 15, 2011 at 19:38

2 Answers 2

Reset to default
1

Try something like this:

WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [ISBDB_No] ORDER BY [ISBDB_Date] DESC) Corr
    FROM [TI-003_ISBDB]
)
SELECT B.*, [ISB_Ad], [ISDB_Sit]
FROM [TB-001_ISB] A
INNER JOIN (SELECT * FROM CTE WHERE corr = 1) B
ON A.[ISB_No] = B.[ISBDB_No]
INNER JOIN [TB-002_ISDB] C
ON C.[ISDB_ID] = B.[ISBDB_ISDB_ID]
Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

Why use a CTE when a correlated subquery does the same thing?
@Wil why use a correlated subquery when a CTE does the same thing?
@Aaron, touche...except a correlated subquery is simpler and more widely understood. Using a CTE for something like this - with a row_number() to boot, unnecessarily complicates the query. That's great, if someone's goal is to make their code so dense that there's no way they can ever be fired. Not so great if you have to be the one that comes along after they get fired and have to deal with it.
CTEs have been in pretty popular usage since SQL Server 2005. If you get fired because you used a CTE and the other guy couldn't understand it, I'd suggest they fired the wrong person. The ranking functions can come in very handy especially when self-joining, they're also very handy for recursion / hierarchy queries, and I like the fact that I can define the column names as part of the definition instead of mixing aliases in with expressions in the query.
@Wil - "if someone's goal is to make their code so dense that there's no way they can ever be fired" that's a huge exaggeration. I actually find CTEs simpler and more clear than a correlated subquery. I guess its all about the user.
|
0 
SELECT
    ISB_No
    ,ISB_Ad
    ,ISDB_Sit
    ,ISBDB_Pers
    ,ISBDB_Date
FROM  [TB-001_ISB] AS isb
INNER JOIN [TI-003_ISBDB] AS isdbd ON isb.ISB_No=isdbd.ISBDB_No 
INNER JOIN   [TB-002_ISDB] AS isdb ON isdb.ISDB_ID=isdbd.ISBDB_ISDB_ID
WHERE isdbd.ISBDB_Date = (select MAX(isdbd1.ISBDB_Date) FROM [TI-003_ISBDB] AS isdbd1
    WHERE isdbd.ISBDB_No = isdbd1.ISBDB_No)
ORDER BY isdbd.ISBDB_Date DESC
Improve this answer

3 Comments

Will, many thanks for your suggestion but I do not have SQL server 2005 with me right now. So to try your query given above, I modified it to be able to run under Access 2007. However, the query asked me to enter ISB_No and ISBDB_Date. Does this problem result from the modification done by me, or will I face with the same problem under SQL 2005?
You will not have that problem under SQL Server 2005 (or 2000 or 2008). I do not know why Access would prompt you for those values, although I do believe you would have had to rearrange the JOINS to work with Access syntax and may have introduced an issue. If the query was for Access, you should tag the question as such.
No, the query was not for Access. Tomorrow, I will try your query and give information about the results.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.