Writing a tsql select statement... suggestions?

[GeekDrew]

Note the following tables:

pv
pv_id (PK)

pr
pv_id
code_id

code
code_id (PK)
type
message
weight

Table pr describes the relationship between people pv and attributes that may apply to those people code. Each person pv_id may have zero or more attributes (rows) code_id in pr.

When I'm asking for detail of the associations, I might use a query like this:

SELECT pv.pv_id, code.code_id, code.type, code.message, code.weight
FROM pv
INNER JOIN pr ON pv.pv_id = pr.pv_id
INNEER JOIN code ON pr.code_id = code.code_id

What I need to do now, though, is select the pv.pv_id and code.code_id per code.type where code.weight is either (the least number appearing OR null). For example, while referring to the following result set from the above select query:

1, 32, GREETING, WELCOME, 1
1, 33, GREETING, HELLOTHAR, 2
1, 34, GREETING, WHATSUP, NULL
1, 35, GREETING, LEAVEMEALONE, 3

I need to build a query to determine the following result set:

1, 32, GREETING, WELCOME, 1
1, 34, GREETING, WHATSUP, NULL

I've done something similar to this previously, but I don't currently have access to those statements, and I'm apparently having a bad day cause I can't think of any way to do this without a really ugly solution involving multiple nested queries.

Does anyone have any suggestions?

Thanks!

 

[KLin]

SELECT pv.pv_id, code.code_id, code.type, code.message, code.weight
FROM ((pv INNER JOIN pr ON pv.pv_id = pr.pv_id) INNER JOIN code ON pr.code_id = code.code_id) INNER JOIN
(SELECT pr.pv_id, min(code.weight) As MinWeight FROM CODE inner join pr on code.code_id = pr.code_id Where Code.Weight Is Not Null GROUP BY pr.pv_id) C1 ON C1.minweight = code.weight and c1.pv_id = pr.pv_id

UNION ALL

SELECT pv.pv_id, code.code_id, code.type, code.message, code.weight
FROM ((pv INNER JOIN pr ON pv.pv_id = pr.pv_id) INNER JOIN code ON pr.code_id = code.code_id)
WHERE code.weight Is Null


Convoluted? Yes. Does it work with your data set example? Yes. Will it work for the rest of the data set? Maybe, maybe not.

 

[awal]

Since your output doesn't include any fields from pv, you can exclude that from your statement. What Klin posted would work , you could also accomplish the same using the code below. The execution plan below is more efficient.

 

[KLin]

Originally posted by: awal
Since your output doesn't include any fields from pv, you can exclude that from your statement. What Klin posted would work , you could also accomplish the same using the code below. The execution plan below is more efficient.

Your statement wouldn't work. What if the minimum weight for a code_id associated with someone is 2? My statement selects the minimum weight from the code_id's associated with each person.

 

[awal]

Originally posted by: KLin

Originally posted by: awal
Since your output doesn't include any fields from pv, you can exclude that from your statement. What Klin posted would work , you could also accomplish the same using the code below. The execution plan below is more efficient.

Your statement wouldn't work. What if the minimum weight for a code_id associated with someone is 2? My statement selects the minimum weight from the code_id's associated with each person.

I stand corrected, my statement wouldn't return the correct result set. You should change your approach though, you can return the same records without using the UNION ALL. An EXISTS clause would be far more efficient.

 

[KLin]

Originally posted by: awal

Originally posted by: KLin

Originally posted by: awal
Since your output doesn't include any fields from pv, you can exclude that from your statement. What Klin posted would work , you could also accomplish the same using the code below. The execution plan below is more efficient.

Your statement wouldn't work. What if the minimum weight for a code_id associated with someone is 2? My statement selects the minimum weight from the code_id's associated with each person.

I stand corrected, my statement wouldn't return the correct result set. You should change your approach though, you can return the same records without using the UNION ALL. An EXISTS clause would be far more efficient.

The reason I used the union clause is because he wants to return records where the weight field is the minimum for each person, or is null.

So instead of doing it all in one query, I separated them out into a union. I doubt it would cause much of a slowdown with a large data set.

 

[KLin]

Update OP? What'd you end up doing?

 

[GeekDrew]

Originally posted by: KLin
Update OP? What'd you end up doing?

Haven't revisited the problem since I made the OP... been swamped with bigger problems this week. I hope to get back to this tomorrow or Monday. I'll let you know.

I think that my stumbling block was that I was trying to avoid a "UNION ALL"... though I really don't know why I need to avoid that.