Newb - Mutiple-column Join

Mar 23, 2007

Hi,

very new to SQL queries, and strugling with join concept

I had to do a join based on a single field:

select
*
from
tableA, tableB
where
tableA.value = tableB.value(+)
;

this works fine

but how can i do the same thing while comparing multiple columns ... i
was thinking something like this: (obviously doesn't work)

select
*
from
tableA, tableB
where
[tableA.value1, tableA.value2] = [tableB.value1,
tableB.value2](+)
;

Is there some sort of "tuple" comparison I can do?

Thank you.

View 2 Replies


ADVERTISEMENT

Mutiple Random Source To Mutiple Respective Destination - Update And Insert

Sep 5, 2007



Hello All


Firstly thanks a lot Phil and Jamie on such a helpful article on "Checking to see if a record exists and if so update else insert"

Here is my question


I have about 10 tables and there respective working tables
For examples: A, B, C, D, E.... and WorkA, WorkB, WorkC....

Notes:
1) When I execute a package these work table (Work A, WorkB ...) get populated with certain rows say about 5
2) Its not that all the work table are populated on every execution.
3) Tables A, B, C... have thousands of records in it.
4) Work table is of same structure as there parent table..Like WorkA same structure as A.....
5) The table A and WorkA and as on... are linked with a KeyID

Now I want to build a SSIS package that can
1) Get the the data from these multiple tables(WorkA, WorkB...)
2) Process each row of these tables WorkA, WorkB..
3) Depending upon the KEYID of WorkA., WorkB.. etc Update a Flag colunm of table A, B...where the KeyID is equal to KeyID of Work Table
4) After updating insert that processed row of Work A, WorkB ...into Table A, B..

I can do this if I have one source table and one destination table. Here i have some say 10 randomly source tables to respective random destination .
All I could think of creating 10 different packages as adviced in Jamie's article. But I am sure there might some other alternative.

Can somebody advice me the best practice of doing this. Thanks a lot in advance


View 5 Replies View Related

How To: Mutiple Select Statements Where 2nd Uses Column Value For Each Record From The First

Jul 16, 2006

To clarify, I'm trying to create a hiearchial grid that will display projects and their sub projects.

The page the sproc is servicing is a search page where a user can display all projects by a customer or they can enter in a projectID and return info that way.  Thus you'll see If else in what I have listed below.  I highlighted in red the 2nd select statement I'm trying to get to run.  One problem is I'm getting an error:

Msg 156, Level 15, State 1, Procedure sp_GetProjects, Line 33

Incorrect syntax near the keyword 'ELSE'.

Think this has something to do with me trying to run 2 select statements in an IF.  Secondly, I'm not sure exactly what syntax I need to use to accomplish what I'm trying to do.  Again, the first select statement is creating a table with all projects that match the passed custID, the second select statement I'm trying to generate a 2nd table that will take the projectID for each row returned to the first table and find all rows with the same projectID's in the BillGroup table.

 I need these two tables returned to a dataset I'm populated with a sqldataadapter in .net so I can create a relationship between the two tables and then bind to a grid.

 

@custID nchar(25) = NULL,

@projectID bigint = NULL

IF @custID != 'null'

   SELECT p.projectID, p.Description FROM Projects p

    WHERE custID = @custID



     SELECT * FROM Projects p

     JOIN dbo.BillGroups bg ON p.projectID = bg.projectID



ELSE IF @projectID != -1

   SELECT projectID, Description FROM Projects



   WHERE projectID = @projectID

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here



END

View 3 Replies View Related

Fuzzy Lookup | Mutiple Identity Column Error

May 22, 2007

Hi,

I have a table t1 and t2 with following structure.

t1(

ID1 int IDENTITY PRIMARY KEY,

name1 varchar(20),

addr1 varchar(20)

)



t2(

ID2 int IDENTITY PRIMARY KEY,

name2 varchar(20),

addr2 varchar(20)

)



