Results 1 to 12 of 12

Thread: Randomize from 2 table

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Randomize from 2 table

    Hello Forums
    Please i ask for your help
    I have this query which works very well .. I can randomize Columns ( ID1 - Team1 - Team2 ) From (Table1) and display them in DataGridView. How to add at this query another column named Team3 from another table (Table2) and randomize it all together?
    Table2 contains fields (ID2 - Team3)
    My query :
    Code:
    Dim rows As Integer
    Dim sql As String = String.Format("SELECT Top {0} ID1, Team1, Team2 FROM Table1  ORDER BY RND(-(100000*ID1)*Time())", rows)
    I tried like this but I did not succeed :
    Code:
    Dim sql As String = String.Format("SELECT Top {0} ID1, Team1, Team2 FROM Table1  INNER JOIN Team3 FROM Table2 ORDER BY RND(-(100000*ID1)*Time())", rows)
    I found this code it work very very well but sadly is in SQL server database while I have MS Access database
    Code:
                Dim sql As String = String.Format(" SELECT TOP {0} ID1,Team1,Team2,Team3 FROM (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS F1, ID1, Team1, Team2 FROM Table1 ) as t1 INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS F1,Team3 FROM Table2 )  as t2 on t1.f1 = t2.f1", rows)
    Thank you in advance
    ABI
    Last edited by ABIDINE; Jun 7th, 2018 at 07:42 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Randomize from 2 table

    The randomisation part doesn't change. The issue is that you don't know the syntax for an inner join.
    Code:
    SELECT ID1, Team1, Team2, Team3
    FROM Table1 INNER JOIN Table2
    ON ID1 = ID2
    I just took a guess at the columns involved in the foreign key relationship between the two tables but you can correct that as required. Basically, you specify all the columns to be projected from all tables, then specify the tables and the columns that relate them. There's a link to a SQL tutorial in my signature below that you might benefit from perusing.

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Randomize from 2 table

    I make like this but i have an error ( Expression de jointure non supporté ) ....Unsupported join expression
    Code:
     Dim sql As String = String.Format("SELECT Top {0} ID1, Team1, Team2, Team3 FROM Table1  INNER JOIN Table2 ON ID1 = ID2 ORDER BY RND(-(100000*ID1)*Time())", rows)
    Thank you

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Randomize from 2 table

    Are Table1 and Table2 related by ID1 and ID2 and are those unambiguous column names?

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Randomize from 2 table

    Not both tables are related by ID1 and ID2 .. I have no ambiguous name ..
    I tested the code found in my first post and it works very well with Sql server database .. can I change it at MS Access database .. I tried to delete the words (Order by NewId) and replace them only by the word (Order by ID) .. but always not succeed.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Randomize from 2 table

    newID() is a function that returns a new GUID (uniqueIdenftifier) type, so you might be able to just concatenate in Guid.NewGuid in place of newID().

    Otherwise, I'd try the statement without the ORDER BY clause in Access to see what it doesn't like about the JOIN. Heck, I'd try that anyways. That statement is much shorter, and likely performs much better, than the more convoluted example that uses newID().
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Randomize from 2 table

    Thank you Shaggy Hiker very much for help
    Very nice of you
    I tried like this but always i have an error
    Code:
     Dim sql As String = String.Format("Select Top(10) ID1,Team1,Team2,ID2,Team3  From  (select  ROW_NUMBER() OVER(ORDER BY Guid.NewGuid() ASC) AS F1,ID1,Team1,Team2 from Table1) as t1 inner join  (select   ROW_NUMBER() OVER(ORDER BY Guid.NewGuid() ASC) AS F1,ID2, Team3  from Table2)  as t2 on t1.f1 = t2.f1 )", rows)
                InfoCommand = New OleDbCommand(Sql, Con_access)
                InfoAdapter = New OleDbDataAdapter()
                InfoAdapter.SelectCommand = InfoCommand
                InfoTable = New DataTable()
                InfoAdapter.Fill(InfoTable)
                DataGridView2.DataSource = InfoTable

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Randomize from 2 table

    Yeah, that's not what I meant. Concatenating in a GUID is a whole lot different. Try it like this:

    "Select Top(10) ID1,Team1,Team2,ID2,Team3 From (select ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1,ID1,Team1,Team2 from Table1) as t1 inner join (select ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1,ID2, Team3 from Table2) as t2 on t1.f1 = t2.f1 )", rows)

    I may have something wrong in there. In SQL Server, a GUID added like this would be wrapped in single quotes. In Access, I think it has to be wrapped in curly braces, but it might be single quotes AND curly braces, or it might just be single quotes.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Randomize from 2 table

    Thank you very much Shaggy Hiker
    Really i have a Database named Factory
    I have a Table1 with fields ( ID1 - Team1 - Team2 )
    I have a Table2 with fields ( ID2 - Team3 )
    I want choose ( ID1 - Team1 - Team2 - Team3 ) and randomize all together
    I tried like this :
    Code:
                Dim sql As String = String.Format("Select Top(10) ID1,Team1,Team2,ID2,Team3 From (select ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1,ID1,Team1,Team2 from Table1) as t1 inner join (select ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1,ID2, Team3 from Table2) as t2 on t1.f1 = t2.f1 )", rows)
    But i have an error ( The index (zero base) must be greater than or equal to zero and less than the size of the argument list )
    Thank you for best follow and for help
    ABI

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Randomize from 2 table

    This is really a query question, so I moved the thread to Database Development.

    Frankly, I still like the other query better. This one seems excessively complicated.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Randomize from 2 table

    what is the other query master Shaggy please ..if you want I will put all the code in my Form1

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Randomize from 2 table

    I was talking about the first query you had. You were just having issues with the JOIN in post #3. If you could get past that, it's a simpler query, and easier to understand. The error suggest that the problem was something simple, too. Writing the query without the ORDER BY statement in some query designer would be the first step. That would likely show what was wrong with the JOIN. You could then add the ORDER BY statement back in and see whether that was all working. Once the query was working, then you could format things.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width