item master and office stock details (was "Query Problem")

Hi,

I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:

ITEM MASTER
-----
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E

STOCKDETAILS
-----------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3

I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1

--------------
FOR OFFICE - 1
--------------
ITEMCODE ITEMNAME OFFICEID
--------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL

i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.

Thanks in advanceWelcome to the Forum.
Check this...

SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = '1') AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1|||Hi Rudra !

how to get such all result in view and then i will query for officeid to get my desired output.|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?
Why don't you use stored proc and pass parameter using OFFICEID ? I think that would be a better way to deal with your problem.But I am not sure what your requirement is...|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1

hmm,always ahead...;)|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?

I suggest you to use stored proc,its always good to use stored proc
in your case.Just write this...

CREATE PROCEDURE dbo.StockView(
@.officeid VARCHAR(20)

AS

--Use mine or Rudy's one
--this is mine
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = @.officeid) AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME

--OR use Rudy's one

select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = @.officeid

Go

And check BOL to use Stored proc in dataset .Its very easy man and better to use in many respect
Hope this will help you.sql

issues with full text search (especially views and pick lists)

Hi, I am trying to implement a global full text search on our SQL
Server. Our app has several entities that are stored in the DB. I would
like to be able to search for 'John Doe' and get results in all types
of entities. Problem is:
-FT Search does not crawl views. Unfortunately, each entity type in
our system is not stored in full in one table. This is because we are
using pick lists and look ups. For instance: Industry type is stored as
a code that represents an entry in an industries table. However, all
these values are joined in a view to create all the required fields for
the entity a full text search query and index are created per table.
What would be an effective way to work around this problem? (I don't
see any solution to this problem in Yukon either.)
-Ranking: both 2000 and Yukon do not allow for merging of rankings
from several tables/queries. However, it is important to us to be able
to display results from several tables and sorted in a logical way. Any
suggestions/work arounds?
-Performance and scalability: How many rows and/or how many GB can I
have in my DB and still get good FT search query performance (less than
5 seconds)? (I need data for both 2000 and Yukon)
-Same with regards to indexing: ideally we would like to keep the
index up to date in real time. We would like to use the track changes
feature. However, our app allows many users to be logged in and
edit/delete/add entries in the DB. What is the maximum amount of DB
transactions per minute (second?) that will still allow us to keep the
index updated in real time? (I need data for both 2000 and Yukon)
-
I would greatly appreciate any suggestions/tips/info/workaround.
Thanks!
baraka,
Unfortunately, SQL Server 2000 FTS does not directly support the FT Indexing
of views. However, you can include FTS queries *in* views, but not create FT
Indexes *on* views in SQL Server 2000.
Since you're testing Yukon (SQL Server 2005), have you downloaded and
installed the June CTP version? If not, I'd highly recommend that you do so
and then create one of your views as an "indexed view" as FT Indexing an
indexed view is supported in SQL Server 2005.
Relative to merging ranking for multiple tables (in SQL 2000 or SQL 2005),
try the following example to dump them into a
temp table, the below option as a few advantages, especially as it relates
to "summing" via the MAX function the RANK value as well as using a group
by:
CREATE TABLE #FTSQueryResult (PK_ID int, Rank int)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 1st query)
INSERT #FTSQueryResult
SELECT PK, FTSTable.Rank FROM . . . (and the rest of your 2nd query)
Select PK_ID, Max(Rank) as Rank From #FTSQueryResults
Group by PK_ID
In regards to performance and scalability, for SQL 2000 its less an issue
with amount of text, and more a factor of the number of rows and the
language of the text in the FT-enable columns as well as your server's
configuration - number & speed of procs, amount of RAM and speed of your
disk drives. Review SQL 2000 BOL title "Full-text Search Recommendations"
for more info! As for SQL 2005, I've worked with a customer (now in Yukon
TAP) who FT-indexed a table with 180 Million rows in 8 hours, while using
SQL 2000 on the same server and same database and FT-enable table took over
6 weeks to complete! Checkout the case study of an internal MS application
"SQL Server 2005 - 7x faster than SQL 2000 Full-Text Indexing" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!433.entry
I'd highly recommend that you review all of the links at "SQL Server 2000
Full-Text Search Resources and Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"baraka" <barak.benezer@.gmail.com> wrote in message
news:1122341762.314188.223230@.g14g2000cwa.googlegr oups.com...
> Hi, I am trying to implement a global full text search on our SQL
> Server. Our app has several entities that are stored in the DB. I would
> like to be able to search for 'John Doe' and get results in all types
> of entities. Problem is:
> - FT Search does not crawl views. Unfortunately, each entity type in
> our system is not stored in full in one table. This is because we are
> using pick lists and look ups. For instance: Industry type is stored as
> a code that represents an entry in an industries table. However, all
> these values are joined in a view to create all the required fields for
> the entity a full text search query and index are created per table.
> What would be an effective way to work around this problem? (I don't
> see any solution to this problem in Yukon either.)
> - Ranking: both 2000 and Yukon do not allow for merging of rankings
> from several tables/queries. However, it is important to us to be able
> to display results from several tables and sorted in a logical way. Any
> suggestions/work arounds?
> - Performance and scalability: How many rows and/or how many GB can I
> have in my DB and still get good FT search query performance (less than
> 5 seconds)? (I need data for both 2000 and Yukon)
> - Same with regards to indexing: ideally we would like to keep the
> index up to date in real time. We would like to use the track changes
> feature. However, our app allows many users to be logged in and
> edit/delete/add entries in the DB. What is the maximum amount of DB
> transactions per minute (second?) that will still allow us to keep the
> index updated in real time? (I need data for both 2000 and Yukon)
> -
> I would greatly appreciate any suggestions/tips/info/workaround.
> Thanks!
>