×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Select From...SecID

Select From...SecID

Select From...SecID

(OP)
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
 

 

RE: Select From...SecID

(OP)
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


 

RE: Select From...SecID

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?

RE: Select From...SecID

(OP)
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
 

RE: Select From...SecID

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close