SQL Join problems

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

SQL Join problems

Post by SBSP »

Hi I'm building a report that shows the following

Item , Item Desc , Qty Sold , QTY On hand, QTY Backorder.

The report run's year to May 2009 based on the sale date.

Ive create a seperate Sales report , Stock Onhand & Backorder report.
I then imported the 3 reports into an Access database then use crystal reports to
report from it.

All the reports are now a table.

Sales Table
Item, Qty Sold

Qty On hand
Item, QtyOnhand

Backorders Table
Item, QtyBackorder

And im linking the 3 together on the Item (Which is unique)

Sales Item is linked to Qty Onhand and Backorders is also linked to the Sales tables Item Field.

The Problem is.

Some items in the Sales table is not pressent cus its based on date, which is pressent on Bac orders and Same with QtyOnhand some items is not pressent on sales where there is stock available , hope it makes sense.

So when these are linked the Join matches only what it can find, render ing my report useless.

I can set the type of joining in Crystal Reports but ive tried all the combinations and it still doesnt work.
Can any one help me ?
Last edited by SBSP on 01 Jun 2009, 13:02, edited 1 time in total.
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Joint problems

Post by Ron2K »

I think an OUTER JOIN of one kind or another may be your friend here. :wink:
Kia kaha, Kia māia, Kia manawanui.
c0d3r
Registered User
Posts: 8752
Joined: 18 Jan 2009, 10:24
Location: Istanbul, Turkey

Re: Joint problems

Post by c0d3r »

I seriously thought that this is going to be a thread about sore knees, elbows and hips when I read "Joint Problems".

*sigh*

Yes, an inner join only takes records where it finds a match in both tables. Interesting read.
а плечьми буйство струйка. Дверь Твоем юдоли. Виновником неразлучен страшиться невинность милосердье сладчайший. Век под вне Оно Нет сия тук. Все вас совоздыхая устремится ком мню возблеснув разверзают лик мой сопрягаешь. Неправды но Ту светлеет Неправых Ни ум смотреть.
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Joint problems

Post by Tribble »

Not used Crystal but can help you in Access if you want. Ronny is right - you need to set your outer join to be everything in Sales table visible. You never mentioned any dates in your fields though.

Which table do you want to see all the records of?
Image
c0d3r
Registered User
Posts: 8752
Joined: 18 Jan 2009, 10:24
Location: Istanbul, Turkey

Re: Joint problems

Post by c0d3r »

Rather keep your calculations and stuff to a minimum in Crystal. Do Group By's and Order By's and totalling etc in Crystal. Crystal gets clumsy with "raw" data and what ever you want to do, it's so much more easier and versatile in SQL. Except off course displaying the actual report ;)
а плечьми буйство струйка. Дверь Твоем юдоли. Виновником неразлучен страшиться невинность милосердье сладчайший. Век под вне Оно Нет сия тук. Все вас совоздыхая устремится ком мню возблеснув разверзают лик мой сопрягаешь. Неправды но Ту светлеет Неправых Ни ум смотреть.
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Join problems

Post by SBSP »

I changed the topic to SQL Join Problems :mrgreen:

And i made quickly made this macro.

Dont laugh please.

I have 4 sheets SALES, BACKORDERS and STOCKONHAND & COMBINED.

The Stock on hand is all items including 0 and old items which comes to 16 000 Items :roll:
So my macro runs in loops with in loops which finds items and puts the value into the correct field. in 3 stages. :mrgreen:

It works but takes long, atleast i can give a report for now.
I will try the outer join thing for a permanent solution. I some how think that it wont work cut i think i have tried it.
But the stock on hand inventory list wasnt showing 0 on hand items though.

Code: Select all

Dim QTYOnhand As Long
Dim QtySold As Long
Dim QtyBack As Long
Dim ItemNoSold As String
Dim ItemNoBack As String
Dim ItemNoOnhand As String

Dim LineCountOnhand As Double
Dim LineCountBack As Long
Dim LineCountSold As Long

Dim ItemDesc As String
Dim CombLinecount As Double



Sub Main()
CombLinecount = 0

For LineCountOnhand = 1 To 16688
CombLinecount = CombLinecount + 1
ItemNoOnhand = Sheet3.Cells(LineCountOnhand, 1)
ItemDesc = Sheet3.Cells(LineCountOnhand, 2)
QTYOnhand = Sheet3.Cells(LineCountOnhand, 3)
Sheet4.Cells(LineCountOnhand + 1, 1) = ItemNoOnhand
Sheet4.Cells(LineCountOnhand + 1, 2) = ItemDesc
Sheet4.Cells(LineCountOnhand + 1, 5) = QTYOnhand
Next LineCountOnhand

