Need help with SQL statement for JOIN-type stuff

[Mike]

Hello,

I wrote some code below to retrieve data from these tables:

Stat
---
id
gameId
name

MiniStatData
---
id
miniId
statId
statValue

Mini
---
id
gameId

Stat corresponds to a table of variable names and MiniStatData
corresponds to a table of variable values. Each Mini can have
multiple stats, and each stat has a value.

The first problem I ran into is that it is obviously not very robust-
if I am missing a value for one of the stats, all the values that
follow will be shifted by 1 column on the table.

The second problem I ran into is how to sort by the column headings (I
hyperlinked them so I can click on them to send a sort-by value back
to the page). The second SQL statement below has two ORDER BY parts
and I can't really figure out how to order the whole table by the
values themselves given the way the database is layed out.

Any suggestions how I can implement this better?

Thanks,
Mike

'Get the column headings
sql = "SELECT " &_
"Stat.name, " &_
"Stat.id " &_
"FROM " &_
"Stat " &_
"WHERE " &_
"Stat.gameId = " & gameId & " " &_
"ORDER BY " &_
"Stat.id"
set rs2 = oConn.execute(sql)

'Get the rows, in-line (row1row2row3...etc...)
sql = "SELECT " &_
"MiniStatData.statValue " &_
"FROM " &_
"MiniStatData, " &_
"Stat " &_
"WHERE " &_
"MiniStatData.statId = Stat.id " &_
"AND " &_
"Stat.gameId = " & gameId & " " &_
"ORDER BY " &_
"MiniStatData.miniId, " &_
"Stat.id"
set rs = oConn.execute(sql)

Response.write "<table>"

