-
Feb 8th, 2021, 06:25 AM
#1
Thread Starter
Fanatic Member
Need help with this query
Last edited by newbie2; Feb 8th, 2021 at 06:47 AM.
-
Feb 8th, 2021, 07:10 AM
#2
Re: Need help with this query
You have ID of 2 in your Item_Tbl table that is colored RED but you color the row with Item_Id of "1" in you Tbl_Debt.
You requirement is flawed or not drawn properly.
Are these tables of your own design and are you stuck with these names?
-
Feb 8th, 2021, 07:48 AM
#3
Re: Need help with this query
He only compares Item_ID
If he compares PID too, then he will get his result
Code:
StrSql = "Select * from Item_Tbl " & _
" inner join Tbl_Debt on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
" inner join Tbl_detail on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
" where Tbl_Debt.PID = 2 "
Last edited by Zvoni; Feb 8th, 2021 at 07:52 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 8th, 2021, 11:34 AM
#4
Thread Starter
Fanatic Member
Re: Need help with this query
Originally Posted by szlamany
You have ID of 2 in your Item_Tbl table that is colored RED but you color the row with Item_Id of "1" in you Tbl_Debt.
You requirement is flawed or not drawn properly.
Are these tables of your own design and are you stuck with these names?
You're right. I should have colored the first row.
Attachment 180137
-
Feb 8th, 2021, 11:46 AM
#5
Thread Starter
Fanatic Member
Re: Need help with this query
Originally Posted by Zvoni
He only compares Item_ID
If he compares PID too, then he will get his result
Code:
StrSql = "Select * from Item_Tbl " & _
" inner join Tbl_Debt on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
" inner join Tbl_detail on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
" where Tbl_Debt.PID = 2 "
Thank you but still having trouble
In this senario for example:
Attachment 180139
Code:
StrSql = "Select * from Item_Tbl " & _
" inner join Tbl_Debt on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
" inner join Tbl_detail on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
" where Tbl_Debt.PID = 1 "
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs.RecordCount
The expected result is 3
But I'm getting 5
thank you
Last edited by newbie2; Feb 8th, 2021 at 11:51 AM.
-
Feb 8th, 2021, 01:07 PM
#6
Re: Need help with this query
If something is too complex, break it up into steps.
In your case, I would define two separate Views:
1) the first view (vw_Debt_Detail) connects only the two tables:
.. Tbl_Debt and Tbl_Detail (via PID and Item_ID joining) ... easy to do, easy to verify - useful in your App also in this form
2) the second view (vw_Debt_Detail_Item) connects your just created view with another table:
.. vw_Debt_Detail and Item_Tbl (via join on Item_ID = ID)
HTH
Olaf
-
Feb 8th, 2021, 01:47 PM
#7
Thread Starter
Fanatic Member
Re: Need help with this query
Originally Posted by Schmidt
If something is too complex, break it up into steps.
Olaf
Thank you Olaf for this idea
When I use two separate Views, in the senarion above, I'm getting the right result
Code:
StrSql = "Select * from Item_Tbl " & _
" inner join Tbl_Debt on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
" where Tbl_Debt.PID = 1 "
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs.RecordCount
Or
Code:
StrSql = "Select * from Item_Tbl " & _
" inner join Tbl_detail on Item_Tbl. ID = Tbl_detail. Item.Id " & _
" where Tbl_detail.PID = 1 "
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs.RecordCount
Rs.RecordCount = 3
However When I use all in one view, I get incorrect result.
Code:
StrSql = "Select * from Item_Tbl " & _
" inner join Tbl_Debt on Item_Tbl. ID = Tbl_Debt .Item.Id " & _
" inner join Tbl_detail on Item_Tbl. ID = Tbl_detail. Item.Id and Tbl_Debt. PID = Tbl_detail. PID " & _
" where Tbl_Debt.PID = 1 "
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs.RecordCount
Rs.RecordCount = 5
-
Feb 8th, 2021, 03:57 PM
#8
Re: Need help with this query
You need to read more carefully - or ask when you do not understand a term (as e.g. View).
A View stores a certain SQL-QueryString under a given (View-)Name (in the DB, permanently) -
and that Views Name can then be used, to "callup" the whole SQL-Query, as if it was a "real Table".
A good analogy is, when you imagine an existing view like a:
"VariableName" (or a "virtual TableName"), that stands for a complex Select, but isn't a real table"
Here is the implementation of Step 1) from post #6 above (please read that part again, before you dive into the code here) -
the resulting RecordCount for the View: vw_Debt_Detail is - as expected 3 - not 5.
Code:
Private Cnn As cConnection, Rs As cRecordset
Private Sub Form_Load()
Set Cnn = New_c.Connection(, DBCreateInMemory)
Cnn.Execute "Create Table Tbl_Debt(PID Integer, Item_ID Integer, BrandID Integer, Dept Text)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 2)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 2)"
Cnn.Execute "Create Table Tbl_Detail(PID Integer, Item_ID Integer, BrandID Integer)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 2)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(2, 2)"
With New_c.ArrayList(vbString) 'Step 1) construction (via joined String-ArrayList)
.Add "Create View vw_Debt_Detail As"
.Add "Select Tbl_Debt.PID, Tbl_Debt.Item_ID, Tbl_Debt.BrandID, Tbl_Debt.Dept"
.Add "From Tbl_Debt Inner Join Tbl_Detail"
.Add "On Tbl_Debt.PID=Tbl_Detail.PID AND Tbl_Debt.Item_ID=Tbl_Detail.Item_ID"
Cnn.Execute .Join(vbCrLf)
End With
Set Rs = Cnn.OpenRecordset("Select * From vw_Debt_Detail Where PID=1") '<- a view can be used like a table
Debug.Print "vw_Debt_Detail", Rs.RecordCount, Rs.Fields.Count
End Sub
Now you should be able, to implement Step 2) entirely on your own -
which inner joins our new "table-like" View vw_Debt_Detail - with the normal table Item_Tbl.
Below is some code, to get you started:
Code:
With New_c.ArrayList(vbString) 'Step 2) construction
.Add "Create View vw_Debt_Detail_Item As"
'... to be enhanced by you, joining via vw_Debt_Detail.Item_ID and Item_Tbl.ID
Cnn.Execute .Join(vbCrLf)
End With
HTH
Olaf
Last edited by Schmidt; Feb 8th, 2021 at 04:12 PM.
-
Feb 8th, 2021, 04:14 PM
#9
Re: Need help with this query
Then there must be something wrong with your data...
Here is a script I just used to replicate your tables and data:
Code:
create table Item_Tbl (
ID INTEGER,
Item CHAR(10 CHAR)
);
create table Tbl_Debt (
PID INTEGER,
Item_ID INTEGER,
Dept INTEGER,
BrandId INTEGER
);
create table Tbl_detail (
PID INTEGER,
Item_ID INTEGER,
BrandID INTEGER
);
COMMIT;
insert into Item_Tbl (Id, Item) VALUES (1, 'aaa');
insert into Item_Tbl (Id, Item) VALUES (2, 'bbb');
insert into Item_Tbl (Id, Item) VALUES (3, 'ccccc');
insert into Item_Tbl (Id, Item) VALUES (4, 'dddd');
insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (1 ,1, 30, 1);
insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (1 ,2, 0, 1);
insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (1 ,2, 0, 1);
insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (2 ,1, 0, 1);
insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (3 ,4, 20, 1);
insert into Tbl_Debt (PID, Item_Id, Dept, BrandID) values (3 ,3, 0, 1);
insert into Tbl_detail (PID, Item_ID, BrandID) values (1,1,1);
insert into Tbl_detail (PID, Item_ID, BrandID) values (1,2,1);
insert into Tbl_detail (PID, Item_ID, BrandID) values (2,1,1);
insert into Tbl_detail (PID, Item_ID, BrandID) values (3,2,1);
insert into Tbl_detail (PID, Item_ID, BrandID) values (3,3,1);
COMMIT;
select *
from item_tbl I
inner join tbl_debt D on I.ID = D.Item_Id
inner join tbl_detail DT on D.Item_Id = DT.Item_Id and D.PID = DT.PID
where d.pid = 1;
drop table item_tbl;
drop table tbl_debt;
drop table tbl_detail;
commit;
I got three rows:
Code:
1,'aaa ',1,1,30,1,1,1,1
2,'bbb ',1,2,0,1,1,2,1
2,'bbb ',1,2,0,1,1,2,1
So either there's something else in your data, or there's something else in your query, that's off.
-tg
-
Feb 9th, 2021, 06:11 AM
#10
Thread Starter
Fanatic Member
Re: Need help with this query
Thank you Schmidt
Thank you techgnome
I'm sorry for being late in replying to you.
In fact I spent very long time testing your codes for different scenarios until 2 AM.
Both the codes give the same results in all the scenarios I tested.
However I'm still confused.
In some scenarios, the codes are giving unexpected results which is confusing me.
So I think I need a kind-hearted man to explain me why this is happening.
First scenario:
Code:
Set Cnn = New_c.Connection(, DBCreateInMemory)
Cnn.Execute "Create Table Item_Tbl(ID Integer, Item Text)"
Cnn.Execute "Insert Into Item_Tbl(ID, Item) Values(1, 'aaa')"
Cnn.Execute "Insert Into Item_Tbl(ID, Item) Values(2, 'bbb')"
Cnn.Execute "Insert Into Item_Tbl(ID, Item) Values(3, 'ccc')"
Cnn.Execute "Create Table Tbl_Debt(PID Integer, Item_ID Integer)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 2)"
Cnn.Execute "Create Table Tbl_Detail(PID Integer, Item_ID Integer)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 2)"
StrSql = "Select * from Item_Tbl I " & _
" inner join Tbl_Debt D on I.ID = D.Item_ID " & _
" inner join Tbl_Detail DT on I.ID = DT.Item_ID And D.PID = DT.PID" & _
" where D.PID = 1 "
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs.RecordCount
Cnn.Execute "drop table item_tbl;"
Cnn.Execute "drop table tbl_debt;"
Cnn.Execute "drop table tbl_detail;"
I expect Rs.RecordCount = 3
But here it is giving 5
Even with Schmidt's approach, it is giving 5
Code:
vw_Debt_Detail ....... 5 ....... 4
vw_Debt_Detail_Item ..... 5 ...... 3
In the next scenario I become more and more confused.
Code:
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
StrSql = "Select * from Item_Tbl I " & _
" inner join Tbl_Debt D on I.ID = D.Item_ID " & _
" inner join Tbl_Detail DT on I.ID = DT.Item_ID And D.PID = DT.PID" & _
" where D.PID = 1 "
Set Rs = Cnn.OpenRecordset(StrSql)
MsgBox Rs.RecordCount
Surprisingly the Rs.RecordCount is 9
And with Olaf's approach the result is:
Code:
vw_Debt_Detail ..... 9 ...... 4
vw_Debt_Detail_Item ..... 9 .... 3
With this this scenario it is giving 12
Code:
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
I'm totally lost.
Thank you again
Last edited by newbie2; Feb 9th, 2021 at 06:17 AM.
-
Feb 9th, 2021, 06:32 AM
#11
Re: Need help with this query
It is really clear to me what is going on. Let's just work with your first example
Code:
Create Table #Item_Tbl(ID Integer, Item Text)
Insert Into #Item_Tbl(ID, Item) Values(1, 'aaa')
Insert Into #Item_Tbl(ID, Item) Values(2, 'bbb')
Insert Into #Item_Tbl(ID, Item) Values(3, 'ccc')
Create Table #tbl_Debt(PID Integer, Item_ID Integer)
Insert Into #tbl_Debt(PID, Item_ID) Values(1, 1)
Insert Into #tbl_Debt(PID, Item_ID) Values(1, 1)
Insert Into #tbl_Debt(PID, Item_ID) Values(1, 2)
Create Table #tbl_Detail(PID Integer, Item_ID Integer)
Insert Into #tbl_Detail(PID, Item_ID) Values(1, 1)
Insert Into #tbl_Detail(PID, Item_ID) Values(1, 1)
Insert Into #tbl_Detail(PID, Item_ID) Values(1, 2)
Select * From #Item_Tbl
Select * From #tbl_Debt
Select * From #tbl_Detail
Select * from #Item_Tbl I
inner join #tbl_Debt D on I.ID = D.Item_ID
inner join #tbl_Detail DT on I.ID = DT.Item_ID And D.PID = DT.PID
where D.PID = 1
Drop Table #Item_Tbl
Drop TAble #tbl_Debt
Drop Table #tbl_Detail
You have two rows in Tbl_Debt with both 1 and 1 in both fields.
Each of these rows will join to the "same two rows" with both 1 and 1 in both fields on the Tbl_Detail table.
It is this this "weak" join causing 4 rows to appear.
sidebar: that is bad data - rows with completely duplicated values should NEVER appear in a database. You would have no way to select just one or delete just one. If this is just sample data, then get better sample data, because you are killing your self with these values.
I've seen novice SQL programmers then put SELECT DISTINCT on the query to fix this! Argh, help me please!
Last edited by szlamany; Feb 9th, 2021 at 06:35 AM.
-
Feb 9th, 2021, 10:55 AM
#12
Re: Need help with this query
Can only agree with szlamany...
It's bad table-design which leads to all this confusion.
Item_Tbl seens to be Ok, in that it seems to have a real PK (Primary Key) with its ID-Field.
(not in the short example you've posted, but I assume in your real DB, Item_Tbl.ID was defined as "Integer Primary Key").
But what about the other two Tables (Tbl_Debt and Tbl_Detail)?
Could you please answer all of the following questions, because otherwise we cannot help:
1) What is the PK of Tbl_Debt? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
2) What is the PK of Tbl_Detail? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
3) What does the Field PID stand for? (it's obviously an abbreviation, but what is it concretely)
4) If PID points to "records of a different Table", what is that Tables Name?
5) it would be nice, when you could post your real Schema-Definitions for:
--- Item_Tbl
--- Tbl_Debt
--- Tbl_Detail
--- and the Table the PID-Field obviously points to
As for the last request #5) ... you can easily do that in "Break-Mode" in the Immediate-Window via:
?Cnn.Databases("main").Tables("TheTableName").SQLForCreate
Olaf
-
Feb 9th, 2021, 10:58 AM
#13
Re: Need help with this query
This is clearly a database question, not a programming question. Normally, I would just move it, but in the original post, you asked that the thread not be moved. Why is that?
My usual boring signature: Nothing
-
Feb 9th, 2021, 01:22 PM
#14
Thread Starter
Fanatic Member
Re: Need help with this query
Item_Tbl seens to be Ok, in that it seems to have a real PK (Primary Key) with its ID-Field.
(not in the short example you've posted, but I assume in your real DB, Item_Tbl.ID was defined as "Integer Primary Key").
That's Right sir
This is the code for creating this table.
Code:
Cnn.Execute "Create TABLE IF NOT EXISTS Item_Tbl(ID Integer PRIMARY KEY, Item Text)"
What is the PK of Tbl_Debt? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
Code:
FOREIGN KEY(`PID`) REFERENCES `Main_Tbl`(`ID`),
FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`),
FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`)
What is the PK of Tbl_Detail? (if it's not a single Field, then post the combination of Fields which make up the combined PK)
Code:
FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`),
FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`)
If PID points to "records of a different Table", what is that Tables Name?
Yes PID points to "records of the Main_Tbl"
As for the last request #5) ... you can easily do that in "Break-Mode" in the Immediate-Window via:
?Cnn.Databases("main").Tables("TheTableName").SQLForCreate
I'm getting error 91: Object variable not set
But By curiosity could you please test this scenario and tell me what RecordCount you have?
Code:
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Debt(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Cnn.Execute "Insert Into Tbl_Detail(PID, Item_ID) Values(1, 1)"
Do you have 12 as I have?
Thank you
-
Feb 9th, 2021, 01:34 PM
#15
Thread Starter
Fanatic Member
Re: Need help with this query
put SELECT DISTINCT on the query to fix this
I can't use DISTINCT as "Item_ID" need sometimes to be duplicated
-
Feb 9th, 2021, 01:38 PM
#16
Thread Starter
Fanatic Member
Re: Need help with this query
Originally Posted by Shaggy Hiker
This is clearly a database question, not a programming question. Normally, I would just move it, but in the original post, you asked that the thread not be moved. Why is that?
In fact I have never posted elsewhere
And I think people here are very helpful
-
Feb 9th, 2021, 02:54 PM
#17
Re: Need help with this query
Originally Posted by newbie2
In fact I have never posted elsewhere
And I think people here are very helpful
GREAT! Now you can learn TWO new things...or even more.
Now you have the thread where it belongs, you'll find some of the same people, along with a bunch of other folks who are very good at queries, and you'll have spread your wings a bit wider. As a bonus, there's still a link to the thread in the old location.
My usual boring signature: Nothing
-
Feb 9th, 2021, 03:15 PM
#18
Thread Starter
Fanatic Member
Re: Need help with this query
As a bonus, there's still a link to the thread in the old location.
Thank you sir for your kindness
-
Feb 9th, 2021, 03:16 PM
#19
Re: Need help with this query
Originally Posted by newbie2
Code:
FOREIGN KEY(`PID`) REFERENCES `Main_Tbl`(`ID`),
FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`),
FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`)
Code:
FOREIGN KEY(`brand_ID`) REFERENCES `Brand_tbl`(`ID`),
FOREIGN KEY(`Item_ID`) REFERENCES `Item_Tbl`(`ID`)
Defining Foreign Key-Fields in SQLite does not auto-generate any indexes,
nor is a combination of Foreign-Key-Fields auto-generating a Primary Key on the table in question.
If you have intermediate tables like that, which consist primarily of Foreign-Key-Fields,
you should manually add a combined PK (out of the ID-Fields which make a record unique) in your Table-Creation.
Could you describe more clearly, what the purpose of the 2 tables (Tbl_Debt and Tbl_Details) is,
and where they differ? (what is the Field DEPT in Tbl_Debt used for?)
For my taste, you hold too many ForeignKeys "per intermediate table".
(I usually restrict myself to only two - the Intermediate-Table then connecting only two "real tables").
I'm absolutely sure that, when you break these two tables up into probably only 3 -
with nice, descriptive names like e.g.:
- tbl_Brand_Item (connecting table Brand with table Item)
- tbl_Main_Item (connecting only table Main with table Item)
- tbl_Main_Brand (connecting only table Main with table Brand)
Also, what is the meaning of the table "Main" (is it Products, or Persons)?
Originally Posted by newbie2
I'm getting error 91: Object variable not set
That's explainable, if you were not properly (as I wrote) in "break mode" ...
(meaning, you have to run your App so that the Cnn-Object got established -
then break - before asking the Cnn-Object to deliver some info in the Immediate-Window).
Please post your Table-Schema-Defs ...
There's a whole lot of recommendations we can only give, when you do so...
If you don't want to loose yourself in more "wondrous behaviour of the DB" in the future,
now is the time to overhaul all your Table-Defs in a consistent manner:
Entity-Tables (tables which store "real data"):
- choose proper names for them (not something like "Main" - and also avoid any underscores in an Entity-Table-Name)
- consistently named ID-Fields in your Entity-Tables
Connection-Tables (tables which typically consist of only two ForeignKey-Fields):
- the table-naming here is simple: if you connect Product with Brand - the name should be: Product_Brand (only in these tables, underscores are allowed)
- the two Fields in such a Table should follow the Entity-TableName as well: (Product_ID, Brand_ID)
- define a unique (combined) PK on these Connection-Tables in addition
If you follow these rules in your new Schema-Def, connections across more than 2 Entity-Tables will behave properly.
HTH
Olaf
Last edited by Schmidt; Feb 9th, 2021 at 03:21 PM.
-
Feb 9th, 2021, 04:18 PM
#20
Re: Need help with this query
Originally Posted by newbie2
I can't use DISTINCT as "Item_ID" need sometimes to be duplicated
You totally missed my point. Using SELECT DISTINCT is evil, bad - wrong - in nearly 99% of the places it is used!
And if that is all you got out of the effort I put into taking your data and SQL and testing it and telling you why you got 4 rows, I'm speechless.
-
Feb 9th, 2021, 04:55 PM
#21
Thread Starter
Fanatic Member
Re: Need help with this query
what is the meaning of the table "Main" (is it Products, or Persons)?
the Main_Tbl holds persons.(Customers)
Could you describe more clearly, what the purpose of the 2 tables (Tbl_Debt and Tbl_Details) is,
and where they differ? (what is the Field DEPT in Tbl_Debt used for?)
The Tbl_Debt holds the ID of cusomers in Main_Tbl in PID field, the Item_ID, brand_ID and the debt owed.
Tbl_Details holds also the ID of cusomers , Item_ID, brand_ID, cash, description and other fields...
Perhaps I badly structured my database
However I found a workaround
I added an autoincrement field 'autoID' to Tbl_Details then
Code:
StrSql = "Select * from Item_Tbl I " & _
" inner join Tbl_Debt D on I.ID = D.Item_ID " & _
" inner join Tbl_Detail DT on I.ID = DT.Item_ID And D.PID = DT.PID" & _
" where D.PID = 1 Group by autoID"
I get exactly what I expect.
Thank you Olaf very much
You are very helpful.
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
|