Left Join and double results where the join is complete
-
I understand that this is what I asked for, but I'm trying to figure out how to just get 1 record of each item, instead of the 2 when the Join is valid.
DECLARE @CategoryID INT;
SET @CategoryID = 23;SELECT *
FROM ProductInfo p
LEFT JOIN ProductInfo_Inventory pi ON p.ProductID = pi.ProductID
WHERE p.Category = @CategoryID -
I understand that this is what I asked for, but I'm trying to figure out how to just get 1 record of each item, instead of the 2 when the Join is valid.
DECLARE @CategoryID INT;
SET @CategoryID = 23;SELECT *
FROM ProductInfo p
LEFT JOIN ProductInfo_Inventory pi ON p.ProductID = pi.ProductID
WHERE p.Category = @CategoryIDI think I get it now. If I were to request a single record, then the join would be fine. But since I requesting all records that match the category ID, you get what you ask for. So I decided to just make a separate call back to the database for the inventory data as I loop through the presentation aspect of the program.
-
I understand that this is what I asked for, but I'm trying to figure out how to just get 1 record of each item, instead of the 2 when the Join is valid.
DECLARE @CategoryID INT;
SET @CategoryID = 23;SELECT *
FROM ProductInfo p
LEFT JOIN ProductInfo_Inventory pi ON p.ProductID = pi.ProductID
WHERE p.Category = @CategoryID -
Don't select *, just select the columns you need, and you can probably use DISTINCT to filter the list down to a unique set.
I ended up wrapping the select in a select statement, and adding the union to get the history and the current records. I think I went through like 5 or 6 versions of this before I got it to work. The numbers they produce are correct, so perhaps I got it right, and it's fast.
"SELECT " & _
" SUM(FSHIPQTY * FCOST) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", SUM(FAMOUNT) " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST) " & _
" FROM " & _
"( " & _
" SELECT " & _
" h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" , h.FAMOUNT " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" AND " & _
" h.FITEMNO = @FITEMNO " & _
" UNION ALL " & _
" SELECT " & _
" v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" , v.FAMOUNT " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" v.FITEMNO = @FITEMNO " & _
") " -
I ended up wrapping the select in a select statement, and adding the union to get the history and the current records. I think I went through like 5 or 6 versions of this before I got it to work. The numbers they produce are correct, so perhaps I got it right, and it's fast.
"SELECT " & _
" SUM(FSHIPQTY * FCOST) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", SUM(FAMOUNT) " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST) " & _
" FROM " & _
"( " & _
" SELECT " & _
" h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" , h.FAMOUNT " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" AND " & _
" h.FITEMNO = @FITEMNO " & _
" UNION ALL " & _
" SELECT " & _
" v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" , v.FAMOUNT " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" v.FITEMNO = @FITEMNO " & _
") "What you've done there is open to SQL Injection attackes, which are a serious security issue. For a really long but excellent article about dynamic SQL and SQL injection, see http://www.sommarskog.se/dynamic_sql.html[^]. A nested query like this will work for a small dataset, but the inner queries will be run for every row in the outer query. It won't take long for this to be a very slow query overall.
-
What you've done there is open to SQL Injection attackes, which are a serious security issue. For a really long but excellent article about dynamic SQL and SQL injection, see http://www.sommarskog.se/dynamic_sql.html[^]. A nested query like this will work for a small dataset, but the inner queries will be run for every row in the outer query. It won't take long for this to be a very slow query overall.
It's an old DOS accounting program "Account Mate" using DBF files; Fox Pro; in which a Windows 7 and 8 application that I wrote for the customer provides extra features used all day long for electronic invoicing, electronic order confirmation, electronic past due statements via emails and PDF attachments. It's not exposed to the internet.