Getting The Matching Columns From Two Tables
Mar 1, 2006
Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure....
Waiting for your response....
View 2 Replies
ADVERTISEMENT
Mar 10, 2014
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
View 2 Replies
View Related
Jul 20, 2005
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View 2 Replies
View Related
Sep 7, 2004
All
I have a table which contains 4 columns each of which are NULL or contain a 6 digit code.
Here is a sample of the table content:
COL1 COL2 COL3 COL4
----- ----- ----- -----
452359 NULL NULL 347406
NULL NULL 347406 347406
592319 NULL 347406 347406
592319 150009 347406 347406
592319 150010 347406 347406
Through out the table any number of the columns can be null.
I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4.
So, the only rows I want from the sample data above are rows 1, 4 and 5.
I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)
Regards,
Katherine
View 6 Replies
View Related
Mar 12, 2008
I have 2 columns from 2 separate tables.
Col1 is AccountName from tblOpportunities
Col2 is BillToName from AccountCodes
I am trying to get the results from tblOpportunities.AccountName that DO NOT also exist in AccountCodes.BillToName
Thanks
View 3 Replies
View Related
Jul 16, 2015
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact];
CREATE TABLE [dbo].[SourceContact]
(
[SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED,
[ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''),
[ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
[code]....
View 2 Replies
View Related
Apr 8, 2008
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT A
22 MAIN C
22 INT A
I want the result to be:
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT IB
22 MAIN C
22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S
SET s.code = B.code
FROM tbltest B
LEFT OUTER JOIN tbltest S ON B.id = S.id
WHERE (S.loc = 'INT')
But when I run it, it says "0 rows affected".
View 5 Replies
View Related
Jun 28, 2013
I have some stored procedure and there is a cursor inside it. I added some new columns to the table and those columns I included in the cursor declaration and fetch statement. In the cursor declaration I forgot to add comma (,) in between the new columns. So SQL Server it considers as a alias name for that column so syntactically it is correct. But logically in the cursor declaration having less number of columns than the columns in the fetch statement. So it should throw an error. But the procedure is getting compiled without raising any error. But if I execute the procedure that time it is throwing the error.
For example, below I have given the sample procedure. In this procedure, in the cursor declaration I removed the comma (,) between DOB and DOJ. If I compile this procedure it is getting compiled. But when execute that time only it is throwing the error. So I am interested in if any option is available to know the error in the compilation time itself.
ALTER PROCEDURE Test
AS
BEGIN
BEGIN TRY
DECLARE @empId INT,
@fname VARCHAR(50),
@dob DATE,
@doj DATE
[code]....
View 3 Replies
View Related
Jan 9, 2008
How would you do the following in SSIS?
SELECT a.TestID,
a.TestCode
FROM TableA a
WHERE UPPER(RTRIM(a.TestCode)) IN SELECT (SELECT UPPER(RTRIM(b.TestCode)) FROM TableB b)
Of course the above query is missing a few things but with ETL the where clause UPPER(RTRIM does not appear to be something that has an object or property that I can use in the Lookup.
Please correct and educate me.
View 4 Replies
View Related
Feb 8, 2008
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
View 9 Replies
View Related
Aug 10, 2015
I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
View 4 Replies
View Related
Sep 3, 2014
I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
View 5 Replies
View Related
Apr 17, 2001
Hey all,
Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
-Marc
View 1 Replies
View Related
Jan 16, 2004
Hi,
I am new to SQL and these forums, and have a quick simple question.
I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.
Here is an example of what the two tables contain.
Table 1
customer_no eaddress_no name address (36000 records)
Table2
customer_no eaddress_no email (17000 records)
I need to find out which customers are not in table 2 by linking the eaddress_no numbers.
This is one of the scripts i ran that gives me the large results with a ton of dups:
SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no
Thanks in advance for any help with this.
View 13 Replies
View Related
Jul 20, 2005
I have a situation where I want to pull strings from one table of a SQL 2000database and find matches for it in other tables of the same database andhave those values returned. i.e. In one table I have prospects and I wantto match their names to a table that stores the names of prospects turnedinto customers. I want to write a query that looks through every entry andreturns a match for each corresponding value (from prospects to customers).So if "Smith" is found in prospects I want SQL to return "Smith" incustomers with full contact info.Any pointers on getting started on this is greatly appreciated. Or if youcould just point me to a reference. Obviously, I need to do some kind ofparsing. I just need to be pointed in the right direction.Thx.
View 2 Replies
View Related
Aug 9, 2007
Let's say I have a list of IDs called EntryID and each EntryID can belong to ONE table out of a group of six, what is the best way to get a listing of these?
For example:
select r.*
from #Reminders r
left join mytable1 mt1 on (r.EntryID = mt1.EntryID)
left join mytable2 mt2 on (r.EntryID = mt2.EntryID)
left join mytable3 mt3 on (r.EntryID = mt3.EntryID)
left join mytable4 mt4 on (r.EntryID = mt4.EntryID)
As you can see, #Reminders has one field called EntryID (and many rows).
In my example above, only ONE of those tables will actually be able to join but I have no idea which one has the matching EntryID.
What is the best way for me to do this? I want to grab "ReportStatus" from the corresponding "mytable"... (each "mytable" has a ReportStatus column)
View 5 Replies
View Related
Mar 31, 2014
I have 3 table
table_A
table_B
table_C
TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908
TABLE_B
SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766
I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C
EX-
SNO NAME ID
1 RAJU 070491
View 3 Replies
View Related
Aug 2, 2013
Two tables:
table #1 = timestamp, servername1(never changes), value(int)
Table #2 = timestamp, servername2(never changes), value(int)
What I need to do is merge the two tables into a single table based on the matching timestamp:
timestamp, servername1, servername2 as headers then values under
"1/1/1-1:10:1" , "10" , "10" - as an example
"1/1/1-1:20:1" , "20" , "21" - as an example
"1/1/1-1:30:1" , "1" , "5" - as an example
Powershell or MSSQL 2008 queries work.
I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.
The table is for some performance stat collection app.
Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object.
Table #2 is the same collection but for a different server.
The layout in the database is terrible but this is what I get to work with.
View 3 Replies
View Related
Feb 4, 2007
Hi All
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
View 5 Replies
View Related
May 11, 2015
I need to generate a script where it should check the tables of a database from one server to another server(If server 1 database XYZ has 10 tables then server 2 database XYZshould match 10 tables). If the tables matches then truncate tables data and insert the data from one server 1 database XYZ to server 2 database XYZ. If tables does not matches, then it should check and create that tables which are not matching.
View 8 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Feb 6, 2007
my supervisor is trying to help move our existing database in IBM U2 into SQL Sever he has a file/table that as 12001 columns, 1 column is the key300 columns are different field errors that can be checked true or falseand for each of those 300 columns there are three extra columns where extra information can be stored is there a size limitation when it comes to columns in a SQL table?what is an efficient way of doing this?create one giant table with 12000 columns?create 300 tables, each table associated with a error association? which will then need to be joined?any suggestions? comments? tips?
View 3 Replies
View Related
Aug 6, 2015
When I run below query I get 3 columns, but when I try to add table name ind.object_name (object_id) it's giving me an error "Ambiguous column name 'object_id".How do I add tables name with 3 columns?
SELECT ind.name, ic.index_id , ind.type_desc from sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
View 4 Replies
View Related
Aug 2, 2007
Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
View 7 Replies
View Related
May 8, 2005
I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column.
This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30)
--Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0)
--Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
Can anyone help ?
Thanks
View 2 Replies
View Related
Sep 19, 2005
Hi All,I am having a problem for which i dont find any reasons ..I hope to get a solution from here.I have 2 tables ..1 with around 150 columns and the other with around 80 columns.I have view based on these tables.The problem i m facing from yesterday is that i cant add /delete/change on these tables.If i make a change on these tables and hit the save button the sql server enterprise just hangs..i also tried to add the columns through the query but no results.I cant even drop this view.Please any help on this??Thanks
View 1 Replies
View Related
Oct 26, 2005
Is there a way to change the collation against all the tables and columns within a database?
View 2 Replies
View Related
Nov 10, 2005
I drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?
View 2 Replies
View Related
Jun 14, 2006
Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!
View 2 Replies
View Related
Feb 5, 2008
How to search all columns of all tables for a keyword?:o
that in MS sql
thanks in advance..
View 2 Replies
View Related
May 5, 2012
I have several tables that I need to sum up a colomn and display the results. I have been struggling with this for some time. Here are my Tables and their columns:
Ingredients_tbl
IngredientID
Description
Inventory_tbl
IngredientID
AmountRemaining
Renconciled
Active
Order_Details_tbl
IngredientID
OrderAmount
Status
Order_Details_Details_tbl
IngredientID
OrderAmount
Status
I want to display all the ingredients from the ingredients table, sum the amountremaining of the items that are reconciled = 'false' from the inventory_tbl, sum the orderamount from both the order_details_tbl and Order_details_details_tbl for each ingredient based on status. Here is some sample data and results I am looking for:
Ingredients_tbl
IngredientID -- Description
1 -- Corn
2 -- Beer
3 -- SBM
4 -- Wine
Inventory_tbl
IngredientID -- AmountRemaining -- Reconciled
1 -- 500 -- False
1 -- 500 -- False
1 -- 100 -- True
1 -- 500 -- False
2 -- 1000 -- False
2 -- 1000 -- False
4 -- 500 -- False
4 -- 500 -- False
Order_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- MIXED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
4 -- 100 -- DELIVERED
Order_Details_Details_tbl
IngredientID -- OrderAmount -- Status
1 -- 100 -- ORDERED
1 -- 100 -- DELIVERED
1 -- 100 -- DELIVERED
2 -- 100 -- ORDERED
3 -- 100 -- ORDERED
3 -- 100 -- ORDERED
4 -- 100 -- ORDERED
I would like the results to be like this:
IngredientID
Description
Inventory = Sum of AmountRemaining of the records in Inventory_tbl where Renconciled = 'false'
Production1 = sum of OrderAmount of the records in the Order_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
Production2 = sum of OrderAmount of the records in the Order_Details_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
IngredientID -- Description -- Inventory -- Production1 -- Production2
1 -- Corn -- 1500 -- 200 -- 100
2 -- Beer -- 2000 -- 100 -- 100
3 -- SBM -- 0 -- 300 -- 200
4 -- Wine -- 1000 -- 100 -- 100
I thought this was going to be as easy as a few simple joins and aggregate sum on the colomns like this:
SELECT Ingredients_tbl.IngredientID, Ingredients_tbl.Description,
Ingredients_tbl.LowInventory, SUM(Inventory_tbl.AmountRemaining) AS Inventory, SUM(Order_Details_tbl.OrderAmount) AS Production1, SUM(Order_Details_Details_tbl.OrderAmount) AS Production2
FROM Ingredients_tbl
LEFT OUTER JOIN Inventory_tbl ON Inventory_tbl.IngredientID = Ingredients_tbl.IngredientID AND Inventory_tbl.Reconciled = 'False'
[Code] .....
But this obviously has its issues with incorrect returned sum values.
View 2 Replies
View Related
May 23, 2008
Hi,
I have 2 tables called Table A, Table B,
In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1
Now want a output like
Data1,
Data2,
Data3
Please help me to get this....
Thank you,
Senthil
View 4 Replies
View Related
Jul 26, 2013
I have requirement like this
1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns
'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column
View 5 Replies
View Related
