Selecting data by External Hierachy created

Question

5.00/5 (1 vote)

See more:

Hi I would like to select data from a table based upon the linked data

I have table A ,Table b ,Table C ,Table D

Table A,B,C i have joined in a view to create vwABC

table D is a symotom table which the data looks like this:
Table A,B,C are all joined onto table d via table D id

SQL

table d
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
id    Symptom      Weight
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1     Corrective    100
2     Repair         50
3     Design        110
4     Replaced       60
5     No Change     150
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

table a
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
id     productCode      tableDSymptom 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1      500                  2
2      600                  5
3      800                  1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

table b
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
id     productCode      tableDSymptom 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1      500                  4
2      600                  2
3      800                  1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

table c
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
id     productCode      tableDSymptom 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1      500                  3
2      600                  4
3      800                  5
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

what i need to do is create a view in which it would show the product, with all the data from Table A,b,C and show a Y for the symptom with the lowest weight and an N for the rest

So the view should look like this

SQL

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
id     productCode      tableDSymptom  Primary
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1      500                  2           Y
1      500                  4           N
1      500                  3           N
2      600                  5           N
2      600                  2           Y
2      600                  4           N
3      800                  1           Y
3      800                  1           Y
3      800                  5           N
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Posted 12-Mar-13 22:02pm

isi19

Updated 12-Mar-13 22:44pm

gvprabu

v3

Add a Solution

3 solutions

Solution 1

First get the record for each product with the lowest weight

SQL

SELECT IDToLinkBackToYourView,
ROW_NUMBER() OVER(PARTITION ProductCode OVER Weight ORDER BY weight DESC) Rw
FROM vwABC v
	INNER JOIN Symptom S ON S.SymptomID = V.SymptomID --or the symptom id from the product table in the view

Using that as a sub query you can use a case when Rw = 1 then Y else N end to get the data you want

Posted 12-Mar-13 22:37pm

Mycroft Holmes

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-03-12T22:51:00

Hi,

Check the following Code...

SQL

SELECT T.id, T.productCode, T.tableDSymptom,
CASE WHEN (ROW_NUMBER()OVER(PARTITION BY A.ID ORDER BY T.tableDSymptom)) = 1 THEN 'Y'
     ELSE 'N' END 'Primary'
FROM (SELECT A.id, A.productCode, A.tableDSymptom 
	 FROM table_A A
	 UNION ALL
	 SELECT B.id, B.productCode, B.tableDSymptom 
	 FROM table_B B
	 UNION ALL
	 SELECT C.id, C.productCode, C.tableDSymptom 
	 FROM table_C C) T
ORDER BY T.id, T.tableDSymptom

Regards,
GVPrabu

Shanalal Kasim · Accepted Answer · 2013-03-12T23:01:00

Hi,

Try

1. Create a view like below. View name is vwABC

SQL

select a.id,a.productCode,a.tableDSymptom, d.weight from a,d where a.tableDSymptom = d.id
union all
select b.id,b.productCode,b.tableDSymptom , d.weight from b,d where b.tableDSymptom = d.id
union all
select c.id,c.productCode,c.tableDSymptom ,d.weight from c,d where c.tableDSymptom = d.id

After that use below sql

SQL

select vwABCMain.id,vwABCMain.productCode,vwABCMain.tableDSymptom,vwABCMain.weight,
case (select min(vwABCSub.weight) from [dbo].[vwABC] vwABCSub where vwABCSub.productCode = vwABCMain.productCode )
when vwABCMain.weight then 'Y'
ELSE 'N'
END
from [dbo].[vwABC] vwABCMain
order by id

Selecting data by External Hierachy created

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0