Help in SELECT Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help in SELECT Query

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-02-01 : 08:20:38

Please go through the Tables Structure lay out once, plz

Lets consider 2 tables ( Products, SalesChild)

[Products]
ProductID
ProductName
..
TagID*

Here the TagID Groups a list of products as a Group ( List of Items in packed Buddle or location or something like that)

[SalesChild]
InvNo
..
..
ProductID
..

Here The table Products and SalesChild is related by ProductID ( Only )

Field TagID in table Products Helps to list the Group of items in that list.

Few of the items may Sold out from this list!

I have to list the Only Remaining Items in Table Products when One Chose a TagID!

Hopes you people Understood! Let me demonstrate bit more for the others who didnt understood the Matter, pls

Lets take these are the data in Both Tables
[PRODUCTS]
{ProductID} {ProductName} {TagID}
1001 A 1
1002 B 1
1003 C 1
1004 D 1
1005 E 2
1006 F 2
1007 G 2
1008 H 2

[SalesChild]
{InvNo} {ProductID}
5001 1001
5001 1004
5002 1007

Seach in Product list by TagID should list the remaining products
For eg:- Search in table Products by TagID = 1 must list

1002 B 1
1003 C 1

Search in table Products by TagID = 2 must list
1005 E 2
1006 F 2
1008 H 2

Because the other items where Sold out.

Please help me by Query Statement To list only the remaining Items in Product List by TagID

Thank you

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-01 : 08:25:11

[code]Select p.*
from Products p
LEFT JOIN SalesChild sc
on p.ProductID = sc.ProductID
where sc.ProductID is NULL
and p.TagID = <TagID-value>[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-02-01 : 09:27:13

Harsh, Please not in Table PRODUCTS there is no filed TagID!! Only ProductID is the relating filed!!
Hopes you will able to correct it for me,! Thank you

quote:
Originally posted by harsh_athalye

Select p.*
from Products p
LEFT JOIN SalesChild sc
on p.ProductID = sc.ProductID
where sc.ProductID is NULL
and p.TagID = <TagID-value>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 09:33:30

quote:
Please not in Table PRODUCTS there is no filed TagID!! Only ProductID is the relating filed!!

what do you mean by this ?

Have you try the query Harsh posted ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-01 : 09:35:35

"Field TagID in table Products Helps to list the Group of items in that list"

Then what do you mean by this?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 09:49:37

Isn't this what you want ? ?


declare @Products table
(
	ProductID	int,
	ProductName	varchar(10),
	TagID		int
)

insert into @Products
select 1001, 'A', 1 union all
select 1002, 'B', 1 union all
select 1003, 'C', 1 union all
select 1004, 'D', 1 union all
select 1005, 'E', 2 union all
select 1006, 'F', 2 union all
select 1007, 'G', 2 union all
select 1008, 'H', 2

declare @SalesChild table
(
	InvNo		int,
	ProductID	int
)

insert into @SalesChild
select 5001, 1001 union all
select 5001, 1004 union all
select 5002, 1007
-- Harsh's query
Select 	p.*
from 	@Products p LEFT JOIN @SalesChild sc
	on	p.ProductID = sc.ProductID
where 	sc.ProductID is NULL
and 	p.TagID = 1

/* RESULT as per your specification
ProductID   ProductName TagID       
----------- ----------- ----------- 
1002        B           1
1003        C           1
*/

-- Harsh's query
Select 	p.*
from 	@Products p LEFT JOIN @SalesChild sc
	on	p.ProductID = sc.ProductID
where 	sc.ProductID is NULL
and 	p.TagID = 2

/* RESULT as per your specification
ProductID   ProductName TagID       
----------- ----------- ----------- 
1005        E           2
1006        F           2
1008        H           2
*/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-01 : 09:55:12

Thanks KHTan for that wonderful piece of sample data and code.

But is it worth for those people who neither look the query carefully nor care to execute it !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources