Table A 2 Column Join On One Column In Table B

Jul 19, 2007

hello everyone
i hope you could help me in this query.



I have two tables: tblA, and tblB.



tblA columns and data are as follows:


Prod Prodchid Req
T880A120 103P789 1
T881A121 275P246 2

tblB columns and data are sa follow

Iprod Idesc Level
T880A120 Assy-network 4
T881A121 Assy-IC 5
103P789 N.Resitor
275P246 IC

I want to join the data to display a table that has the following information:
Prod Idesc Prodchid Idesc Req Level
T880A120 Assy-network 103P789 N.Resitor 1 4
T881A121 Assy-IC 275P246 IC 2 5

How should I join the table?

View 3 Replies


ADVERTISEMENT

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

SP With Inner Join - Update Table Value (Single Column)

Oct 16, 2015

I'm trying to do the following:

update a tables value(a single column), based on a query of another database, using a common column.

Here's what I've cooked up so far:

Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no

[Code] ...

print @trackingNoThe error it's returning is:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table), then update P21 oe_hdr table with "trackingNo from Shipping_data

View 8 Replies View Related

Move Column Of Data To Another Table With A Join

Oct 12, 2013

I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.

I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.

This is what I have tried so far:

Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum

View 1 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

SQL Server 2008 :: How To Return 1 Particular Column From Another Table In Join

Apr 25, 2015

I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?

The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join

My photos table is constructed as follows:

CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,

[Code] ....

Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.

View 3 Replies View Related

T-SQL (SS2K8) :: Dynamic Join On EXECUTE Table Column

Jun 23, 2015

Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e

select * from (select table1.theSql,table1.userid from table1 ) as a

inner join (execute a.thesql) as b

on a.userId=b.userid

View 9 Replies View Related

Ambiguous Column Names In Multi-Table Join

Jun 21, 2006

Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S

View 6 Replies View Related

A Table/column To Table/column Data Check (was Help Please, SQL Something Simple)

Sep 15, 2006

Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

View 6 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm

Jun 4, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,





View 1 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.

Mar 7, 2008

Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
 

View 1 Replies View Related

Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

Nov 12, 2012

I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)

ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC

So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.

View 3 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

Cannot Insert The Value NULL Into Column SnapshotDataID, Table ReportServerTempDB.dbo.SessionData; Column Does Not Allow Nul

May 3, 2007

I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

Nested Table Key Column Is Not Bound To An Input Rowset Column

Jan 11, 2008

Hi!
I have a "little" problem with nested case model:



-- "normal" database:

DROP TABLE [unitInfo] ;

GO

CREATE TABLE unitInfo (

unitID INT PRIMARY KEY

, beginDate SMALLDATETIME

, area VARCHAR(10)

, partSize INT

, y2predict MONEY

) ;

go

INSERT INTO unitInfo

VALUES (1, '2007-02-01', 'home', 42, 10.0) ;

INSERT INTO unitInfo

VALUES (2, '2007-03-05', 'home', 43, 11.0) ;

INSERT INTO unitInfo

VALUES (3, '2007-02-02', 'office', 11, 11.4) ;

INSERT INTO unitInfo

VALUES (4, '2007-02-01', 'office', 10, 33.6) ;

INSERT INTO unitInfo

VALUES (5, '2007-02-01', 'office', 42, 44.1) ;



CREATE TABLE unitLog (

id INT IDENTITY(1, 1)

PRIMARY KEY

, logtime SMALLDATETIME

, -- combination of logtime/unitID is unique

unitID INT

, -- "FK" on unitInfo

m1 FLOAT

, m2 FLOAT

)





INSERT INTO [unitLog]

VALUES ('2007-01-01', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 2, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 3, 63.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 4, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-03', 1, 423.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-04', 1, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-05', 2, 43.0, 441.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 2, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 3, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-07', 1, 4.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 3.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-09', 2, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-10', 3, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 4, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 5, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-12', 2, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-13', 4, 413.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 4, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-20', 1, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-22', 1, 43.0, 414.0)



-- SSAS:

CREATE MINING STRUCTURE NestedStructure

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure

ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees

/* version 1*/

insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,

'Shape {select * FROM mydb.dbo.unitInfo }

Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }

Relate unitID to unitID ) as logdata ')

Parsing the query ...

OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.

Parsing complete

Where is the error?



/*version 2*/

CREATE MINING STRUCTURE NestedStructure1

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure1

ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees



insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }

Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }

Relate unitID to unitID ) as logdata



Parsing the query ...

Error (Data mining):

INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.

Parsing complete








Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.

What is wrong here...?

View 6 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related

Table Column Names = Dataset Column Values?!

Apr 28, 2008



I need to create the following table in reporting services



PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6





My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???


Thanks in advance!!!

View 1 Replies View Related

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 Replies View Related

Alter Table Add Column - How Do You Add A Column After Say The Second Column

Jul 11, 2007



When you use "Alter Table add Column", it adds the column to the end of the list of fields.



How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?



Create table T1 ( a varchar(20), b varchar(20), c varchar(20))



Alter table add column x varchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)



Can this be done programmatically?













View 33 Replies View Related

Populate GUID Column In Table B With Values From GUID Column In Table A

Mar 4, 2008


How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.

Does any one have a script to accomplish this task. thanks

View 4 Replies View Related

Alter A Column To Be The Table Identity Column

Aug 3, 2006

i have a table
table1
column1 int not null
column2 char not nul
column3 char

i want to script a change for table1 to alter column1 to be the table identity column. not primary.

View 5 Replies View Related

Insert Column Concatenated For Each Column In Another Table

Jun 13, 2012

I have a table where one column is an id card number (col1) and another is another column (col2). In col2 I have various values for each col1 ie col1 can have various values of col2. How can i insert col1 concatenated for each col1 in another table?

View 2 Replies View Related

Select Column Value Which Is Selected From A Column Of Another Table

Jan 2, 2004

i need help to get the value of a column which is selected from a column of another table....

View 3 Replies View Related

Adding Column To A Table Before An Existing Column

Mar 30, 2004

I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.

The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?

Thanks.

View 5 Replies View Related

FK On A Column To A Table Where The Column Is Not The PK But A Unique Index

Jul 23, 2005

Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You

View 2 Replies View Related

Copy Column Of Data Into Another Column In The Same Table

Jul 20, 2005

I have a column of digits I'd like to copy into another column in thesame table. How would I do this?Thanks,Bill

View 1 Replies View Related

How To Bind A Column To Identity Column Of The Different Table.

Apr 2, 2007



Hi,



I have two tables table1 and new_table



Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .



And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.



This type of functionality is requirement for my project.



I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;



Please help me out to fix this issue



Thanks

Purnima

View 3 Replies View Related

Data That In One Table Column And Not In The Other Table Column

Jun 24, 2007

dear all

i have 2 tables, lets say table A and Table B

both tables has column ID



i wonder how can i find records that appears in B.ID and not appear in A.ID



what is the SQL command in this case???

Thnks alot



View 1 Replies View Related

Add Calculated Field In Order Table Based On Price Column In Product Table

Nov 18, 2014

I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

View 17 Replies View Related







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