October 14, 2007 at 9:45 pm
Hi All,
I am new to SQL, but based on the excellent response I got from a previous question I am learning quickly.
I need to fill a drop-down list in an application with rows from a table that do not occur in another table.
Please let me explain.
I have the following tables:
GarmentType (eg: Jacket)
GarmentStyle (eg:Classic)
GarmentColor (eg: Black)
GarmentSize (eg: 40R)
GarmentNumber (eg: B) (Equals 2nd jacket of this type/style/color/size combination)
GarmentBookings (StartDate, EndDate, OrderID, CustomerID)
Orders (OrderID, CustomerID,...)
OrderItems (OrderID, TypeID, StyleID, ColorID, SizeID, NumberID, ...)
Each time the user selects a row in a table, the subsequent table is filter accordingly.
However, when the selected garment is booked for a particular period, it needs to be removed from the list.
So a Jacket->Classic->Black->40R booked from 1st November to 3rd of November, should not appear in the available drop-down list for any new bookings for that same period, UNLESS there are unallocated ones available. (Garment Numbers A and C in the above example).
The app is coded in C#.
Any sample SQL code snippets would be appreciated. Any suggestions to table schema improvements will also be welcome.
cheers,
Mark Chimes
October 15, 2007 at 12:38 am
Without a table schema and sample data I can't give you exact code. You're probably looking for something like this.
This will get you all the records in A that do not have matching rows in B
SELECT
FROM TableA LEFT OUTER JOIN TableB ON TableA.JoinField = TableB.JoinField
WHERE TableB.JoinField IS NULL
The principle is that you do a left join to get all rows that have a match and all rows that dn't have a match, then eliminate the ones that do have a match with the IS NULL constraint.
If the table relationship is multi column, then your join will be multiple columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2007 at 2:05 am
Hi Gail,
Thanks for the reply. It has been a big help.
Here is the SP I have created based on your example:
SELECT StockCount.id, StockCount.SizeID, StockCount.Count, StockCount.InDate, StockCount.OutDate, StockCount.OutReason, FunctionStock.ID AS Expr1,
FunctionStock.StartDate, FunctionStock.EndDate, FunctionStock.OrderID, FunctionStock.TypeID, FunctionStock.StyleID, FunctionStock.ColourID,
FunctionStock.SizeID AS Expr2, FunctionStock.CountID
FROM StockCount LEFT OUTER JOIN
FunctionStock ON StockCount.id = FunctionStock.CountID
WHERE (FunctionStock.CountID IS NULL)
This is getting close to what I need.:)
How do I modify the above SP only take into account those rows in FunctionStock that are greater or equal to StartDate and less than or equal to EndDate AND where the StockCount.id equals the FunctionStock.CountID?
BTW, Do you know of any decent beginners books on SQL Stored Procedures?
cheers,
Mark Chimes
October 15, 2007 at 2:46 am
Mark Chimes (10/15/2007)
How do I modify the above SP only take into account those rows in FunctionStock that are greater or equal to StartDate and less than or equal to EndDate AND where the StockCount.id equals the FunctionStock.CountID?
Sorry, I don't understand. Rows in functionstock where what is between startdate and endDate?
Could you please post the schame of the two applicable tables, some sample data for both and your desired output? I'm working fairly blind.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2007 at 5:04 pm
Hi Gail,
File structures are:
StockType
ID, int (PK)
Type, nvarchar(50)
Charge, money
StockStyle
ID, int (PK)
TypeID, int
Style, nvarchar(50)
StockColor
ID, int (PK)
StyleID, int
Colour, nvarchar(50)
StockSize
ID, int (PK)
ColourID, int
Size, nvarchar(50)
StockCount
id, int (PK)
SizeID, int
Count, nchar(2)
InDate, datetime
OutDate, datetime
OutReason, nvarchar(100)
FunctionStock
ID, int (PK)
StartDate, datetime
EndDate, datetime
OrderID, int
CountID, int
Orders
OrderID, int (PK)
OrderNum, int
OrderDate, datetime
FunctionDate, datetime
NumInParty, smallint
CustomerID, int
Picked, bit
Returned, bit
Paid, bit
Completed, bit
FunctionType, int
Desposit, money
Discount, money
Total, money
OrderItems
OrderItemID, int (PK)
OrderID, int
CustomerID, int
CustomerName, nvarchar(50)
CustomerNumber, int
GarmentType, nvarchar(255)
GarmentStyle, nvarchar(50)
Size, nvarchar(10)
Colour, nvarchar(50)
Length, decimal(18, 2)
GarmentID, int
Amount, money
OrderType, bit
Paid, bit
Returned, bit
Garments Types have multiple Styles, which have multiple colors, which have multiple sizes, and there are a varying number of items within each size.
eg: There may be 7 Jacket->Classic->Black->40R in stock (A,B,C,D,E,F,G) but only 3 jacket->Classic->Black->42L in stock (A,B,C).
Each selection in a Stockxxx file retrieves a matching set in the next Stockxxx file. (This means each StockCount row is unique and actually identifies the Garment type, style, Colour, and Size).
When a Customer comes in to select garments for a function (Wedding, Ball, etc) the assistant needs to know if the garment chosen is available for the duration of the required hire time.
A row is inserted into the Orders table, and appropriate rows into the OrderItems table.
A row is inserted into the FunctionStock table as well.
The assistant selects the Garment Type, Style and colour.
eg: Jacket->Classic->Black->40R
Then, I need to be able to provide a list of available Classic Black 40R Jackets for the required period, hence my need to lookup tables for rows that do not exist.
So, if A,D,E,F,G have already been rented, then only B and C will appear in the list.
I can send you a whole lot of code from VS2005 (C#), but I don't know how to upload such a file.
Thanks for your help, and I hope this is not too long a post.
cheers,
Mark Chimes
October 16, 2007 at 12:09 am
Ok, I'm still a bit vague, but will try.
You want all stockcount entries that don't have matching entries in FunctionStock for a particular date?
What's the join between FunctionStock and StockCount? Which dates are important? How do the Functionstock's start and end dates relate to StockCount's in and out dates?
Could you possibly post a few rows of sample data for those 2 tables, and what you want as output? Even just makeup data's fine. Comma delimited lists should be fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2007 at 4:27 pm
Hi Gail,
I am appreciating your persistence with this. Thank you.
> You want all stockcount entries that don't have matching entries in FunctionStock for a particular date?
Almost. I need all stockcount entries for the currently selected garment that don't have matching entries in FunctionStock for a particular date range.
>What's the join between FunctionStock and StockCount?
StockCount.id and FunctionStock.CountID
?Which dates are important? How do the Functionstock's start and end dates relate to StockCount's in and out dates?
StockCount's In and Out dates are used for stock control, not hire management. So, if StockCount.OutDate is not NULL, then it is excluded from all hire activities (including the one we are currently discussing).
Could you possibly post a few rows of sample data for those 2 tables...
StockCount
id,SizeID,Count,InDate,OutDate,OutReason
1,20,A,01/01/2007,NULL,NULL (Jacket->Classic->Black->40R->A)
2,20,B,01/01/2007,09/03/2007,Not returned by hirer (Jacket->Classic->Black->40R->B)
3,20,C,01/01/2007,NULL,NULL (Jacket->Classic->Black->40R->C)
4,21,A,01/01/2007,NULL,NULL (Jacket->Classic->Black->42R->A)
5,21,B,01/01/2007,NULL,NULL (Jacket->Classic->Black->42R->B)
6,22,A,01/01/2007,NULL,NULL (Jacket->Classic->Navy->40R->A)
FunctionStock
ID,StartDate,EndDate,OrderID,CountID
1,10/25/2007,11/30/2007,7359,1
2,11/01/2007,11/03/2007,7360,1
3,11/01/2007,11/03/2007,7360,6
4,11/10/2007,11/13/2007,7361,6
... and what you want as output?
For a hire period of 11/01/2007 to 11/02/2007...
If the garment selected is a Jacket->Classic->Black->40R, then only row #3 (Jacket->Classic->Black->40R->C) should be returned by the query and show in the listbox. (A is already booked, and B is not available at all).
If the garment selected is a Jacket->Classic->Black->42R then both rows #4 & #5 should be returned as neither are already booked for the required period.
For the period 11/07/2007 to 11/09/2007...
If the garment selected is a Jacket->Classic->Black->40R then both rows #1 & #3 should be returned as both "active" jackets are available.
One further carification.
The date range is derived from user input and is stored in the Order table, so while it does not appear in any of the above table rows, it is available to the query at runtime.
There are a number of modifications and options to the above scenario, but if I can understand the query for this one, then I am certain I can resolve the others as they are only permutations of this main query.
Looking forward to your suggestions 🙂
Cheers,
Mark Chimes
October 16, 2007 at 6:02 pm
Hi Gail,
I have been trying different queries in an attempt to learn more about SQL.
The following query does not work, but it does represent what I am actually trying to do.
SELECT StockCount.id, StockCount.SizeID, StockCount.Count, StockCount.InDate, StockCount.OutDate, StockCount.OutReason, FunctionStock.ID AS Expr1,
FunctionStock.StartDate, FunctionStock.EndDate, FunctionStock.OrderID, FunctionStock.TypeID, FunctionStock.StyleID, FunctionStock.ColourID,
FunctionStock.SizeID AS Expr2, FunctionStock.CountID
FROM StockCount LEFT OUTER JOIN
FunctionStock ON StockCount.id = FunctionStock.CountID
WHERE (FunctionStock.CountID IS NULL) OR ((StockCount.SizeID = @SizeID) WHERE ((FunctionStock.StartDate @End)))
I do not understand why this does not work, but I thought it may give you another idea of what I am trying to achieve.
cheers,
Mark Chimes
October 17, 2007 at 12:15 am
Mark Chimes (10/16/2007)
One further carification.The date range is derived from user input and is stored in the Order table, so while it does not appear in any of the above table rows, it is available to the query at runtime.
Ha! That's the piece of info I was missing. I'll see if I can work something up for you a bit later, once I get the morning tasks out of the way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2007 at 1:44 am
Apoogies for the delay.
Give this a try
SELECT * FROM StockCount LEFT OUTER JOIN
( SELECT CountID FROM FunctionStock
WHERE @FunctionStartDate BETWEEN StartDate AND EndDate OR @FunctionEndDate BETWEEN StartDate AND EndDate -- Some form of overlap
) Booked
ON StockCount.id = booked.CountID
WHERE Booked.CountID IS NULL -- not in the booked list
AND OutDate IS NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2007 at 5:19 am
Hi Gail,
No apologies necessary. I appreciate the help.
The following query works.
SELECT StockCount.id, StockCount.SizeID, StockCount.Count, StockCount.OutDate, FunctionStock.ID AS Expr1, FunctionStock.StartDate, FunctionStock.EndDate,
FunctionStock.OrderID, FunctionStock.SizeID AS Expr2, FunctionStock.CountID
FROM StockCount LEFT OUTER JOIN
FunctionStock ON StockCount.id = FunctionStock.CountID
WHERE (StockCount.OutDate IS NULL) AND (StockCount.SizeID = @SizeID) AND (@FunctionDate NOT BETWEEN FunctionStock.StartDate AND FunctionStock.EndDate)
You will notice that I have removed the reference to (FunctionStock.CountID IS NULL) as I am pretty sure this is redundant and serves no purpose for me.
Anyway, it's working nicely. Thanks very much for your help.
BTW,do you know of a good SQL book for beginners? Particularly one that covers stored procedures and the syntax of commands, LEFT/RIGHT INNER/OUTER, etc.
cheers,
Mark Chimes
October 21, 2007 at 12:37 pm
Yup. That's pretty much the same query expressed a different way. The IS NULL check needs to be left out, or you'll get no records as you've reversed the logic of the date comparison.
Also, you can change the left outer join to an inner join as you only want matching rows this time.
I don't have any beginner SQL book recommendations. I haven't read a beginners book in some years. I would suggest you start a thread in the 2005 TSQL forum and ask for recommendations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply