Database Discussion Boards

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 Suspicious | :suss:

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.

Wrong is evil and must be defeated. - Jeff Ello

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 Smile | :)

. 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:

SQL

WHERE   floor_id = param_floor
    OR  (floor_id IS NULL AND param_floor IS NULL)

Wrong is evil and must be defeated. - Jeff Ello

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.

Wrong is evil and must be defeated. - Jeff Ello

so what if I want to say:

SQL

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.

SQL

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:

SQL

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

and this is my .NET application code:

C#

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", Convert.ToInt32(gridLocation.GetRowCellValue(gridLocation.FocusedRowHandle, "employee"))).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. Smile | :)

Wrong is evil and must be defeated. - Jeff Ello

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.

SQL

decode(floor_id,null,1,floor_id)=decode(param_floor,null,1,param_floor)

thanks in advance

regards,
Sundeep