MsgBox "Complete with Onhand"

'Calculate Backorders
For LineCountBack = 1 To 810
ItemNoBack = Sheet2.Cells(LineCountBack, 1)
QtyBack = Sheet2.Cells(LineCountBack, 2)


    For LineCountOnhand = 2 To 16688
    ItemNoOnhand = Sheet4.Cells(LineCountOnhand, 1)
        If ItemNoBack = ItemNoOnhand Then
            Sheet4.Cells(LineCountOnhand, 4) = QtyBack
            Exit For
        End If
    
    Next LineCountOnhand

Next LineCountBack

MsgBox "Complete with backorders"

'Calculate Sales
For LineCountSold = 1 To 1607
ItemNoSold = Sheet1.Cells(LineCountSold, 1)
QtySold = Sheet1.Cells(LineCountSold, 3)


    For LineCountOnhand = 2 To 16688
    ItemNoOnhand = Sheet4.Cells(LineCountOnhand, 1)
        If ItemNoOnhand = ItemNoSold Then
            Sheet4.Cells(LineCountOnhand, 3) = QtySold
            Exit For
        End If
    
    Next LineCountOnhand

Next LineCountSold



MsgBox "Complete with Sales"

End Sub
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Join problems

Post by RuadRauFlessa »

SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Join problems

Post by SBSP »

RuadRauFlessa wrote:SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.
I'm thinking of creating 3 views then a final view combined from that.
Or like you say a storedproc.

But i'm not good with SQL stored procs.
The other thing is if i do this , This just add an extra shlep to the system.
Cus i know this report will become popular, (Very good for forecasting stock)

So when there is upgrades all this has to be upgraded or accounted for.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Join problems

Post by RuadRauFlessa »

SBSP wrote:
RuadRauFlessa wrote:SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.
I'm thinking of creating 3 views then a final view combined from that.
Or like you say a storedproc.

But i'm not good with SQL stored procs.
The other thing is if i do this , This just add an extra shlep to the system.
Cus i know this report will become popular, (Very good for forecasting stock)

So when there is upgrades all this has to be upgraded or accounted for.
All the more reason to use a single stored proc for the job. It will cut down on execution time and soforth. Also as a rule try and use nolock if you only draw data for reports.

SELECT * FROM [dbo].[TABLE1] WITH (NOLOCK)

The query will read over records with read/write locks. It will also not mark any records as read locked. The chance of getting a deadlock is thus null and void. You should, on a fairly busy system, see a notable performance difference.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Join problems

Post by SBSP »

Will a query lock a table if the logon user has read only rights ?
Last edited by SBSP on 01 Jun 2009, 15:26, edited 2 times in total.
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: SQL Join problems

Post by Ron2K »

^^ For me to explain when each type of locking occurs is a separate essay in itself. :wink:
Kia kaha, Kia māia, Kia manawanui.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Join problems

Post by RuadRauFlessa »

SBSP wrote:Will a querry lock a table is the logon user has read only rights ?
Yes. Any query you pull has to lock the record it is busy with otherwise you might get concurrency issues. In the case of a nolock the record will read the last committed version of the record if the record is currently write locked. If you do a select without it then it will turn out that the write query trying to update the table waits until the select is finished and then only updates the record. It is so that you only read records which is correct. A nolock will not give you inaccurate results but could rather not contain the latest result as one of the selected records may have been written to while it executed. There is no risk in using it. There is another option which is the readover but that will cause the record to be omitted from the resultset as when it sees that the record is locked it will simply skip it.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Join problems

Post by SBSP »

RuadRauFlessa wrote:
SBSP wrote:Will a querry lock a table is the logon user has read only rights ?
Yes. Any query you pull has to lock the record it is busy with otherwise you might get concurrency issues. In the case of a nolock the record will read the last committed version of the record if the record is currently write locked. If you do a select without it then it will turn out that the write query trying to update the table waits until the select is finished and then only updates the record. It is so that you only read records which is correct. A nolock will not give you inaccurate results but could rather not contain the latest result as one of the selected records may have been written to while it executed. There is no risk in using it. There is another option which is the readover but that will cause the record to be omitted from the resultset as when it sees that the record is locked it will simply skip it.
Thanks ron2k and RuadRauFlessa.

Thought as much. :mrgreen:

On a different note.

I can hardly read my own posts :oops:

I sometimes only type can when i meant "can't" and often type "is" when i meant if.
And to mix up those to words makes things confusing.
Post Reply