Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,
i have 5 item(A,B,C,D,E).Item A is a father of item (B,C).
In my application,when i select item A,it automatic insert item (B,C) to datagrid too.
It means that i selected only item A but it display item (A,B,C) in datagrid.
when i add two more item (D,E).So in datagrid will display 5 item (A,B,C,D,E)
When i use selete statement in sql, it is also display 5 item (A,B,C,D,E).
but i purpose is i want to display only item (A,D,E),For item (B,C) it can't display because they are the child of item A.

Note:
i have 2 table
1.is sale table
2.is for store father item and child item

Here is the table stucture

table1

ColumnNameDataTypeSize
Code int
itemcodevarchar20
itemnamevarchar50
pricenumeric(18,2)
qtyint
amountnumeric(18,2)


table2


ColumnNameDataTypeSize
FatherCodevarchar20
ChildCodevarchar20
docdatedatetime50


How can i use select statement in sql to display only father item and not child item?

Thanks

TONY

[EDIT]

Hi all,
These are my table structure and what i want to display.
TABLE1

Item CodeQuantity
A5
B1
C3
D4
E5
F6
G1
H3
I8
J5

TABLE2

Father CodeChild Code
AB
AC
DE
DF
DG
HI

What i want to display.


Item CodeQuantity
A5
D4
H3
J5

Why item(B,C,E,F,G,I)​ are not display?Because they are the child item of item (A,D,H) and item(J) is not child or father of other item.

Thanks

TONY
[/EDIT]
Posted
Updated 21-May-12 3:28am
v5
Comments
Maciej Los 18-May-12 1:01am    
We need to know the structure of your tables.
soeun tony 18-May-12 21:17pm    
Hi all,
Here are my table structure.
table1(Code [int],itemcode[varchar(20)],itemname[varchar(50)],price [numeric(18,2)],qty[int],amount[numeric(18,2)])
table2(FatherCode[varchar(20)],ChildCode[varchar(20)],docdate[datetime])
Thanks
bhagirathimfs 18-May-12 2:41am    
Can you please give both the table structure( All columns name with their data type).
soeun tony 18-May-12 21:16pm    
Hi all,
Here are my table structure.
table1(Code [int],itemcode[varchar(20)],itemname[varchar(50)],price [numeric(18,2)],qty[int],amount[numeric(18,2)])
table2(FatherCode[varchar(20)],ChildCode[varchar(20)],docdate[datetime])
Thanks
Sunny_Kumar_ 19-May-12 0:54am    
Hi soeun tony,
please share table data also... could be a sample.

1 solution

I'm not sure what you wnat, but the below query selects all non-duplicates FatherCodes from table2.
SQL
SELECT DISTINCT FatherCode
FROM table2


This query selects all records from table2 where FatherCode is equal 'A'.
SQL
SELECT *
FROM table2
WHERE FatherCode = 'A'


It looks like the relation between tables doesn't exists, unless it is table1.itemCode = table2.FatherCode
To select all records from both tables based on above relation:
SQL
SELECT t1.*, t2.*
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.itemCode = t2.FatherCode


[EDIT]
1 option:
SQL
SELECT DISTINCT t1.[ItemCode], t1.[Quantity]
FROM Table1 AS t1 RIGHT JOIN Table2 AS t2 ON t1.ItemCode = t2.FatherCode


2 option:
SQL
SELECT DISTINCT [ItemCode], [Quantity]
FROM Table1 AS t1 
WHERE [ItemCode] IN (SELECT [FatherCode] AS [ItemCode]
			FROM Table2)


[/EDIT]
 
Share this answer
 
v2
Comments
soeun tony 20-May-12 21:22pm    
Hi Losmac,

Thanks
Maciej Los 21-May-12 1:30am    
You're welcome ;)
soeun tony 21-May-12 2:37am    
Hi Losmac
Please go to this link then you will see the 2nd Solution.That solution is what i want to be.
http://www.codeproject.com/Answers/388358/Select-Statement-in-sql-server-2008-R2#answer2
Thanks
TONy
Maciej Los 21-May-12 12:30pm    
See my answer after modifications.
soeun tony 21-May-12 23:45pm    
Hi losmac
I have followed with your idea.For your 1 Option,it displays include child item and father item and it doesn't display an item that not child or father(in example is item J)). For your 2 Option,It displays only the father Item and doesn't display item (J).

Thanks

TONY

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900