Objective here is to match name1 and name2 column using fuzzy look up. So, I used t1 as source table and t2 as reference/lookup table and mapped name1 and name2 column in Fuzzy look up editor. As output column I selected "ID2" column from t2.



Now when i run the package, it throws error

"Multiple identity columns specified for table '##FLRef_070522_14:16:39_5064_c1c6cbbd-5a54-4e36-9154-1371118f0931'. Only one identity column per table is allowed."



I suppose that during Fuzzy lookup, SSIS internally created temporary table and thats where this error occurs when adding two columns as identity. Can someone help me in resolving this issue.



Thanks

Sid

PS: I need ID2 column as output for further calculation.







View 3 Replies View Related

Mutiple Column Group Subtotals, Custom Subtotals Expressions/Calculations

Jul 9, 2007




































Some DB Field ID
X
2007
2008

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
A
X-A
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
B
X- A -B

1
500
10
20
0
0
0
0
0
0
0
20
50
0
100
400
25
10
10
0
0
5
0
25
15
10
10
20
130
270

2
750
10
10
10
20
20
10
10
20
10
10
10
10
150
600
20
20
20
20
30
30
10
10
10
30
30
30
260
340

3
600

































All,



I am trying to achieve something as above. Basically, the Months subtotals are represented by A and B. Then (X-A) and (X-A-B) are also the subtotals at the same group level as A and B but don't simply display the total for respective years 2007 and 2008, instead those are remaning totals from X. In order to calculate the remaining totals however, one need to consider the subtotal in previous group. For example, for 2007 its X-A, but for 2008 its X-A-B. I would like to know if this can be achieved using Matrix control. If so, what would be the steps?



Thanks.



View 5 Replies View Related

Help The NewB

Mar 24, 2003

Well the boss suddenly decided to slap me with one mean!!! (when you have never worked with SQL/MSDE) project, any help or guidance will be gretly appreciated :)

Q: Also understand how MSDE works. Find out if an MSDE database can be used independently of SQL Server. i.e. figure out how an MSDE database gets used as an "off-line" version of SQL Server. I.e. so you can carry a portion of a SQL Server database away with you on a notebook and synchronise back when you re-connect

..... the question is a little broken but i'm sure one of you wiz's will understand,

BTW. I have a little knowledge of Enterprise manager, SQL DB's, Backing up/Restoring, basics in other words, so I might seem a little slow ;) if you bombard me with complicated replies.

Thanks once again.

View 7 Replies View Related

Newb Question

Oct 30, 2006

If I am using Ado.Net from a .Net Class library hosted outside SqlServer to do Sends and Receives do I have to use anything else other than queues? The reason I am asking is because I already have an abstraction layer for a couple of our queueing systems and I was hoping to put SSB in there as well but the abstraction layers' primitive is the Queue not Conversation/Dialog/Service, etc.

Thanks

Costas

View 8 Replies View Related

Ultimate Newb Question

Feb 1, 2006

im trying to gain some experience using mssql. in my system tray there is a sql server service manager. my question is this...what do i use to interface with sql server? ive seen messages referencing 'enterprise manager', but i cant find it or this thing called 'query analyzer'.

im running windows 2000 professional. i downloaded and installed a program called 'database fishing tool' which seems to do what i need but, im asked for a username and password. since ive never connected to the sql server i dont know this info.

ive scoured the web for anything to help me with this, but cant find any info for what seems would be the most fundamental...

please help. thank you.

View 6 Replies View Related

Newb: Managing Multiple Queries

Dec 7, 2007

I am currently using enterprise manager to run multiple queries on asingle table in a DB. I refresh these queries every few minutes. Dueto the huge number of them I was looking for a better way (or should Ijust say "a way") to manage/save these queries so I can recall themeasier/faster for monitoring purposes. Suggestions?TIA.

View 4 Replies View Related

(very Newb) Created A Legend For Reports But................

May 7, 2008

.......I need to know how to have that displayed before the reports are ran. I'd like to have in the Report Parameters area so users can see this information when making choices for the reports.

