Professional Web Applications Themes

Need help with SQL statement for JOIN-type stuff - ASP Database

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 ...

  1. #1

    Default Need help with SQL statement for JOIN-type stuff

    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
    Mike Guest

  2. #2

    Default Re: Need help with SQL statement for JOIN-type stuff

    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"


    Bob Barrows [MVP] Guest

  3. #3

    Default Re: Need help with SQL statement for JOIN-type stuff

    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]" <reb01501NOyahoo.SPAMcom> wrote in message news:<eDcfA3FgEHA.1196TK2MSFTNGP11.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
    Mike Guest

  4. #4

    Default Re: Need help with SQL statement for JOIN-type stuff

    "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 yzer 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"


    Bob Barrows [MVP] Guest

  5. #5

    Default Re: Need help with SQL statement for JOIN-type stuff

    "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 yzer 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?

     
    Mike Guest

  6. #6

    Default Re: Need help with SQL statement for JOIN-type stuff

    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 yzer 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"


    Bob Guest

  7. #7

    Default Re: Need help with SQL statement for JOIN-type stuff

    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
    Mike Guest

  8. #8

    Default Re: Need help with SQL statement for JOIN-type stuff

    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 Guest

  9. #9

    Default Re: Need help with SQL statement for JOIN-type stuff

    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"


    Bob Guest

  10. #10

    Default Re: Need help with SQL statement for JOIN-type stuff

    "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 aculated
    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 Guest

  11. #11

    Default Re: Need help with SQL statement for JOIN-type stuff

    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 ing 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?
    Mike Guest

  12. #12

    Default Re: Need help with SQL statement for JOIN-type stuff

    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"


    Bob Guest

  13. #13

    Default Re: Need help with SQL statement for JOIN-type stuff

    "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
    Mike Guest

  14. #14

    Default Re: Need help with SQL statement for JOIN-type stuff

    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"


    Bob Guest

Similar Threads

  1. Replies: 0
    Last Post: November 1st, 03:08 PM
  2. #39334 [NEW]: Value und type of var changes in if statement
    By sw4u at gmx dot net in forum PHP Bugs
    Replies: 0
    Last Post: November 1st, 02:47 PM
  3. Type mismatch in assignment statement
    By Digital Hand in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: April 24th, 10:43 AM
  4. Comma Seperation List and Join Statement
    By Flashm@n in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 13th, 04:19 PM
  5. Replies: 2
    Last Post: September 8th, 11:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139