-
Jun 7th, 2018, 09:12 AM
#1
Thread Starter
Member
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.
-
Jun 7th, 2018, 09:29 AM
#2
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.
-
Jun 7th, 2018, 09:52 AM
#3
Thread Starter
Member
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
-
Jun 7th, 2018, 10:24 AM
#4
Re: Randomize from 2 table
Are Table1 and Table2 related by ID1 and ID2 and are those unambiguous column names?
-
Jun 7th, 2018, 10:37 AM
#5
Thread Starter
Member
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.
-
Jun 7th, 2018, 11:40 AM
#6
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
-
Jun 7th, 2018, 12:11 PM
#7
Thread Starter
Member
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
-
Jun 7th, 2018, 01:12 PM
#8
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
-
Jun 7th, 2018, 02:43 PM
#9
Thread Starter
Member
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
-
Jun 7th, 2018, 03:22 PM
#10
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
-
Jun 7th, 2018, 03:39 PM
#11
Thread Starter
Member
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
-
Jun 12th, 2018, 09:18 AM
#12
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|