|
Dear All
we need some help. We have Three Tables in sqlserver2012
Master Table
OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz
Child1 Table
OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red
DB Script
CREATE TABLE [dbo].[Child1](
[Child1ID] [int] NOT NULL,
[OrderID] [int] NULL,
[ControlName] [nvarchar](max) NULL,
CONSTRAINT [PK_Child1] PRIMARY KEY CLUSTERED
(
[Child1ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Child2] Script Date: 11/11/2014 6:06:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Child2](
[Child2ID] [int] NOT NULL,
[PackageID] [int] NULL,
[Product] [nvarchar](max) NULL,
[Color1] [nchar](10) NULL,
[Color2] [nchar](10) NULL,
[Color3] [nchar](10) NULL,
CONSTRAINT [PK_Child2] PRIMARY KEY CLUSTERED
(
[Child2ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[MasterTable] Script Date: 11/11/2014 6:06:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MasterTable](
[OrderID] [int] NOT NULL,
[PackageID] [int] NULL,
[CustomerName] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_MasterTable] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (1, 1, N'Row1Column1')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (2, 1, N'Row3Column1')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (3, 1, N'Row4Column3')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (4, 2, N'Row1Column2')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (5, 3, N'Row3Column1')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (1, 1, N'Pant', N'Red ', N'Green ', N'Blue ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (2, 1, N'Shirt', N'Blue ', N'Pink ', N'Purple ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (3, 1, N'Gown', N'Blue ', N'Black ', N'Yellow ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (4, 1, N'T Shirt', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (5, 2, N'Tie', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (6, 2, N'Socks', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (7, 2, N'Bow', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[MasterTable] ([OrderID], [PackageID], [CustomerName]) VALUES (1, 1, N'Abc')
GO
INSERT [dbo].[MasterTable] ([OrderID], [PackageID], [CustomerName]) VALUES (2, 2, N'xyz')
GO
USE [master]
GO
ALTER DATABASE [GarmentsTest] SET READ_WRITE
GO
Thanks and Best Regards Umair
|
|
|
|
|
This isn't how this site works, we will help you where you are stuck. But we dont supply solutions to a requirement.
But I would suggest you read up on inner joins[^] and left outer joins[^]
the syntax is similar across the different versions on SQL Server.
after you have read these and get stuck please show us your code and try to explain why its wrong and we will help you further.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Next to that, I'd recommend normalizing the data-structure; non-normal tables often make querying more complicated.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I second Eddys suggestion, you're really in for trouble if you keep that table structure.
|
|
|
|
|
Can you redesign your Database tables? It has very bad relations and out put. Why you have 3 columns for Color1, Color2 and Color3 .With this Column how can you get your result .
|
|
|
|
|
A clustering index is an ordered file with two fields; the first field is of the same type as the clustering field of the data file, and the second field is a disk block pointer. There is one entry in the clustering index for each distinct value of the clustering field, and it contains the value and a pointer to the first block in the data file that has a record with that value for its clustering field.
Could any one explain to me how the clustering index have one entry for each distinct value of the clustering field While these below link to an illustrations image show a pointer to the same value 1 in the same block
http://imagizer.imageshack.us/a/img538/3491/AdYgxd.png[^]
|
|
|
|
|
Read the quote a few more times; it matches the diagram. Try reversing the arrows and see if that helps you understand it.
|
|
|
|
|
I have read it many times but I cannot understand it. One entry for each distinct value while the are 2 pointers each of which pointing to the clustering field 1 in the same block file. I tried to reverse the arrows but the same issue 2 pointers 1, and 2 to the same clustering field in the same clustering file as you can see while other pointers point to a distinct values in each clustering block.
|
|
|
|
|
Hmmm, actually it looks like the diagram is slightly confusing. The two arrows to block 1 should both be pointing to the start of block 1.
|
|
|
|
|
How about this:
The records with Dept_number==1 start in block 0
The records with Dept_number==2 start in block 0
The records with Dept_number==3 start in block 1
The records with Dept_number==4 start in block 2
The records with Dept_number==5 start in block 3
The records with Dept_number==6 start in block 4
The records with Dept_number==8 start in block 5
|
|
|
|
|
This could be intended  . With this a pointer to the first block in the data file that has a record with that value for its clustering field This seems reasonable
|
|
|
|
|
Hi,
I have a table with floor_id field.
The floor_id is null and I am trying to read it
when I read it using where floor_id = NULL I don't get the correct result but when I try where floor_id IS NULL it's working
so how can I do when passing a parameter like where floor_id = param_floor?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
You can do it like this in MSSQL
WHERE ISNULL(Floor_id,'') = ISNULL(param_floor,'')
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
As a general solution you can try:
WHERE floor_id = param_floor
OR (floor_id IS NULL AND param_floor IS NULL)
modified 3-Dec-14 15:06pm.
|
|
|
|
|
hmm but can I ask why?
I was using just floor_id = param_floor in other application and it was working fine.. why it's acting funny now?
Technology News @ www.JassimRahma.com
|
|
|
|
|
You cannot equal null in tristate logics, it's undefined and does not equal anything.
You can only check for null in both the field and the parameter.
|
|
|
|
|
so what if I want to say:
floor_id = param_floor
where the passed param_floor from my application could be a value or null.
How can I do this?
Technology News @ www.JassimRahma.com
|
|
|
|
|
Based on your question and Jassim Rahma wrote: where the passed param_floor from my application could be a value or null.
floor_id = param_floor
is valid.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: is valid.
but it returns nothing when the passed value is null and I do have null in the table!
here is my stored procedure:
SELECT assets$audit.audit_id,
assets$audit.audit_date,
assets$status.status_name,
assets$conditions.condition_name,
assets$audit.quantity
FROM assets$audit
JOIN assets$status ON `assets$status`.status_id = assets$audit.audit_status
LEFT JOIN assets$conditions ON `assets$conditions`.condition_id = assets$audit.audit_condition
WHERE item_id = param_item_id
AND location = param_loaction
AND floor = param_floor
AND room = param_room
AND department = param_department
AND employee = param_employee
ORDER BY audit_date DESC, created_date DESC;
Technology News @ www.JassimRahma.com
|
|
|
|
|
Read and understand this visual explanation of sql joins[^] then you will have an idea of what is happening.
You may not have a null value in the table but you certainly have a left join which will return nulls if there is no corresponding row on the right side of the join.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
the left join dose not return null
Technology News @ www.JassimRahma.com
|
|
|
|
|
sql_connection = new MySqlConnection(xmlClass.get_connectionstring("Assets\\Assets"));
sql_connection.Open();
sql_command = new MySqlCommand("sp_populate_asset_audits", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.CommandTimeout = Convert.ToInt32(string_encryptor.DecryptString(xmlClass.read_xml_value("Assets\\Assets", "CommandTimeOut"), "JassimRahma@731004167"));
sql_command.Parameters.AddWithValue("param_item_id", item_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_loaction", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "location_id"))).MySqlDbType = MySqlDbType.Int32;
if (gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "floor_id") != null) sql_command.Parameters.AddWithValue("param_floor", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "floor_id"))).MySqlDbType = MySqlDbType.Int32; else sql_command.Parameters.AddWithValue("param_floor", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
if (gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "room_id") != DBNull.Value) sql_command.Parameters.AddWithValue("param_room", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "room_id"))).MySqlDbType = MySqlDbType.Int32; else sql_command.Parameters.AddWithValue("param_room", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
if (gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "department_id") != DBNull.Value) sql_command.Parameters.AddWithValue("param_department", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "department_id"))).MySqlDbType = MySqlDbType.Int32; else sql_command.Parameters.AddWithValue("param_department", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_employee", DBNull.Value).MySqlDbType = MySqlDbType.Int32;
Technology News @ www.JassimRahma.com
|
|
|
|
|
It was a general solution i gave you, so it work for that case too. 
|
|
|
|
|
Have a bit of a google as there is no simple way to explain it, however it goes something like this:
Null is not nothing, null is indeterminate, in other words null is an unknown value.
So in trying to compare an unknown value with another unknown value the result is always false.
Null == Null returns false.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hello Jassim,
while working in a procedure then i got the same problem. so based on my experience i would suggest to use decode function. decode function is similar to if else block.
decode(floor_id,null,1,floor_id)=decode(param_floor,null,1,param_floor)
thanks in advance
regards,
Sundeep
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