View 1 Replies View Related

NEWB: Moving DBs To New Install Of SQL Serv. W/o Sp_detach_db

Jul 20, 2005

Hi,After a power outage (and UPS failure), my boss's SQL Server 2000 won'tstart (or crashes quickly or something). We don't have any backups, as it'sa test server. Is there a way to import the database files into a new SQLinstallation? By "installation," I mean either a re-installed SS, a secondinstallation on the same PC, or maybe even on another PC. In another words,given only the files sitting on the harddrive, is there a way to "import"the DBs in those files into SS?Sp_attach_db would probably be the answer, except it seems to requirerunning sp_detach_db...which I cannot do, as SS is not working.Thanks in advance,Jay

View 2 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Mutiple Insert

Jan 3, 2006

Hi there,
I have been at this problem for a few hours now using Net 2.0, and can't seem to find an answer.
I am using a asp:calendar that allows the user to pick multiple dates.
When the user is finished and hits the command button, I would like for those dates to be inserted into a table in my SQL DB called Dates.
The mutildate calendar works fine--, and I have placed all of the sates into an Array.  And now have a loop for the array to print them out.
What I want to do, instead of printing, is insert them.
C anyone post some 2.0 code behind for insertion? I have found other "batch insert" code, but when I place in the VB, I get errors telling me it is no longer supported.
For example:
Dim sqlCon As New SqlConnection (no longer works in 2.0) since it doesn't recognize Sqlconnection.
Should I use a SqlDataSource? And if so, how do I prepare the Insert command for it?
Any and all help appreciated!
 
 

View 6 Replies View Related

Mutiple Dataset In RDL?

Feb 22, 2008

Hi Friends,

Is it Possible to combine two different dataset in a single rdl file ?

for example:

emp dept

eno enmae deptno deptno depatname
--------------------------- ----------------------------
1 x 10 10 computer
2 y 20 20 Testing



In RDL File out put will be

Eno Ename Deptname

------------------------------------
1 x computer
2 y Testing



PS : I am not used the join condition.

Thanks & Regards,
G.V.Senthilkumar
+91 9894017253.

View 6 Replies View Related

Delete Mutiple Records

Feb 9, 2007

Hi,

I have two tables one called students and location.

I want to delete the location and all records in student corrosponding to that location, can any help or point me in the right direction.

Many Thanks

View 2 Replies View Related

How To Create Mutiple 4G Database

May 21, 2007

I understand SQL Express has a database size limit of 4G, but I read some forum correspondence here saying the 4G is a per database limitation, and within the Express instance, it can have multiple database of 4G in size.

I create multiple database and got an error saying size limit reached for the primary filegroup. Does that mean the 4G limits apply to the total database size ?

KL

View 1 Replies View Related

Retrieving Mutiple Rows

Sep 21, 2007



I have a table like this.

Depositors Table

Value(int) StartDate(Date) AccountID(int)


I want to create a report from this table. the report should look like this.





Value No of Accounts Average Value

For Yesterday
For Last 7days
For Last 30 days


Please Can anyone write a simple query for this?

Thanks

View 3 Replies View Related

Mutiple Processes/connections Problem

Dec 27, 2004

Hi All,

I'm relatively new to ASP.NET coming from ASP. I've created a new, relatively simple ASP.NET application using WebMatrix with various controls and pages accessing an SQL Server database.

3 people are trying to use this app. After a short amount of time, the server starts kicking back an error message that it can not open a connection. I go in with Enterprise Manager to the SQL Server and I can see a long list of processes there almost as if each page is opening a connection and not closing. I'm explicitly closing all connections in my code where I open them for use by a data reader. What about data grid controls. Do they leave connections open? How about the drag and drop insert/update/delete functions. Unless I'm mistaken, this should close the connection when the function completes, shouldn't it:

Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

I originally did this app connecting to an Access database but had the same problem. I figured it was due to the known connection issues with Access via ADO so I upsized to SQL Server and over time, get this same problem.

