joins

hi

in mya pplication im trying getquery coming table ,i don't know how it.below tables

create table [dbo].[r2inventorytable]( 	[productid] [int] identity(1,1) not null, 	[productname] [nvarchar](50) null, 	[productdesc] [nvarchar](max) null, 	[phcode] [nvarchar](max) null, 	[pmno] [nvarchar](max) null, 	[plineno] [varchar](50) null, 	[relayrack] [varchar](50) null, 	[sno] [varchar](50) null, 	[assettagno] [varchar](50) null, 	 constraint [pk_r2inventorytable] primary key clustered  ( 	[productid] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary]  go set ansi_padding off

select r.pmno,r.productdesc,r.productname r2inventorytable  r

output

modelno      productdesc            productname
4             testing1 desc        testing1
4             testing2                 testing2
34             testing3 desc        testing3
7677xe             acer 15"  crt monitor   crt monitor
e40             viewsonic 14" monitor crt monitor
ultrascan p780      dell 17" monitor         crt monitor
tx-d1752l     at&t 17" monitor          crt monitor
786n            e-machines 17"         crt monitor
6554            ibm 17"                 crt monitor
 

create table [dbo].[recyclecomponents1table]( 	[rid] [int] identity(1,1) not null, 	[productid] [int] null, 	[productname] [varchar](50) null, 	[metal] [nvarchar](max) null, 	[plastic] [nvarchar](max) null, 	[electronic] [nvarchar](max) null, 	[cables] [nvarchar](max) null, 	[type] [varchar](50) null, 	[modelno] [varchar](50) null, 	[productdesc] [nvarchar](max) null, 	[shippinginfo] [nvarchar](max) null, 	[shippingaddress] [nvarchar](max) null, 	[productinfo] [varchar](50) null ) on [primary]  go set ansi_padding off go alter table [dbo].[recyclecomponents1table]  check add  constraint [fk_recyclecomponents1table_r2inventorytable] foreign key([productid]) references [dbo].[r2inventorytable] ([productid]) go alter table [dbo].[recyclecomponents1table] check constraint [fk_recyclecomponents1table_r2inventorytable]

select productname,productdesc ,electronic ,modelno recyclecomponents1table   output productname     productdesc          modelno testing1	testing1 desc		4 crt monitor	viewsonic 14" monitor	e40

if productdesc  existed in recyclecomponents table want display only  remaining product desc .im not able please have look

hi ,

  try using left join

   select r.pmno,r.productdesc,r.productname       r2inventorytable  r left join recyclecomponents1table rc        on r.productdesc = rc.productdesc     rc.[rid] null

---

best regards sorna

SQL Server  >  Getting started with SQL Server