Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select From...SecID 1

Status
Not open for further replies.
harfri

harfri

Programmer
Joined
Mar 5, 2010
Messages
17
Location
NL
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


 
Sort by date Sort by votes
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


 
Upvote 0 Downvote
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
FAQ181-2886
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question Question
SELECT COUNT distinct
Replies
5
Views
723
sal21
sal21
mmiah1982
  • Locked
  • Question Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
563
mmiah1982
mmiah1982
mikrom
  • Locked
  • Question Question
from a table with symmetric relation select every pair only once
Replies
0
Views
439
mikrom
mikrom
szeiss
  • Locked
  • Question Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
622
szeiss
szeiss
fredong
  • Locked
  • Question Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
828
carp
carp

Part and Inventory Search

Sponsor

Back
Top