Any help would be appreciated!
Thanks,
Larry

View 2 Replies View Related

Multiple Reference To Mutiple Tables

Aug 23, 2007

Not sure if the title describes my situation or not.

Simplified example is:
I have an [Employee] table with EmpCode, EmpName

I have a second table [NewHires] that has: HireDate, EmpCode, Addedby

Both EmpCode and Addedby contain EmpCode referring to the Employee table.

I wish an output similar to:

New Employee (from EmpCode in NewHire), Hired on (From HireDate), Hired By (from Addedby)

My problem is with an Employee.EmpCode=NewHires.Empcode or Employee.EmpCode=NewHires.Addedby in the Where clause or Join part of the SQL I don't know how to get EmpName from the Employee table twice but using two different EmpCode as the reference.

Thanks in advance - Shawn

View 2 Replies View Related

Referential Integrity In Mutiple Tables. Need Help.

Oct 12, 2004

I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.

Example:

Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|

Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|

Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|

Table 4
======|====|
StateID Name
======|====|

How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?

View 7 Replies View Related

Server Crashing With Mutiple Database

May 20, 2004

I got a server that Crashed. My network group was able to get it up and running but it's vary fragile. One of the disk is done. What's the best way to get the my database:tables,views,stored procedured,dts packages, jobs off the bad server without crashing it again? I used the databae copy utility but found out that some of the database are replicated and it wont allow it to copy.

Thanks


Just another day in the life of a dba

View 2 Replies View Related

Using Osql To Apply SPs In Mutiple Threads

Jul 20, 2005

using osql to apply SPs in mutiple threadsHello,I got a weird problem when I was using osql to apply scripts for msdedatabase in multiple threads mode. Sometime 2 sps were missing duringthe whole apply process, sometime not, and seems like only those twoSPs met the problem. No error was appeared. Did anyone meet sameproblem before? Or any possible solutions?Thank you very much!

View 4 Replies View Related

Mutiple Table Single Task

Sep 21, 2007

I have about 100 different tables that I would like to bring on nightly basis via integration services. SSIS will process the data and send on to its warehouse destination.

Is it possible to use a single task to bring in all these tables from within SSIS? and can write failed records to flat files at the sametime? instead of defining a data flow for each table and dealing with hundreds of dataflows just have one task that loop through the list of tables.




Wonder how warehouses fed by 100s of tables deal with this kind of scenario?

View 8 Replies View Related

Mutiple Backup Is Good Choice?

Feb 9, 2008



Hi,

I am using a stored procedure to take backup of my database from the Visual Basic Programming.

Before i posted one of my thread with the same thing, so i was recommended to go through with DMOSQL do deal with SQL server with Visual Basic Programming. For me, this takes some time to understand the complete concept.

Because of urgent i am using stored procedure to take backup with the following:

---------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.BackUPBLMSDB

(
@RP nvarchar(200)
)

AS

declare @backupfilename nvarchar(200)
set @backupfilename=@RP
BACKUP DATABASE [BLMSDB] TO DISK = @backupfilename WITH NOFORMAT, NOINIT, NAME = N'BLMSDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
---------------------------------------------------------------------------------------
Example @RP="D:ackupBackup-1 09-02-2008 11-24"


Every time i am passing a parameter with somename and dateandtime(System).
Example

Backup-1 09-02-2008 11-24 2900KB
Backup-1 09-02-2008 12-30 2900KB
Backup-1 09-02-2008 18-10 5400KB
Backup-1 09-02-2008 22-00 2900KB
Backup-1 09-02-2008 22-00 2900KB

I would like to clear my doubt, is it a good practice to take backup with different names. The above one i store four backups . If the system crashes and i created the new database with the same schema without any data present in the tables, can i restore previous backup database to the newly created database.

Moreover, The first two backups contains "2900KB", the third one is "5400KB" after the data is being modified. Look at the fourth one it is "2900KB". Why the size is being reduced to "2900KB" after taking backup eventhough i didn't delete or added data into the database.



