Select From...SecID

Question

Select From...SecID

harfri (Programmer)

(OP)

8 Mar 10 14:25

Can anyone help me with this SQL Query?

I have two tabels

TABEL A
SECID      SECSYMBOL
US00111    AAA
US00222    BBB
US00333    CCC
US00333    DDD
US00333    EEE
US00333    FFF

TABEL B
SECSYMBOL  SECNUM
AAA            1234
BBB            1235
CCC            1236
DDD            1237
EEE            1238
FFF            1239

This is what I would as a result.....

SECID      SECNUM(1) SECNUM(2) SECNUM(3) SECNUM(4)
US00111       1234
US00222       1235
US00333       1236      1237           1238          1239

harfri (Programmer) · Answer 1 · 2010-03-08 14:28:47

When using MS-Acces this works fine:

SELECT [Tabel A].SECID, [Tabel A].SECSYMBOL, Count([Tabel A_1].SECID) AS ColNum
FROM [Tabel A] INNER JOIN [Tabel A] AS [Tabel A_1] ON [Tabel A].SECID = [Tabel A_1].SECID
WHERE ((([Tabel A_1].SECSYMBOL)<=[Tabel A]![SECSYMBOL]))
GROUP BY [Tabel A].SECID, [Tabel A].SECSYMBOL;

and then

TRANSFORM First([Tabel B].SECNUM) AS FirstOfSECNUM
SELECT qrnkSECID.SECID
FROM [Tabel B] INNER JOIN qrnkSECID ON [Tabel B].SECSYMBOL = qrnkSECID.SECSYMBOL
GROUP BY qrnkSECID.SECID
PIVOT "SECNUM(" & [ColNum] & ")";

Maybe

PHV (MIS) · Answer 2 · 2010-03-08 20:15:35

A starting point for no more than 4 secnum per secid:

CODE

SELECT SECID, MAX(SecNum1) AS SECNUM_1, MAX(SecNum2) AS SECNUM_2, MAX(SecNum3) AS SECNUM_3, MAX(SecNum4) AS SECNUM_4
FROM (
SELECT A.SECID, B.SECNUM AS SecNum1, Null AS SecNum2, Null AS SecNum3, Null AS SecNum4
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=1
UNION SELECT A.SECID, Null, B.SECNUM, Null, Null
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=2
UNION SELECT A.SECID, Null, Null, B.SECNUM, Null
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=3
UNION SELECT A.SECID, Null, Null, Null, B.SECNUM
FROM TableB AS B
INNER JOIN TableA AS A ON B.SECSYMBOL = A.SECSYMBOL
INNER JOIN TableA AS A1 ON A.SECID = A1.SECID AND A.SECSYMBOL>=A1.SECSYMBOL
GROUP BY A.SECID, B.SECNUM HAVING COUNT(*)=4
) U
GROUP BY SECID

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

harfri (Programmer) · Answer 3 · 2010-03-09 09:06:10

Hi,

Thank you for this query. I have try to run this in MS-Access.
But then I get a syntax error.

Any Idea?

regards

PHV (MIS) · Answer 4 · 2010-03-09 09:12:02

You've posted in the ANSI_SQL forum, so I gave you an ANSI answer ...
The MS-Access answer is in the thread you've started in the JetSQL forum.

Come Join Us!

Posting Guidelines

Select From...SecID