It still returns two rows if it exists in both tables(I added the bold Kredi to your select statement)
Dim sqlUnion As String = String.Format("SELECT DISTINCT Kredi_ID,Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=?
UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?) ORDER BY Kredi_ID ", Server.MapPath("~/App_Data/Visieren.mdb"))
What I meant is that if the same Kredi_ID exists in both tables, I want it to show only once. Could it be that it shows it to me twice because even though Kredi_ID is the same, Kredi is not?
If both columns are the same then it's not shown twice. But if kredi_ID is same and Kredi different then it's shown twice. How do I go about that?
OK here is what I found searching on the internet ( now that I know where the problem lies) , seems to be working. Is this the correct way of doing it?
Dim sqlUnion As String = String.Format("SELECT Kredi_ID, Max(Kredi) as Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION
SELECT Kredi_ID, Kredi FROM Projek WHERE Lager_ID=?) GROUP BY Kredi_ID ORDER BY 2", HttpContext.Current.Server.MapPath("~/App_Data/Visieren.mdb"))
OK here is what I found searching on the internet ( now that I know where the problem lies) , seems to be working. Is this the correct way of doing it?
Dim sqlUnion As String = String.Format("SELECT Kredi_ID, Max(Kredi) as Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION
SELECT Kredi_ID, Kredi FROM Projek WHERE Lager_ID=?) GROUP BY Kredi_ID ORDER BY 2", HttpContext.Current.Server.MapPath("~/App_Data/Visieren.mdb"))
Yep, that looks good.... This will only show each Kredi_ID only once, and if there are multiple values of Kredi, it will show the maximum value....
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 22, 2012 08:50 AM|LINK
It still returns two rows if it exists in both tables(I added the bold Kredi to your select statement)
Dim sqlUnion As String = String.Format("SELECT DISTINCT Kredi_ID,Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?) ORDER BY Kredi_ID ", Server.MapPath("~/App_Data/Visieren.mdb"))
hans_v
All-Star
35986 Points
6550 Posts
Re: select two databases
Feb 22, 2012 10:01 AM|LINK
You mean your statement returns 2 records with the same kredi_ID AND Kredi ????
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 22, 2012 10:15 AM|LINK
If both columns are the same then it's not shown twice. But if kredi_ID is same and Kredi different then it's shown twice. How do I go about that?
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 22, 2012 12:03 PM|LINK
OK here is what I found searching on the internet ( now that I know where the problem lies) , seems to be working. Is this the correct way of doing it?
Dim sqlUnion As String = String.Format("SELECT Kredi_ID, Max(Kredi) as Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID, Kredi FROM Projek WHERE Lager_ID=?) GROUP BY Kredi_ID ORDER BY 2", HttpContext.Current.Server.MapPath("~/App_Data/Visieren.mdb"))
hans_v
All-Star
35986 Points
6550 Posts
Re: select two databases
Feb 22, 2012 12:09 PM|LINK
That is exactly what DISTINCT is all about. It does show only unique rows.
hans_v
All-Star
35986 Points
6550 Posts
Re: select two databases
Feb 22, 2012 12:10 PM|LINK
Yep, that looks good.... This will only show each Kredi_ID only once, and if there are multiple values of Kredi, it will show the maximum value....
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 22, 2012 12:17 PM|LINK
Thanks for sticking with me until the end