'Fill in the first row (the column headings)
Response.Write "<tr>"
recordCount = 0
while not rs2.EOF
Response.Write "<td><b><a href=""" & thispage & "?order=" &
rs2(1) & """>" & rs2(0) & "</a></b></td>"
rs2.movenext
recordCount = recordCount + 1
wend
Response.Write "</tr>"

'Fill in the rows of data, retrieving each row from the inline
data (row1row2row3)
while not rs.EOF
Response.Write "<tr>"

for i = 1 to recordCount
if not rs.EOF then
Response.Write (td_string & rs(0) & "</td>")
rs.movenext
end if
next

Response.Write "</tr>"
wend

[Bob Barrows [MVP]]

Mike wrote:

> Hello,
>
> I wrote some code below to retrieve data from these tables:
>
> Stat
> ---
> id
> gameId
> name
>
> MiniStatData
> ---
> id
> miniId
> statId
> statValue
>
> Mini
> ---
> id
> gameId
>
> Stat corresponds to a table of variable names and MiniStatData
> corresponds to a table of variable values. Each Mini can have
> multiple stats, and each stat has a value.
>
> The first problem I ran into is that it is obviously not very robust-
> if I am missing a value for one of the stats, all the values that
> follow will be shifted by 1 column on the table.

Huh? What table?

Show us a few rows of sample data from each source table (in tabular format)
and then show us the desired results (again in tabular format). Please
include the data that causes the problem you are trying to solve in your
sample data, and show the incorrect results (in tabular format)

>
> The second problem I ran into is how to sort by the column headings (I
> hyperlinked them so I can click on them to send a sort-by value back
> to the page). The second SQL statement below has two ORDER BY parts
> and I can't really figure out how to order the whole table by the
> values themselves given the way the database is layed out.
>

Same request as above: sample data, current results, desired results: all in
tabular format.

Only show us the minimum number of fields required to illustrate the
problem.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike]

I found ASPFAQ #2145 after reading your response... sorry about that.

How's this:

'according to aspfaq 2145

CREATE TABLE Game
(
[id] INT IDENTITY,
[name] VARCHAR(32)
)

CREATE TABLE GameSet
(
[id] INT IDENTITY,
[gameId] INT,
[name] VARCHAR(32)
)

CREATE TABLE Mini
(
[id] INT IDENTITY,
[gameSetId] INT,
[gameId] INT,
[name] VARCHAR(32)
)

CREATE TABLE Stat
(
[id] INT IDENTITY,
[gameId] INT,
[name] VARCHAR(32)
)


CREATE TABLE MiniStatData
(
[id] INT IDENTITY,
[miniId] INT,
[statId] INT,
[value] TEXT
)

CREATE TABLE User
(
[id] INT IDENTITY,
[name] VARCHAR(32)
)

CREATE TABLE MiniHaveWant
(
[id] INT IDENTITY,
[userId] INT,
[miniId] INT,
[have] INT,
[want] INT,
)

INSERT INTO Game(name) VALUES ('DND')
INSERT INTO Game(name) VALUES ('Star Wars')

INSERT INTO GameSet(gameId, name) VALUES (1, 'Harbinger')
INSERT INTO GameSet(gameId, name) VALUES (1, 'Dragoneye')
INSERT INTO GameSet(gameId, name) VALUES (2, 'Rebel Assault')

INSERT INTO Mini(gameSetId, gameId, name) VALUES (1, 1, 'Goblin
Sneak')
INSERT INTO Mini(gameSetId, gameId, name) VALUES (2, 1, 'Brass
Dragon')

INSERT INTO Stat(gameId, name) VALUES (1, 'Collectors_number')
INSERT INTO Stat(gameId, name) VALUES (1, 'Rarity')
INSERT INTO Stat(gameId, name) VALUES (1, 'Faction')

INSERT INTO MiniStatData(miniId, statId, value) VALUES (1, 1, '1')
INSERT INTO MiniStatData(miniId, statId, value) VALUES (1, 2,
'Common')
INSERT INTO MiniStatData(miniId, statId, value) VALUES (2, 3, 'Good')
INSERT INTO MiniStatData(miniId, statId, value) VALUES (2, 1, '3')
INSERT INTO MiniStatData(miniId, statId, value) VALUES (2, 2, 'Rare')
INSERT INTO MiniStatData(miniId, statId, value) VALUES (1, 3, 'Evil')

INSERT INTO User(name) VALUES ('Mike')
INSERT INTO User(name) VALUES ('Aaron')
INSERT INTO User(name) VALUES ('Bob')

INSERT INTO MiniHaveWant(userId, miniId, have, want) VALUES (1, 1, 12,
0)
INSERT INTO MiniHaveWant(userId, miniId, have, want) VALUES (1, 2, 0,
2)
INSERT INTO MiniHaveWant(userId, miniId, have, want) VALUES (2, 2, 0,
1)
INSERT INTO MiniHaveWant(userId, miniId, have, want) VALUES (3, 1, 0,
15)


Assume user = Mike
I want to create a table with the headings:
'Game, GameSet, Collectors_number, miniName, faction, rarity, have,
want
for example, a row (assuming user = Mike) would be:
DND, Harbinger, 1, Goblin Sneak, Evil, Common, 12, 0

the table will have i rows corresponding to i minis.

for i = 1 to #records(Mini)

some pseodocode I don't know how to form into SQL properly

'game = Game.name where Game.id = Mini.gameId where Mini.id = i

'set = GameSet.name where GameSet.id = Mini.gameSetId where Mini.id
= i

'# = MiniStatData.value where MiniStatData.statId = Stat.id where
Stat.name = 'collectors_number' and Stat.gameId = Game.id where
Game.id = Mini.gameId where Mini.id = i

'mini = Mini.name where Mini.id = i

'faction = MiniStatData.value where MiniStatData.statId = Stat.id
where Stat.name = 'faction' and Stat.gameId = Game.id where Game.id =
Mini.gameId where Mini.id = i

'rarity = MiniStatData.value where MiniStatData.statId = Stat.id
where Stat.name = 'rarity' and Stat.gameId = Game.id where Game.id =
Mini.gameId where Mini.id = i

'have = MiniHaveWant.have where MiniHaveWant.userId = me and
MiniHaveWant.miniId = Mini.id where Mini.id = i

'want = MiniHaveWant.want where MiniHaveWant.userId = me and
MiniHaveWant.miniId = Mini.id where Mini.id = i

'trade = MiniHaveWant.trade where MiniHaveWant.userId = me and
MiniHaveWant.miniId = Mini.id where Mini.id = i

next i

question 1:
How do I do this properly?

question 2:
I would like to make the table headings sortable. Before I normalized
the database, that was easy... I just made each heading a hyperlink
and asp'ed in number corresponding to which column to sort by, then at
the end of my sql statement I said "SORT BY " & fieldName,...but now I
have no idea how to implement the sort! Is it still practical?

question 3: How do I test out the CREATE TABLE, INSERT INTO etc...
stuff before posting it on the newsgroups? I have Access and tried
modifying the variables (i.e., Text(32) instead of VARCHAR(32)) before
pasting it into a Query in Access but I keep getting a syntax error
when I try to save the query.







"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<eDcfA3FgEHA.1196@TK2MSFTNGP11.phx.gbl>...

> Mike wrote:

> > Hello,
> >
> > I wrote some code below to retrieve data from these tables:
> >
> > Stat
> > ---
> > id
> > gameId
> > name
> >
> > MiniStatData
> > ---
> > id
> > miniId
> > statId
> > statValue
> >
> > Mini
> > ---
> > id
> > gameId
> >
> > Stat corresponds to a table of variable names and MiniStatData
> > corresponds to a table of variable values. Each Mini can have
> > multiple stats, and each stat has a value.
> >
> > The first problem I ran into is that it is obviously not very robust-
> > if I am missing a value for one of the stats, all the values that
> > follow will be shifted by 1 column on the table.

>
> Huh? What table?
>
> Show us a few rows of sample data from each source table (in tabular format)
> and then show us the desired results (again in tabular format). Please
> include the data that causes the problem you are trying to solve in your
> sample data, and show the incorrect results (in tabular format)
>
>

> >
> > The second problem I ran into is how to sort by the column headings (I
> > hyperlinked them so I can click on them to send a sort-by value back
> > to the page). The second SQL statement below has two ORDER BY parts
> > and I can't really figure out how to order the whole table by the
> > values themselves given the way the database is layed out.
> >

> Same request as above: sample data, current results, desired results: all in
> tabular format.
>
> Only show us the minimum number of fields required to illustrate the
> problem.
>
> Bob Barrows

[Bob Barrows [MVP]]

"User" and "name" are reserved keywords, so I would suggest changing them.

Mike wrote:

I needed to see your primary and foreign keys.

>
>
> Assume user = Mike
> I want to create a table

Do you mean an html table? In the future, you can avoid confusion by calling
this a "resultset"

> with the headings:
> 'Game, GameSet, Collectors_number, miniName, faction, rarity, have,
> want
> for example, a row (assuming user = Mike) would be:
> DND, Harbinger, 1, Goblin Sneak, Evil, Common, 12, 0

I needed to see all the rows that you wanted the query to return from the
sample data. Not just one. How will I know if I have the query correct? Or
do you mean that you want the query to return only a single row ...?

>
> the table will have i rows corresponding to i minis.

No, I guess you want more than one row ... :-)

>
> for i = 1 to #records(Mini)
>
> some pseodocode I don't know how to form into SQL properly

The pseudocode could have been avoided by simply showing me the desired
results, followed by an explanation of how the results were to be achieved
.... :-)

>
> 'game = Game.name where Game.id = Mini.gameId where Mini.id = i
>
> 'set = GameSet.name where GameSet.id = Mini.gameSetId where Mini.id
> = i
>
> '# = MiniStatData.value where MiniStatData.statId = Stat.id where
> Stat.name = 'collectors_number' and Stat.gameId = Game.id where
> Game.id = Mini.gameId where Mini.id = i

hmm, I guess you mean "Collectors_Number"

>
> 'mini = Mini.name where Mini.id = i
>
> 'faction = MiniStatData.value where MiniStatData.statId = Stat.id
> where Stat.name = 'faction' and Stat.gameId = Game.id where Game.id =
> Mini.gameId where Mini.id = i
>
> 'rarity = MiniStatData.value where MiniStatData.statId = Stat.id
> where Stat.name = 'rarity' and Stat.gameId = Game.id where Game.id =
> Mini.gameId where Mini.id = i
>
> 'have = MiniHaveWant.have where MiniHaveWant.userId = me and

??? what do you mean by "me"? "Mike"?

> MiniHaveWant.miniId = Mini.id where Mini.id = i
>
> 'want = MiniHaveWant.want where MiniHaveWant.userId = me and
> MiniHaveWant.miniId = Mini.id where Mini.id = i
>
> 'trade = MiniHaveWant.trade where MiniHaveWant.userId = me and
> MiniHaveWant.miniId = Mini.id where Mini.id = i

??? You did not provide a column called "trade".

>
> next i
>
> question 1:
> How do I do this properly?

I'm still not sure if I should limit the records to "Mike", but I will
assume that's what you want.


To tell you the truth, I don't understand this design at all. Why does the
Stat name depend on the gameid?

But be that as it may, see if this query gives you the correct results:

SELECT g.name AS Game, gs.name AS Gameset, c.value AS Collectors_Number,
m.name AS MiniName, f.value AS Faction, r.value AS Rarity,
mh.have, mh.want, u.name
FROM MiniHaveWant AS mh INNER JOIN [User] AS u ON mh.userId = u.id
INNER JOIN Mini AS m ON mh.miniId = m.id
INNER JOIN (
SELECT s.id, s.gameId, ms.miniId, ms.value
FROM MiniStatData ms INNER JOIN Stat s ON ms.statId = s.id
WHERE s.[name]='Collectors_Number'
) AS c ON c.gameId = m.gameId AND c.miniId = m.id
INNER JOIN (
SELECT s.id, s.gameId, ms.miniId, ms.value
FROM MiniStatData ms INNER JOIN Stat s ON ms.statId = s.id
WHERE s.[name]='Faction') AS f ON f.gameId = m.gameId AND f.miniId =
m.id
INNER JOIN (
SELECT s.id, s.gameId, ms.miniId, ms.value
FROM MiniStatData ms INNER JOIN Stat s ON ms.statId = s.id
WHERE s.[name]='rarity') AS r ON r.gameId = m.gameId AND r.miniId = m.id
INNER JOIN GameSet AS gs ON m.gameSetId = gs.id AND m.gameId = gs.gameId
INNER JOIN Game AS g ON gs.gameId = g.id

It returns these results:

DND | Dragoneye | 3 | Brass Dragon | Good | Rare | 0 | 2 | Mike
DND | Harbinger | 1 | Goblin Sneak | Evil | Common | 12 | 0 | Mike
DND | Dragoneye | 3 | Brass Dragon | Good | Rare | 0 | 1 | Aaron
DND | Harbinger | 1 | Goblin Sneak | Evil | Common | 0 | 15 | Bob



I'm not going to address the following question (#2) until I determine that
I correctly understand your database design.

>
> question 2:
> I would like to make the table headings sortable. Before I normalized
> the database, that was easy... I just made each heading a hyperlink
> and asp'ed in number corresponding to which column to sort by, then at
> the end of my sql statement I said "SORT BY " & fieldName,...but now I
> have no idea how to implement the sort! Is it still practical?
>
> question 3: How do I test out the CREATE TABLE, INSERT INTO etc...
> stuff before posting it on the newsgroups? I have Access and tried
> modifying the variables (i.e., Text(32) instead of VARCHAR(32)) before
> pasting it into a Query in Access but I keep getting a syntax error
> when I try to save the query.
>

If you're using SQL Server as it appears, given the use of the IDENTITY
attribute, just use Query Analyzer in a test database ...

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike]

"Bob Barrows [MVP]" <SPAMcom> wrote in message > "User" and "name" are reserved keywords, so I would suggest changing them.

ok
 
>
> Do you mean an html table? In the future, you can avoid confusion by calling
> this a "resultset"
> 
>
> I needed to see all the rows that you wanted the query to return from the
> sample data. Not just one. How will I know if I have the query correct? Or
> do you mean that you want the query to return only a single row ...?
> 
>
> No, I guess you want more than one row ... :-)
> 
>
> The pseudocode could have been avoided by simply showing me the desired
> results, followed by an explanation of how the results were to be achieved
> ... :-)
> 
>
> hmm, I guess you mean "Collectors_Number"
> 
>
> ??? what do you mean by "me"? "Mike"?[/ref]

yes
 
>
> ??? You did not provide a column called "trade".[/ref]

my mistake, I removed extraneous columns from the database I listed in
the post but forgot to remove this from the pseudocode
 
>
> I'm still not sure if I should limit the records to "Mike", but I will
> assume that's what you want.
>
>
> To tell you the truth, I don't understand this design at all. Why does the
> Stat name depend on the gameid?[/ref]

A stat is a piece of information about the mini. Each game (with a
unique gameId) may have a completely different set of stats for a
mini. For example, one game may have a mini with "strength",
"intelligence", "hit points", "attack", while another game may only
have stats called "health" and "power". Therefore, you have to check
which game it is before you know what stats you need to list.

 


This is a great help! When I was writing out the pseudocode, I
figured out that I knew what I wanted and that it was possible to form
an SQL statement to get it, because I traced all the data output back
to a Mini.id. Therefore, every piece of data is a function of
Mini.id. I would find it extremely instructive if you could explain a
little about how you went about composing the SQL statement below.
Did you do it in steps (i.e., from aggregating smaller SQL
statements?)? If so, what steps did you do? If you could explain it
from the standpoint of converting the pseudocode into the SQL
statement, that would be great (because I understand the logic in the
pseudocode).

 
This isn't limited to only "Mike"... how would I do that? 

your returned data is correct except that it includes data from Aaron
and Bob, not just Mike
 
>
> If you're using SQL Server as it appears, given the use of the IDENTITY
> attribute, just use Query Analyzer in a test database ...[/ref]


I entered the information manually based on an example from
aspfaq.com. I don't have SQL Server, I have Access, but when I try to
test the stuff I entered before I posted it, I couldn't get Access to
run it in a Query, even after changing the variables to the Access
versions- how do you go about testing the statements above in Access?

 

[Bob]

Mike wrote: 
>>
>> Do you mean an html table? In the future, you can avoid confusion by
>> calling
>> this a "resultset"
>> 
>>
>> I needed to see all the rows that you wanted the query to return
>> from the
>> sample data. Not just one. How will I know if I have the query
>> correct? Or
>> do you mean that you want the query to return only a single row ...?
>> 
>>
>> No, I guess you want more than one row ... :-)
>> 
>>
>> The pseudocode could have been avoided by simply showing me the
>> desired
>> results, followed by an explanation of how the results were to be
>> achieved ... :-)
>> 
>>
>> hmm, I guess you mean "Collectors_Number"
>> 
>>
>> ??? what do you mean by "me"? "Mike"?[/ref]
>
> yes
> 
>>
>> ??? You did not provide a column called "trade".[/ref]
>
> my mistake, I removed extraneous columns from the database I listed in
> the post but forgot to remove this from the pseudocode
> 
>>
>> I'm still not sure if I should limit the records to "Mike", but I
>> will
>> assume that's what you want.
>>
>>
>> To tell you the truth, I don't understand this design at all. Why
>> does the
>> Stat name depend on the gameid?[/ref]
>
> A stat is a piece of information about the mini. Each game (with a
> unique gameId) may have a completely different set of stats for a
> mini. For example, one game may have a mini with "strength",
> "intelligence", "hit points", "attack", while another game may only
> have stats called "health" and "power". Therefore, you have to check
> which game it is before you know what stats you need to list.[/ref]

So you plan to have multiple stat records with the same stat id,
differentiated by the gameid column? The sample data you provided only
included rows for gameid 1, so it was hard for me to tell what the table's
primary key was. I still think that using the gameid overly complicates this
relationship, and will make it hard to create truly generic queries to
retrieve data, but it's your database...
 
>
>
> This is a great help! When I was writing out the pseudocode, I
> figured out that I knew what I wanted and that it was possible to form
> an SQL statement to get it, because I traced all the data output back
> to a Mini.id. Therefore, every piece of data is a function of
> Mini.id. I would find it extremely instructive if you could explain a
> little about how you went about composing the SQL statement below.
> Did you do it in steps (i.e., from aggregating smaller SQL
> statements?)?[/ref]

Yes. Sort of.
 


I started off with the basic stuff. I could see from your statement " the
table will have i rows corresponding to i minis" that the results would be
driven from the Mini table, so I started with a query containing only that
table, returning only the column needed in your resultset:

SELECT m.[name] AS MiniName
FROM Mini As m

I tested it to verify that it returned two records.
Then, since it appeared that Gameset was a "bridge" table between Mini and
Game, I added Gameset to the query:

SELECT m.[name] AS MiniName, gs.name AS Gameset
FROM Mini As m INNER JOIN GameSet AS gs
ON m.gameSetId = gs.id AND m.gameId = gs.gameId

Again, a quick test to make sure only two records were returned.

I then tackled the Stat thing, realizing I needed to have subqueries to get
the appropriate values. And finally i added the user's haves and wants.
 
> This isn't limited to only "Mike"... how would I do that?[/ref]

Oops, I forgot to add the WHERE clause:

WHERE u.[name]='Mike'
 
>
> your returned data is correct except that it includes data from Aaron
> and Bob, not just Mike
>[/ref]
 [/ref][/ref]

The statements you provided are valid for SQL Server, not Access. I could
not understand why you were using Access to test SQL Server statements ....
 
>>
>> If you're using SQL Server as it appears, given the use of the
>> IDENTITY
>> attribute, just use Query Analyzer in a test database ...[/ref]
>
>
> I entered the information manually based on an example from
> aspfaq.com. I don't have SQL Server, I have Access,[/ref]

Oh! That explains why you did not include your primary and foreign key DDL.
In the future, always tell us what database you are using to avoid these
misunderstandings.

You were able to run the above query in the Access Query Builder??? It's
not a valid JetSQL query! This will need to be done over using the Query
Builder.
 

Rewrite them using Jet syntax (look up the JetSQL Reference in Access online
help). [/ref]

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike]

SELECT g.name AS Game, gs.name AS Gameset, c.value AS
Collectors_Number,
m.name AS MiniName, f.value AS Faction, r.value AS Rarity,
mh.have, mh.want, u.name
FROM MiniHaveWant AS mh INNER JOIN [User] AS u ON mh.userId = u.id
INNER JOIN Mini AS m ON mh.miniId = m.id
INNER JOIN (
SELECT s.id, s.gameId, ms.miniId, ms.value
FROM MiniStatData ms INNER JOIN Stat s ON ms.statId = s.id
WHERE s.[name]='Collectors_Number'
) AS c ON c.gameId = m.gameId AND c.miniId = m.id
INNER JOIN (
SELECT s.id, s.gameId, ms.miniId, ms.value
FROM MiniStatData ms INNER JOIN Stat s ON ms.statId = s.id
WHERE s.[name]='Faction') AS f ON f.gameId = m.gameId AND
f.miniId =
m.id
INNER JOIN (
SELECT s.id, s.gameId, ms.miniId, ms.value
FROM MiniStatData ms INNER JOIN Stat s ON ms.statId = s.id
WHERE s.[name]='rarity') AS r ON r.gameId = m.gameId AND r.miniId
= m.id
INNER JOIN GameSet AS gs ON m.gameSetId = gs.id AND m.gameId =
gs.gameId
INNER JOIN Game AS g ON gs.gameId = g.id

I tried putting the above SQL statement into an Access 2000 query but
it gives me "Syntax error (missing operator) in query expression..."
it highlights the "e, gs." in AS Game, gs.name (at the start of the
statement) after I click the error message away with the OK button. I
considered perhaps it was the reserved keywords that you mentioned at
the beginning of the post, so I tried replacing 'name' with 'qname'
throughout the tables and the query, and changed User to Player, but I
get the same error. Could you try running it as an Access query and
see if you get the same error?

Thanks,
Mike

[Bob]

As I said in my previous message, this query was written with the assumption
that you were using SQL Server. It needs to be rewritten to allow it to run
in Access (the joins need to be grouped within parentheses). I don't have
time to tackle this right now. Hopefully smebody else in the group will
"pick up the ball" here.

Bob Barrows
Mike wrote: 

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob]

Actually, the only real problem with this statement as far as Jet is
concerned is the lack of grouping parentheses in the join statements. It
would be a good exercise for you to correct this yourself. Fire up the
Access query builder and use the gu (design view)i to build a query
containing three or more table, drawing the joins between the tables. I
usually use the Properties dialog to create aliases for each of the tables.
Pull a couple fields from the tables into the grid. Then switch to SQL View
to see what the sql statement is supposed to look like. Note the use of
parntheses in the FROM clause.

I would create saved queries (Views) for each of the subqueries in the
statement I provided in order to simplify it. If you create a saved query
called "qCollectors", you can substitute "qCollectors" for "( SELECT s.id,
s.gameId, ms.miniId, ms.value FROM MiniStatData ms INNER JOIN Stat s ON
ms.statId = s.id WHERE s.[name] = 'Collectors_Number')".

Bob Barrows


Mike wrote: 

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike]

"Bob Barrows [MVP]" <SPAMcom> wrote in message news:<#phx.gbl>... 

Actually I figured that out yesterday around 2 am after a couple hours
of playing around with your SQL in an access query. I bought a book
today called "SQL Queries for Mere Mortals" which is actually quite
good (I chose it because it seemed to be the only one in Barnes and
Noble that actually discussed Left Joins in any kind of depth). After
some studying of the left join chapter, combined with the accumulated
knowledge from bugging the hell out of you(Bob), I concocted this
(because I wanted to understand how to do it), which surprised me by
working:

SELECT [Mini].[id], [Mini].[name], MHFiltered.have, MHFiltered.want,
cNumber, Rarity, Faction
FROM (((Mini LEFT JOIN [Select miniId, have, want From MiniHaveWant
Where userId =1]. AS MHFiltered ON MHFiltered.miniId=[Mini].[id]) LEFT
JOIN [Select statValue AS cNumber, miniId From MiniStatData Where
MiniStatData.statId = 2]. AS MSDFiltered ON
MSDFiltered.miniId=[Mini].[id]) LEFT JOIN [Select statValue AS Rarity,
miniId From MiniStatData Where MiniStatData.statId = 4]. AS
MSDFiltered2 ON MSDFiltered2.miniId=[Mini].[id]) LEFT JOIN [Select
statValue AS Faction, miniId From MiniStatData Where
MiniStatData.statId = 7]. AS MSDFiltered3 ON
MSDFiltered3.miniId=[Mini].[id]
WHERE [Mini].[gameSetId]=2
ORDER BY [Mini].[name];

See, all those posts weren't a waste after all :))

Here was a key line from the book that really helped:
"You can substitute an entire SELECT statement for any table name in
your FROM clause. Of course, you must assign a correlation name so
that the RESULT of evaluating your embedded query has a name"

Here's something for the FAQ...
I noticed what seems to be some sort of bug in Access 2000, when
embedded SELECT statements (like the ones above) with parenthesis are
put in the query builder. I can save the query just fine, but when I
reopen it, Access sometimes replaces all of the parenthesis with
brackets, for example (Select bla bla bla) becomes [Select bla bla
bla]. <-- note the period! When the period is there, the query
works. When the period isn't there (and somehow it disappears in
Access), it breaks! Access spits out this when I try to save the query
again:

"The Microsoft Jet database engine cannot find the input table or
query 'Select Mini.id, ...'. Make sure it exists and that it is
spelled correctly."

This happened to me several times. It seems almost random whether
Access will replace the parenthesis or not (but it is a function of
the SQL statement itself, it seems- earlier, shorter versions of the
above SQL would cause Access to break the code, but the version above
seems stable), and if you edit the SQL in other sections, it sometimes
deletes the period after the closing bracket, causing the error
message above.

Any suggestions on how to make the above SQL statement better would be
appreciated.

-Mike

[Mike]

I just found an example of Access mangling my code. I tried adding
two more columns to the Result Set. Here is what happens...

Here's the copy I keep in my text editor:

SELECT Mini.id, Mini.name, MHFiltered.have, MHFiltered.want, cNumber,
Rarity, Faction, MHFiltered.trade, Game.name, GameSet.name FROM
(((((Mini
LEFT JOIN (Select miniId, have, want, trade From MiniHaveWant Where
userId =1) AS MHFiltered ON MHFiltered.miniId=Mini.id)
LEFT JOIN (Select statValue AS cNumber, miniId From MiniStatData Where
MiniStatData.statId = 2) AS MSDFiltered ON
MSDFiltered.miniId=Mini.id)
LEFT JOIN (Select statValue AS Rarity, miniId From MiniStatData Where
MiniStatData.statId = 4) AS MSDFiltered2 ON
MSDFiltered2.miniId=Mini.id)
LEFT JOIN (Select statValue AS Faction, miniId From MiniStatData Where
MiniStatData.statId = 7) AS MSDFiltered3 ON
MSDFiltered3.miniId=Mini.id)
LEFT JOIN Game ON Game.id = Mini.gameId)
LEFT JOIN GameSet ON GameSet.id = Mini.gameSetId
WHERE Mini.gameSetId=2
ORDER BY Mini.name

Note that I added the last two LEFT JOIN statements since my last
post.

I paste that mess into a new Access query and save it. No errors.
Then I try to run it. I get this:

The Microsoft Jet Database engine cannot find the input table or query
'Select miniId, have, want, trade From MiniHaveWant Where userId = 1'.
Make sure it exists and that its name is spelled correctly.

I open up the query... now look at it:

SELECT [Mini].[id], [Mini].[name], MHFiltered.have, MHFiltered.want,
cNumber, Rarity, Faction, MHFiltered.trade, [Game].[name],
[GameSet].[name]
FROM (((((Mini LEFT JOIN [Select miniId, have, want, trade From
MiniHaveWant Where userId =1] AS MHFiltered ON
MHFiltered.miniId=[Mini].[id]) LEFT JOIN [Select statValue AS cNumber,
miniId From MiniStatData Where MiniStatData].[statId = 2] AS
MSDFiltered ON MSDFiltered.miniId=[Mini].[id]) LEFT JOIN [Select
statValue AS Rarity, miniId From MiniStatData Where
MiniStatData].[statId = 4] AS MSDFiltered2 ON
MSDFiltered2.miniId=[Mini].[id]) LEFT JOIN [Select statValue AS
Faction, miniId From MiniStatData Where MiniStatData].[statId = 7] AS
MSDFiltered3 ON MSDFiltered3.miniId=[Mini].[id]) LEFT JOIN Game ON
[Game].[id]=[Mini].[gameId]) LEFT JOIN GameSet ON
[GameSet].[id]=[Mini].[gameSetId]
WHERE [Mini].[gameSetId]=2
ORDER BY [Mini].[name];

I think the thing the Access is screwing up is the parenthesis/bracket
insertion. I think the error above is at the "Where userId=1] " part.
In my original code, that bracket was a parenthesis. Access only
changed one of them- the open parenthesis to the left of Mini LEFT
JOIN is still a parenthesis. I tried changing it to a left bracket
but then I get a syntax error.

I decided to try the orginal (un-Access-mangled) query from asp. It
works fine! Here is my string:

sql = "SELECT Mini.id, Mini.name, MHFiltered.have, MHFiltered.want,
cNumber, Rarity, Faction, MHFiltered.trade, Game.name, GameSet.name
FROM (((((Mini LEFT JOIN (Select miniId, have, want, trade From
MiniHaveWant Where userId =1) AS MHFiltered ON
MHFiltered.miniId=Mini.id) LEFT JOIN (Select statValue AS cNumber,
miniId From MiniStatData Where MiniStatData.statId = 2) AS MSDFiltered
ON MSDFiltered.miniId=Mini.id) LEFT JOIN (Select statValue AS Rarity,
miniId From MiniStatData Where MiniStatData.statId = 4) AS
MSDFiltered2 ON MSDFiltered2.miniId=Mini.id) LEFT JOIN (Select
statValue AS Faction, miniId From MiniStatData Where
MiniStatData.statId = 7) AS MSDFiltered3 ON
MSDFiltered3.miniId=Mini.id) LEFT JOIN Game ON Game.id = Mini.gameId)
LEFT JOIN GameSet ON GameSet.id = Mini.gameSetId WHERE
Mini.gameSetId=2 ORDER BY Mini.name"

So, does anyone know how to 1) turn off Access's SQL-mangle feature,
or 2) fix the mangled SQL so that Access stops messing with it?

[Bob]

Mike wrote: 

As I recall, Access only mangles the SQL if you open the query in Design
View and then switch to SQL View. As long as you save the query while it's
in SQL View, so that it opens in SQL View the next time you open it, it
should leave your SQL alone.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike]

"Bob Barrows [MVP]" <SPAMcom> wrote in message news:<phx.gbl>... 
>
> As I recall, Access only mangles the SQL if you open the query in Design
> View and then switch to SQL View. As long as you save the query while it's
> in SQL View, so that it opens in SQL View the next time you open it, it
> should leave your SQL alone.[/ref]

I actually found that exact piece of advice in a search of the
newsgroups, but I still can't figure out what people mean.

Looking at Access 2000's main window, I click on "Queries" on the
Objects menu on the left. From there I am presented with a list of
existing queries. I customized my toolbar to have the "SQL View"
button as well as the "Query type: Select" button. I can't click
either button until I first make a query, however, and the only way I
can seem to do that is to use "Create a query in design view" first.
When I am in design view I immediately press cancel on the "Show
Table" dialog box that comes up and then I press "SQL View" and write
my SQL. However, like you say, Access then mangles the code when I
save it. I also tried the Insert, Query menu but that comes up with
only Design View and wizard choices. So, how do I get to SQL View
directly??

-Mike

[Bob]

Mike wrote: 
>>
>> As I recall, Access only mangles the SQL if you open the query in
>> Design
>> View and then switch to SQL View. As long as you save the query
>> while it's
>> in SQL View, so that it opens in SQL View the next time you open it,
>> it
>> should leave your SQL alone.[/ref]
>
> I actually found that exact piece of advice in a search of the
> newsgroups, but I still can't figure out what people mean.
>
> Looking at Access 2000's main window, I click on "Queries" on the
> Objects menu on the left. From there I am presented with a list of
> existing queries. I customized my toolbar to have the "SQL View"
> button as well as the "Query type: Select" button. I can't click
> either button until I first make a query, however, and the only way I
> can seem to do that is to use "Create a query in design view" first.
> When I am in design view I immediately press cancel on the "Show
> Table" dialog box that comes up and then I press "SQL View" and write
> my SQL. However, like you say, Access then mangles the code when I
> save it. I also tried the Insert, Query menu but that comes up with
> only Design View and wizard choices. So, how do I get to SQL View
> directly??
>
> -Mike[/ref]

It's been years since I really used Access. I seem to recall that if I saved
and closed the query while in SQL View, the next time I clicked Design on
the query, it opened directly in SQL View. Has that behavior changed?

I just tried it. Opened an existing query in Design View, switched to SQL
View, saved, closed, and reopened in design view. It opened directly in SQL
View.

This isn't really an ASP question. You will probably get a better response
if you ask it in one of the Access newsgroups.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"