Database

Re: Another IN clause Question

31-Jul-08 11:06

I can imagine. Your first task should be to eliminate the subqueries; they don't benefit from indices.

I had to change some of my JOINs the other day because the statements seemed to just sit there, now they zip along.

Wendelius31-Jul-08 6:48

Wendelius

31-Jul-08 6:48

Hi,

Didn't test this so there may be typos but try something like this:

SELECT *
FROM   UC
WHERE uc_key IN (SELECT mo_Key
		FROM MO  		 
		WHERE 	(mo_MoKey = @Key1 AND mo_Value = @Value1) 
		OR	(mo_MoKey = @Key2 AND mo_Value = @Value2) 
		GROUP BY mo_Key
		HAVING COUNT(*) > 2);

Hope this helps,

Mika

Re: Another IN clause Question

MaxRelaxman31-Jul-08 10:54

MaxRelaxman

31-Jul-08 10:54

Excellent, thank you for your help!

One thousand internet dollars are coming your way, just put your ethernet cable into the trash can can to catch it all.

Query using multiple columns in IN Clause

CodalcoholicNBrainy31-Jul-08 1:17

31-Jul-08 1:17

Hi Everyone,

I am having one table which contains 5 columns. These five columns together contitute the primary key. I am having one more table which contains data for these primary key. I need to write a query which will bring the data from the second table for those primary keys which are available in first table. D'Oh! | :doh:

CodeManiac
xxxxxxxxxx
xxxxxxxxxx

Syed Mehroz Alam31-Jul-08 1:23

Syed Mehroz Alam

31-Jul-08 1:23

If I understand you correctly, you just need a simple inner join.

Select ...
From YourPrimaryTable
InnerJoin YourDetailTable
  on YourPrimaryTable.Column1 = YourDetailTable.Column1
  and YourPrimaryTable.Column2 = YourDetailTable.Column2
  ...

Hope that helps.

Syed Mehroz Alam

My Articles

CodalcoholicNBrainy31-Jul-08 1:40

31-Jul-08 1:40

Thanks for your reply. Big Grin | :-D

Can we do the same thing without taking join. I mean can we use IN clause for multiple columns?

CodeManiac
xxxxxxxxxx
xxxxxxxxxx

Syed Mehroz Alam31-Jul-08 1:43

Syed Mehroz Alam

31-Jul-08 1:43

Confusing...I wonder what do you need. Can you explain using a simple example.

My Articles

CodalcoholicNBrainy31-Jul-08 2:02

31-Jul-08 2:02

Sorry to confuse you.
Let me explain you complete scenario.
I am having two databases DB1 and DB2. DB1 has single table T1 and 15 columns namely C1, C2, C3 etc. DB2 has two table T1 and T2. T1 table is identical in both the databases. So T1 table has same 15 columns and same data in both the databases. T2 has 5 columns C1, C2, C3, C4,C5 which together constitute primary key. In T1 table of DB1 database, some of the records are modified. T2 table of database DB2 has the primary keys of all the modified records. I need to update table T1 of database DB2 for the modified records.
I need a way to accomplish this. Hopefully I am clear now.

CodeManiac
xxxxxxxxxx
xxxxxxxxxx

Syed Mehroz Alam31-Jul-08 2:36

Syed Mehroz Alam

31-Jul-08 2:36

I got it now. This seems a difficult issue. I suggest you to create some trigger on db1.T1 that updates the modified rows in db2.
If you still want to use some batch update, the most inappropriate way will be to use a cursor loop. But wait, lets hope someone else in the forum finds a more efficient way.

Good luck.
Syed Mehroz Alam

My Articles

Blue_Boy31-Jul-08 3:18

Blue_Boy

31-Jul-08 3:18

You have to create any trigger or you can make changes on T1 of DB2 something like query down below which you execute that both querys at same time:

Update DB1.dbo.T1 set DB1.dbo.T1.column='value' where DB1.dbo.T1.colname ='conditionvaue'<br />
Update DB2.dbo.T1 set  DB2.dbo.T1.column='value' where  DB2.dbo.T1.colname ='conditionvaue'

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

PIEBALDconsult31-Jul-08 3:55

31-Jul-08 3:55

Perhaps an inner join in the update?

UPDATE T1
SET ...
FROM T1
INNER JOIN T2
ON T1.blah=T2.blah
AND ...

CodalcoholicNBrainy31-Jul-08 4:04

31-Jul-08 4:04

Just one more thing that this process will happen in Batch.

CodeManiac
xxxxxxxxxx
xxxxxxxxxx

PIEBALDconsult31-Jul-08 6:45

31-Jul-08 6:45

What does that mean?

CodalcoholicNBrainy31-Jul-08 21:20

31-Jul-08 21:20

I mean the proces will happen every night for the data that changed during day time.

CodeManiac
xxxxxxxxxx
xxxxxxxxxx

PIEBALDconsult1-Aug-08 3:14

1-Aug-08 3:14

Shouldn't be a problem then.

I always try to have the database engine do most of the work.

Blue_Boy31-Jul-08 1:54

Blue_Boy

31-Jul-08 1:54

select FirstTable.*,SecondTable.*<br />
from FirstTable,SecondTable<br />
where SecondTable.FirstTableColumn in (FirstTable.FirstColumn)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

creating a pivot table

prithaa30-Jul-08 22:06

30-Jul-08 22:06

Hello,

I had posted the question before .Sorry to post it again since I thought my question went to the 2nd page.

I want to write a pivot table query and transfer the results in a table

The following query works but the result doesn't get transferred in a table

Select Cap orderby JobWorker pivot TimePeriod into CrossTabCap

Regards
Pritha

Ashfield30-Jul-08 22:18

Ashfield

30-Jul-08 22:18

Well that is no reason to post again. In the first place, you double posted. Then you didn't include any code, so what do you expect?

I have just done what you could have done, Goggled for SQL Server 2005 Pivot and got loads of results with example code.

Bob
Ashfield Consultants Ltd

prithaa31-Jul-08 2:35

31-Jul-08 2:35

Thank you for all your replies.

prithaa31-Jul-08 3:12

31-Jul-08 3:12

Hello,

Before posing question even I have googled on pivot query,pivot sql query but none of the sites explained the synttax abut making a new table from a pivot query .And that's the reason I asked the question .I know the pivot query but how make a table of the results from the query.
Using INTO for the pivot query didn't work.

The following query works but I want to make a table

TRANSFORM sum(cCapacity) AS SumOfCapacity
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract into y;

Thanks and sorry.

Pritha

TheFM23431-Jul-08 7:44

TheFM234

31-Jul-08 7:44

Have you tried sub-querying the pivot?

Select *
Into Table
From
(
TRANSFORM sum(cCapacity) AS SumOfCapacity
SELECT jobworker, sum(cCapacity) AS Total
FROM cCapacity
GROUP BY JObWorker
PIVOT contract into y
) t

prithaa31-Jul-08 18:51