Help with SQL problem: match where other record is flagged

[Jon Davis]

I need help with a SQL problem. This is "problem" as in like math problem.
(And no, this isn't for class, it's a contract that I've lost a lot of time
on by spending it pulling my hair out.)

I have a database with one catch-all table, something like as follows:

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

A unique record is determined by Username + Key + SubKey.

I have two sets of many records. The first set looks like this:
[username], MyBoolValueRecord, [subkey], [1/0], [null]

The second set looks like this:
[username], MyIntValueRecord, [subkey], [null], [#number#]

The problem that I'm having is that I need the second set to be directly
corresponding the first set, so that I only retrieve the [subkey] +
[username] values of [key:MyIntValue] where [username] + [key:MyBoolValue] +
[subkey] is 1/true, and ignore the [subkey]+[username] values of
[key:MyIntValue] where [username] + [key:MyBoolValue] + [subkey] is 0/false.

Since I have to refer to separate records of the same table, I cannot use
JOIN (can I?). Surely this can still be done with a single SQL query? I can
settle with a stored procedure if I have to, but I'd prefer not. I've tried
WHERE ... IN (SELECT...), but that only supports one field match and I need
both [username] and [subkey] to match.

Sorry if this is hard to follow ... now you know my plight!!

Thanks,
Jon

 

[Steve Kass]

Jon,

This is indeed hard to follow. Things like
"[username]+[key:MyBoolValue]+[subkey] is 1/true" make little sense, in
particular. How can you add or concatenate strings and end up with
true? You say "I need the second set to be directly corresponding the
first set" which also isn't very clear.

You could make this much easier to follow and have more luck getting a
useful response if you provided the CREATE TABLE statement for your
table, INSERT INTO .. VALUES statements that insert several rows of data
(enough to show what you want), and exactly what you would want to
appear in a result set given the sample data you provide.

I doubt this will be a difficult problem to solve, once it's clear
what the problem is.

Steve Kass
Drew University

 

[Jon Davis]

"[username]+[key:MyBoolValue]+[subkey] is 1/true",

How can you add or concatenate strings and end up with true?

Concatonate? I'm not concatonating. This is pseudocode, but I'm referring to
records where all of these fields line up.

The "value" of the record is either IntValue or it's BoolValue, both of
these each being a field in the table. To get the value, three keys must
line up: Username, Key, and SubKey. That's what I mean by
"[username]+[key:MyBoolValue]+[subkey] is 1/true", that is, all records
where the Username field match up, the Key field is "MyBoolValue", the
subkey field matches up, and the value of BoolAnswer is 1. I could explain
all this in such detail, or I could use smaller pseudocode and say
"[username]+[key:MyBoolValue]+[subkey] is 1/true".

CREATE TABLE statement for your table

Sorry, but this is easier to follow.

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

Let me illustrate the problem, with two users, or eight records. User1
trades horses and cows, and owns two of each. User2 trades cows only, and
has five of them.

This would be stored in the table as:

#1: 'User1', 'Trades', 'Horses', 1, <null>
#2: 'User1', 'Trades', 'Cows', 1, <null>
#3: 'User1', 'Owns', 'Horses', <null>, 2
#4: 'User1', 'Owns', "Cows', <null>, 2
#5: 'User2', 'Trades', 'Horses', 0, <null>
#6: 'User2', 'Trades', 'Cows', 1, <null>
#7: 'User2', 'Owns', 'Horses', <null>, 0
#8: 'User2', 'Owns', 'Cows', <null>, 5

So if I want to get an average of all horses ONLY from those who trade them,
and an average of all cows ONLY from those who trade them, I must NOT count
Record #7.

Obviously I'm not dealing with records and cows, and there are more
variations. But this is otherwise exactly my problem.

Jon

Jon,

This is indeed hard to follow. Things like
"[username]+[key:MyBoolValue]+[subkey] is 1/true" make little sense, in
particular. How can you add or concatenate strings and end up with
true? You say "I need the second set to be directly corresponding the
first set" which also isn't very clear.

You could make this much easier to follow and have more luck getting a
useful response if you provided the CREATE TABLE statement for your
table, INSERT INTO .. VALUES statements that insert several rows of data
(enough to show what you want), and exactly what you would want to
appear in a result set given the sample data you provide.

I doubt this will be a difficult problem to solve, once it's clear
what the problem is.

Steve Kass
Drew University

I need help with a SQL problem. This is "problem" as in like math problem.
(And no, this isn't for class, it's a contract that I've lost a lot of time
on by spending it pulling my hair out.)

I have a database with one catch-all table, something like as follows:

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

A unique record is determined by Username + Key + SubKey.

I have two sets of many records. The first set looks like this:
[username], MyBoolValueRecord, [subkey], [1/0], [null]

The second set looks like this:
[username], MyIntValueRecord, [subkey], [null], [#number#]

The problem that I'm having is that I need the second set to be directly
corresponding the first set, so that I only retrieve the [subkey] +
[username] values of [key:MyIntValue] where [username] + [key:MyBoolValue] +
[subkey] is 1/true, and ignore the [subkey]+[username] values of
[key:MyIntValue] where [username] + [key:MyBoolValue] + [subkey] is 0/false.

Since I have to refer to separate records of the same table, I cannot use
JOIN (can I?). Surely this can still be done with a single SQL query? I can
settle with a stored procedure if I have to, but I'd prefer not. I've tried
WHERE ... IN (SELECT...), but that only supports one field match and I need
both [username] and [subkey] to match.

Sorry if this is hard to follow ... now you know my plight!!

Thanks,
Jon

 

[Jon Davis]

Obviously I'm not dealing with records and cows, and there are more

variations. But this is otherwise exactly my problem.

Oops I mean horses and cows, not records and cows LOL

Jon

"[username]+[key:MyBoolValue]+[subkey] is 1/true",

How can you add or concatenate strings and end up with true?

Concatonate? I'm not concatonating. This is pseudocode, but I'm referring to
records where all of these fields line up.

The "value" of the record is either IntValue or it's BoolValue, both of
these each being a field in the table. To get the value, three keys must
line up: Username, Key, and SubKey. That's what I mean by
"[username]+[key:MyBoolValue]+[subkey] is 1/true", that is, all records
where the Username field match up, the Key field is "MyBoolValue", the
subkey field matches up, and the value of BoolAnswer is 1. I could explain
all this in such detail, or I could use smaller pseudocode and say
"[username]+[key:MyBoolValue]+[subkey] is 1/true".

CREATE TABLE statement for your table

Sorry, but this is easier to follow.

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

Let me illustrate the problem, with two users, or eight records. User1
trades horses and cows, and owns two of each. User2 trades cows only, and
has five of them.

This would be stored in the table as:

#1: 'User1', 'Trades', 'Horses', 1, <null>
#2: 'User1', 'Trades', 'Cows', 1, <null>
#3: 'User1', 'Owns', 'Horses', <null>, 2
#4: 'User1', 'Owns', "Cows', <null>, 2
#5: 'User2', 'Trades', 'Horses', 0, <null>
#6: 'User2', 'Trades', 'Cows', 1, <null>
#7: 'User2', 'Owns', 'Horses', <null>, 0
#8: 'User2', 'Owns', 'Cows', <null>, 5

So if I want to get an average of all horses ONLY from those who trade them,
and an average of all cows ONLY from those who trade them, I must NOT count
Record #7.

Obviously I'm not dealing with records and cows, and there are more
variations. But this is otherwise exactly my problem.

Jon

Jon,

This is indeed hard to follow. Things like
"[username]+[key:MyBoolValue]+[subkey] is 1/true" make little sense, in
particular. How can you add or concatenate strings and end up with
true? You say "I need the second set to be directly corresponding the
first set" which also isn't very clear.

You could make this much easier to follow and have more luck getting a
useful response if you provided the CREATE TABLE statement for your
table, INSERT INTO .. VALUES statements that insert several rows of data
(enough to show what you want), and exactly what you would want to
appear in a result set given the sample data you provide.

I doubt this will be a difficult problem to solve, once it's clear
what the problem is.

Steve Kass
Drew University

I need help with a SQL problem. This is "problem" as in like math problem.
(And no, this isn't for class, it's a contract that I've lost a lot of time
on by spending it pulling my hair out.)

I have a database with one catch-all table, something like as follows:

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

A unique record is determined by Username + Key + SubKey.

I have two sets of many records. The first set looks like this:
[username], MyBoolValueRecord, [subkey], [1/0], [null]

The second set looks like this:
[username], MyIntValueRecord, [subkey], [null], [#number#]

The problem that I'm having is that I need the second set to be directly
corresponding the first set, so that I only retrieve the [subkey] +
[username] values of [key:MyIntValue] where [username] + [key:MyBoolValue] +
[subkey] is 1/true, and ignore the [subkey]+[username] values of
[key:MyIntValue] where [username] + [key:MyBoolValue] + [subkey] is 0/false.

Since I have to refer to separate records of the same table, I cannot use
JOIN (can I?). Surely this can still be done with a single SQL query? I can
settle with a stored procedure if I have to, but I'd prefer not. I've tried
WHERE ... IN (SELECT...), but that only supports one field match and I need
both [username] and [subkey] to match.

Sorry if this is hard to follow ... now you know my plight!!

Thanks,
Jon

 

[Steve Kass]

I'm sorry Jon, but I still have no idea what you want. I don't know
what it means for keys to "line up" and fields to "match up", and I
still don't know what your pseudocode means. Nor do I know from your 7
rows of sample data what you want. Suddenly there is an "average", and
all you say about what you want is that you must not count record #7.
It would help, for example, if you displayed the result you want to see
when your table consists of exactly the 7 rows you show below, assuming
you are trying to write a query that returns a result set, which isn't
obvious.

Perhaps this makes more sense to someone else.

If by chance you want a list of those UserName values that appear both
in a row with Key="Trades" and SubKey = "Horses" and also in a row with
Key = "Owns" and SubKey = "Horses" and IntValue > 0, and for each such
UserName you wat the average of IntValue from the row with that UserName
and SubKey = "Horses" and Key = "Owns", you can do this:

select Username, avg(IntValue*1.00) as avgHorses
from yourTable T1
where SubKey = 'Horses' and Key = 'Owns'
and exists (
select * from yourTable T2
where SubKey = 'Horses' and Key = 'Trades' and BoolValue > 0
)
group by Username

That's assuming that BoolValue = 1 means "yes, the user does <Key>
<SubKey>s, regardless of the value of IntValue in that row.

SK

"[username]+[key:MyBoolValue]+[subkey] is 1/true",

How can you add or concatenate strings and end up with true?

Concatonate? I'm not concatonating. This is pseudocode, but I'm referring to
records where all of these fields line up.

The "value" of the record is either IntValue or it's BoolValue, both of
these each being a field in the table. To get the value, three keys must
line up: Username, Key, and SubKey. That's what I mean by
"[username]+[key:MyBoolValue]+[subkey] is 1/true", that is, all records
where the Username field match up, the Key field is "MyBoolValue", the
subkey field matches up, and the value of BoolAnswer is 1. I could explain
all this in such detail, or I could use smaller pseudocode and say
"[username]+[key:MyBoolValue]+[subkey] is 1/true".

CREATE TABLE statement for your table

Sorry, but this is easier to follow.

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

Let me illustrate the problem, with two users, or eight records. User1
trades horses and cows, and owns two of each. User2 trades cows only, and
has five of them.

This would be stored in the table as:

#1: 'User1', 'Trades', 'Horses', 1, <null>
#2: 'User1', 'Trades', 'Cows', 1, <null>
#3: 'User1', 'Owns', 'Horses', <null>, 2
#4: 'User1', 'Owns', "Cows', <null>, 2
#5: 'User2', 'Trades', 'Horses', 0, <null>
#6: 'User2', 'Trades', 'Cows', 1, <null>
#7: 'User2', 'Owns', 'Horses', <null>, 0
#8: 'User2', 'Owns', 'Cows', <null>, 5

So if I want to get an average of all horses ONLY from those who trade them,
and an average of all cows ONLY from those who trade them, I must NOT count
Record #7.

Obviously I'm not dealing with records and cows, and there are more
variations. But this is otherwise exactly my problem.

Jon

Jon,

This is indeed hard to follow. Things like
"[username]+[key:MyBoolValue]+[subkey] is 1/true" make little sense, in
particular. How can you add or concatenate strings and end up with
true? You say "I need the second set to be directly corresponding the
first set" which also isn't very clear.

You could make this much easier to follow and have more luck getting a
useful response if you provided the CREATE TABLE statement for your
table, INSERT INTO .. VALUES statements that insert several rows of data
(enough to show what you want), and exactly what you would want to
appear in a result set given the sample data you provide.

I doubt this will be a difficult problem to solve, once it's clear
what the problem is.

Steve Kass
Drew University

Jon Davis wrote:

I need help with a SQL problem. This is "problem" as in like math

problem.

(And no, this isn't for class, it's a contract that I've lost a lot of

time

on by spending it pulling my hair out.)

I have a database with one catch-all table, something like as follows:

string Username
string Key
string SubKey
bool/bit BoolValue
int IntValue

A unique record is determined by Username + Key + SubKey.

I have two sets of many records. The first set looks like this:
[username], MyBoolValueRecord, [subkey], [1/0], [null]

The second set looks like this:
[username], MyIntValueRecord, [subkey], [null], [#number#]

The problem that I'm having is that I need the second set to be directly
corresponding the first set, so that I only retrieve the [subkey] +
[username] values of [key:MyIntValue] where [username] +

[key:MyBoolValue] +

[subkey] is 1/true, and ignore the [subkey]+[username] values of
[key:MyIntValue] where [username] + [key:MyBoolValue] + [subkey] is

0/false.

Since I have to refer to separate records of the same table, I cannot use
JOIN (can I?). Surely this can still be done with a single SQL query? I

can

settle with a stored procedure if I have to, but I'd prefer not. I've

tried

WHERE ... IN (SELECT...), but that only supports one field match and I

need

both [username] and [subkey] to match.

Sorry if this is hard to follow ... now you know my plight!!

Thanks,
Jon

 

[Jon Davis]

I admit I also did not mention and should have--is that there are seven
SubKey variations, and I was hoping to avoid seven separate queries.

Otherwise, if I want seven queries, I suppose I can always go with:

SELECT IntValue FROM MyTable WHERE Key='Owns' AND SubKey='Horse' AND
Username In (SELECT Username FROM MyTable WHERE Key='Trades' AND
SubKey='Horse' AND BoolValue = 1)

But I don't want to pre-specify 'Horse'. I'd like to SELECT SubKey,
IntValue, rather than just IntValue, but, as I said in the first post, WHERE
IN only checks for matches against a single field.

Considering no one else is commenting I guess I have no choice but to have
each SubKey variation to be used in a separate query. I suppose if that's my
only (and best) option then I've thought out that much from this thread
enough to be grateful for a better sense of direction.

Jon

 

[Steve Kass]

EXISTS can do more than IN - generalizing my previous example:

select SubKey, Username, avg(IntValue*1.00) as avgHorses
from yourTable T1
where Key = 'Owns'
and exists (
select * from yourTable T2
where T2.SubKey = T1.SubKey and Key = 'Trades' and BoolValue > 0
)
group by Username

SK