Solved

Need help in framing a SQL query

Posted on 2008-10-23
9
266 Views
Last Modified: 2012-05-05
Got an External View View1 and a Table A in my DB


Column details of View 1
ID1, ID2

Column details of Table A column
ID
System ID
Reconciliation ID
Instance ID

Required Result
ID1, System ID1, Reconciliation ID1, Instance ID1, ID2, System ID2, Reconciliation ID2, Instance ID2

Note:  ID1 OR  ID2 refers to ID in table A.

Let me know if you need any clarifications
0
Comment
Question by:tks_g100
9 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 350 total points
ID: 22789555
Something like this should work.
SELECT v.ID1

, s1.[System ID1]

, s1.[Reconciliation ID1]

, s1.[Instance ID1]

, v.ID2

, s2.[System ID1] AS [System ID2]

, s2.[Reconciliation ID1] AS [Reconciliation ID2]

, s2.[Instance ID1] AS [Instance ID2]

FROM View1 v

LEFT JOIN TableA s1 ON s1.ID = v.ID1

LEFT JOIN TableA s2 ON s2.ID = v.ID2

Open in new window

0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 22789567
SELECT V.ID1, A.systemID, A.ReconciliationID, A.InstanceID,
      V.ID2, V.SystemID, V.ReconciliationID, V.InstanceID
FROM View1 V
LEFT OUTER JOIN TableA A
      ON A.ID = V.ID1
LEFT OUTER JOIN TableA B
      ON B.ID = V.ID2
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22789585
If you only want records where both the ID1 and ID2 values exist, then change query to use INNER JOIN versus LEFT JOIN.  As structured above, you will get where ID1 exists and ID2 doesn't and vice versus as well as where neither exist.

If you like the LEFT JOIN to get one or the other, but don't want to see where neither exists, you can add a where clause like:

WHERE COALESCE(s1.[System ID1], s2.[System ID1]) IS NOT NULL;
0
 
LVL 16

Expert Comment

by:Sheils
ID: 22789595
where does System ID2, Reconciliation ID2, Instance ID2 come from
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22789608
And sorry, I just relooked and your fields are ID not ID1.
SELECT v.ID1

, s1.[System ID] AS [System ID1]

, s1.[Reconciliation ID] AS [Reconciliation ID1]

, s1.[Instance ID] AS [Instance ID1]

, v.ID2

, s2.[System ID] AS [System ID2]

, s2.[Reconciliation ID] AS [Reconciliation ID2]

, s2.[Instance ID] AS [Instance ID2]

FROM View1 v

LEFT JOIN TableA s1 ON s1.ID = v.ID1

LEFT JOIN TableA s2 ON s2.ID = v.ID2

Open in new window

0
 
LVL 3

Expert Comment

by:richard_crist
ID: 22789609
Clarification:

*  You have two columns in View 1 called ID1 and ID2
*  You have Table A with four columns where the first column ID can have a value equal to the value in View 1 in either ID1 or ID2, that is, for a given row in Table A its first column ID could match a row in View 1 if the Table A ID equals either ID1 or ID2 in View 1
*  You wish to output rows showing ID1 from View 1 followed by the matching System, Reconciliation, and Instance IDs from Table A and on the same output row show ID2 from View 1 followed by the matching System, Reconciliation, and Instance IDs from Table A

Is that correct?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22789624
That was a type-o on my part. Those should be aliases to identify which one of the TableA the data came from (i.e. which ID are they matching to ID1 or ID2).
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22789641
The WHERE clause suggestion above would likewise be changed to this:
WHERE COALESCE(s1.[System ID], s2.[System ID]) IS NOT NULL;

Hope all that helps...
0
 

Author Closing Comment

by:tks_g100
ID: 31509404
Fantastic response between here is my modified query to you. Let me know your suggestions

SELECT v. id_1
, s1.assetid AS assetid1
, s1.reconciliationidentity AS reconciliationidentityID1
, s1.instanceid AS instanceid1
, s1.datasetid AS datasetid1
, s1.classid AS classid1
, s1.name AS SysID1

, v. id_2
, s2.assetid AS assetid2
, s2.reconciliationidentity AS reconciliationidentityID2
, s2.instanceid AS instanceid2
, s2.datasetid AS datasetid2
, s2.classid AS classid2
, s2.name AS name2

FROM (select id_1, id_2 from aradmin.shr_association@testdb where id_1 is not NULL and id_2 is not NULL and status = 0 and id_1 like 'AST%' and id_2 like 'AST%') v
LEFT JOIN bmc_core_bmc_baseelement s1 ON substr(s1.assetid, 4) = substr(v.id_1, 4)
LEFT JOIN bmc_core_bmc_baseelement s2 ON substr(s2.assetid, 4) = substr(v.id_2, 4)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 56
Query criteria:  Count number of distinct months in date field 38 47
Updating a table from a temp table 4 29
SQL Query Syntax Join 4 34
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now