help

[zhj]

i use sql 2000 server. what query gets me T3 from T1 and T2?.

T1:
col1,col2,col3,col4,col5,col6:

Store1    district1     Inv1    PRICE1    item1    3
Store1    district1     Inv1    PRICE1    item2    3
Store1    district1     Inv1    PRICE1    item3    0
Store1    district1     Inv2    PRICE3    item1    3
Store1    district1     Inv2    PRICE3    item2    3
Store1    district1     Inv2    PRICE4    item3    0

T2:
col1,col2,col3:

A      AVAIL    item1    
B      AVAIL    item3

T3:

Store1   district1    Inv1   PRICE1   A   AVAIL  item1   3
Store1   district1    Inv1   PRICE1   B   AVAIL  item3   0
Store1   district1    Inv2   PRICE3   A   AVAIL  item1   3
Store1   district1    Inv2   PRICE4   B   AVAIL  item3   0

[andreasblixt]

Well, that's pretty vague information and poor names for tables/columns. But assuming that "T2" holds a list of items that should be shown in "T3" (excluding items not in "T2"), this query should do it:

SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM T1
INNER JOIN T2 ON T2.col3 = T1.col5

I'm using a join because I believe the database engine will work fastest that way at excluding rows that don't match with "T2". A more logical way of doing it would be either:

SELECT col1, col2, col3, col4, col5, col6
FROM T1
WHERE EXISTS (SELECT * FROM T2 WHERE T2.col3 = T1.col5)

or this one (most likely slower):

SELECT col1, col2, col3, col4, col5, col6
FROM T1
WHERE col5 IN (SELECT col3 FROM T2)

[zhj]

thank you for your suggestions. I tried it but gave me a blank output. basically, i need to add the first two columns of #temp2 to #temp1 and keep only those rows in #temp1 that are in #temp2 (like above T3). i tried to use "select *" from two tables and gave an output but was not right. i tried the following stored procedure. but no luck.

CREATE PROCEDURE TestSP
AS

create table #temp1 (col1 nvarchar(20), col2 nvarchar(20), col3 nvarchar(20), col4 nvarchar(20),col5 nvarchar(20), col6 smallint)
insert into #temp1 values('Store1', 'district1',' Inv1', 'PRICE1', 'item1', 3);
insert into #temp1 values('Store1', 'district1',' Inv1', 'PRICE1', 'item2', 3);
insert into #temp1 values('Store1', 'district1',' Inv1', 'PRICE1', 'item3', 0);
insert into #temp1 values('Store1', 'district1',' Inv2', 'PRICE3', 'item1', 3);
insert into #temp1 values('Store1', 'district1',' Inv2', 'PRICE3', 'item2', 3);
insert into #temp1 values('Store1', 'district1',' Inv2', 'PRICE4', 'item3', 0);

create table #temp2 (col1 nvarchar(10), col2 nvarchar(20), col3 nvarchar(20))
insert into #temp2 values('A', 'AVAIL', 'item1');
insert into #temp2 values('B', 'AVAIL', 'item3');

/*
SELECT t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t1.col6
FROM #temp1 as t1
INNER JOIN #temp2 as t2 ON t2.col3 = t1.col5 */

/*
SELECT t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t1.col6
FROM #temp1 as t1
WHERE EXISTS (SELECT * FROM #temp2 as T2 WHERE T2.col3 = t1.col5) */

SELECT t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t1.col6
FROM #temp1 as t1
WHERE col5 IN (SELECT col3 FROM #temp2 as t2)

DROP TABLE #temp2
DROP TABLE #temp1
GO

[zhj]

andreasblixt, thank you for your helpful answer. I made a tiny bit adjustment to your solution and works fine. thanks.

[andreasblixt]

May I ask what adjustments you made? I tried your queries above exactly as they stand, with the exception of not putting them in a stored procedure but rather running them as separate batches (first I created #temp1 and #temp2, then I ran the queries one by one), and I got the expected result set of four rows for all three of them.

[zhj]

maybe it was my wrong when i tried the procedure. I did not get the A B A B column. that was all missing. i had to add the t2b.col1, t2b.col2 to your query in order to get that A B A B column. thanks again for the help.

[andreasblixt]

Oh, that's right, I didn't see you wanted to include those columns as well, sorry.