Hope you will solve my problem.

Thanks.



Best Regards,
Kashif Chotu





View 5 Replies View Related

Using One Alias For Mutiple Columns In A SELECT Statement

Feb 8, 2006

Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT    RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.],         LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building],        LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street],        LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District],        LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town],         LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County],        RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst

View 2 Replies View Related

Count Same Field Mutiple Times With Different Criteria

May 12, 2006

Is it possible to count the same field with different criteria. It would be something like this.

car_table
car_id
car_name
car_brand

So you would execute a statement which would count(car_brand) with two different criteria.

I am not sure if this is possible or if there is another way to approach it.

View 1 Replies View Related

Using One Alias For Mutiple Columns In A SELECT Statement

Feb 8, 2006

Hi all,

Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?


Code:


SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.],
LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building],
LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street],
LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District],
LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town],
LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County],
RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]



Thanks

Tryst

View 2 Replies View Related

Stored Procedures Return Ing Mutiple Rows

Jul 29, 2006

hello. I am trying to speed up my asp.net caledar.

I need a Stored Procedure that takes a Parameter.
the Parameter will be a Date.

then I want sql server to return all the classes that will happen on that date in formated string so i can display in the Asp.net caledar.

the table looks like this
classid
classname
classdate
classtime
(there are more than one class happening on the same day)


I need the retuen value to look like this

"Yoga 9:00am" & "<p>" & "Jazz 11:00am" & <p>


can someone help me or point me to a sample. this is really hard. thank you

View 6 Replies View Related

Deleting Mutiple Rows Based On Info

Apr 12, 2007

Is it possible to delete multiple rows from multiple tables based on information specified. Can you write a query that would pull the information if you knew what tables it would need to look in? If anyone know I would greatly appreciate any help I am not sure of this.

Thanks,
Traci

View 1 Replies View Related

Returning Row Counts On Mutiple Search Values

Sep 13, 2007

I need to return row counts for a list of all our users. The problem with the first query is that it doesn’t search for names within the column, it considers a list of email addresses a unique entry. I need to be able to see how many times each email address appears in the database. The second query obviously does that but I don’t want to have to copy and paste 500 usernames. the to_addr_head is a text column if that matters. Thanks!!

SELECT
Table1.to_addr_head,
COUNT(*) AS "COUNT(*)"
FROM Table1
group by Table1.to_addr_head
ORDER BY "COUNT(*)" DESC;


select count(*)
where Table1.to_addr_head like '%username%'

View 15 Replies View Related

Change Data Source For Mutiple Datasets At Once

Jun 4, 2007



Hi All,



In the BI development studio when I have to change the data source for data sets within a report, I have to go to each of the datasets individually to do this. Is there a quicker way to do this. Say I want to change data source for the entire report in BI dev studio.



thanks



Sonny

View 3 Replies View Related

Execute Mutiple Tasks In Debug Mode

Apr 7, 2006

Maybe I'm missing something, but I can't find how to run multiple tasks in sequence while in Visual Studio debug mode. In DTS design mode I grew accustomed to right-clicking tasks one-at-a time, but in SSIS I find the additional step of having to exit Debugging mode after every task gets old after a while.

There must be a way to start execution at a certain task and have the package continue all the way to some other specified task. It would also be nice to have every task in a Group execute in sequence and stop (even if connections continue beyond the group). I could even settle for repeatedly clicking the Continue button in Debug mode, but it's always grayed out when the current task is finished!

Can this be achieved by setting breakpoints?

View 3 Replies View Related

One Package, Two Configurations, Mutiple Databases On Same Machine

Jun 14, 2007

Hi,



I have package on a single server. This packages extracts Data from System A and loads System B, database C. What I'd like to do is use configurations to have this one package loads System B, database C and D.



When a I create two configurations (one pointing at database C and one pointing at database D) and run the package only the last configuration pointing at database D gets loaded.



Anyone know how to get around this and use configurations to load both database C and D?



Thanks,



Mark

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved