Update Statement With Join

Mar 12, 2008

Hey guys,
Up to this point I've been dealing with mostly select statements but time has come, and I need to do an update. Basically I'm not sure how to structure the query.

I'd like to update the field "new_applicationreceived" to the value of "new_lastcontact" based off the results of the following select query:

select new_lastcontact from lead
LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue
AND StringMap.AttributeName = 'SalesStageCode'
AND StringMap.ObjectTypeCode = 4
where new_applicationreceived is null
and lead.salesstagecode = 5

I'd really appreciate your help - I'm very worried about messing up the data and don't want to screw something up.

View 2 Replies

Help With Inner Join In Update Statement

Aug 27, 2007

Here is my update statements which doesn't work, can you show me an example or provide a hint.

thanks

update property
inner join propinv on propinvid=propertyinvid
set property.lotsize='100'
where property.lotsize <> '' and property.lotize is not null

Thank you

View 9 Replies View Related

Join In An Update Statement

Sep 1, 2006

I have a join between two tables. I need to updated column a from table a with column a from table b. How do I do that with a set statement?

View 5 Replies View Related

Can We Have An Inner Join Clause In An Update Statement

Aug 14, 2001

Hi,
I'm trying to inner join an update statement.
Something like this:

update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point
where #point_connection_temp.next_point is null
order by a.key_fld

I'm getting an error message:Incorrect syntax error near AS
Any help will be greatly appreciated.Thank you!!!!!!!!!1

View 1 Replies View Related

One Statement Update - Join, No Cursor ?

Jul 20, 2005

HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!

View 6 Replies View Related

UPDATE Statement With A JOIN Condition

Jun 30, 2006

Hi all,

HERE IS MY UPDATE STATEMENT:

Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM

WACTS_Inventory_Part_Loc_Tbl WIPLT

INNER JOIN

Temp_MatIn_Data tmp

ON

WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID

WHERE

WIPLT.WIPLT_Location_Code='MF'

I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.

The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.

Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.

I woould appreciate any help on this.

Thanks,

Jothi

View 4 Replies View Related

Compare Two Table And Update - Join Statement

Apr 27, 2015

I compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?

View 2 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Where Statement Compared With Join Statement

Jan 11, 2006

for complex views should I use "where" statements or "joins" in terms of performance?

Which one is faster?

View 6 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View 1 Replies View Related

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related

Stored Procedure - Update Statement Does Not Seem To Update Straight Away

Jul 30, 2007

Hello,

I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.

I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?

View 6 Replies View Related

Help On Join Statement

Jan 10, 2007

I have two tables:
tblUserDataUserNameUserCode
tblBlogsUserCodeBlogText
I have an SP which takes the username as a variable.
How can I select all blogtext from tblBlogs where the usercode belonging to the username in tblUserdata is equal to the usercode in tblBlogs?
so select all blogs for a specfic username...

View 1 Replies View Related

Help On Join Statement

Feb 17, 2007

I have table A:ID intName textAnd Table BID intName text Now, I want to select all records from A where there is no matching record in B based on the IDI want to do this with a JOIN statement and not a subquery as I understood that the execution plan for JOIN statements is more efficient...Any help?

View 2 Replies View Related

How To Join Sql Statement

Mar 14, 2007

Hi all,
I had some sql statement query and would require your help.
I would like to query data from table A and table B and would like to join the data and return as a result.
Take for example, Query A: select timestart, timeend from TableA where product = 'A'
Query B : select timestart, timeend from TableB where product = 'B'
How can I join QueryA and QueryB and return as a single result?
Thanks

View 1 Replies View Related

Use If Else In A Join Statement

Mar 3, 2008

Hi Everybody,
I'm trying to join two tables on the condition that field "A" on Table 1 = field "B" on table 2 but the problem is field "A" on table 1 and Field "B" on table 2 can have null values in which case I want to use field "C" on table 1 = field "D" on table 2. To accomplish this, I need to use if then statement inside the join statement. I tried using it, but its giving error saying there is an error near "if" and near "inner join" statement. How can this be accomplished ? any help is appriciated.
devmetz

View 6 Replies View Related

JOIN Statement 'Help'

Oct 12, 2004

I am very new to SQL and need to create a statement that will JOIN data from 3 tables into my datagrid. The following are the tables:
Table A: Compliance
- FinancialsID
-NetWorth
-DebtRatio
-WorkCapital

Table B: Financials
- FinancialsID
- cAssets
- TransDate
- CustomerID

Table C: CompanyInfo
- CustomerID
- Company
- Agent

I need to be able to display Company.CompanyInfo, NetWorth.Compliance, DebtRatio.Compliance, WorkCapital.Compliance in a datagrid and make sure that it ONLY displays the most current entry for the Company.

The Compliance table has a relationship to the Financials table through the FinancialsID field and the Financials table is related to the CompanyInfo table through the CustomerID field. The TransDate is a date field in the Financials table.

This seems extremely confusing to me, but I am sure its easier than what I am trying to make it.

Any help would be GREATLY appreciated.

Thanks
Garrett

View 6 Replies View Related

SQL Statement - INNER JOIN

Jun 9, 2004

I have two tables:
Employees[ID, FirstName, LastName]
DLSUs[ID, Title, HeadID, DeputyHeadID, Link]

I would like to be able to display
(* DLSUs.Title (add its corresponding link)),
(Employees.FirstName, Employees.LastName WHERE Emplooyees.ID = DLSUs.HeadID)
(Employees.FirstName, Employees.LastName WHERE Emplooyees.ID = DLSUs.DeputyHeadID)

The problem with this is that I'm trying to fill a table as it is built, using recordsets and "do loop". In other words I want to have all DLSUs to have there corresponding Title(with Link), Head and Deputy Head.

This is what I have so far, it only prints out the first DLSU Title(with Link) with its Firstname, LastName. Should I be using INNER JOIN, I don't fully understand the principle of it.
sqlStmt = "SELECT DLSUs.*, Employees.* FROM DLSUs, Employees WHERE DLSUs.DLSUType = 'LSU' AND Employees.ID = DLSUs.HeadID"

< ...
__do until objRS.EOF
____Response.Write "<tr><td width=""320"">"
____If NOT objRS("Link") = "" Then
______Response.Write "<a href=" & objRS("Link") & ">" & objRS("Title_EN") & "</a>"
____Else
______Response.Write objRS("Title_EN")
____End If
____Response.Write "</td>"
____Response.Write objRS("FirstName")
____Response.Write "</td></tr>"
____objRS.MoveNext
__loop
__objRS.close()
... >

Thanks in advance for your help

Gazzou

View 2 Replies View Related

Help With JOIN Statement

Apr 24, 2008

Hi, I'm doing a short lab assignment for a college SQL Server course, and I'm a little stuck up on one of the SELECT statements. Here's the questions: Create a SELECT statement that will return a list of all your employees, their project and the hours they have logged.

Here's the SQL Code:

CREATE TABLE project_employee (
employee_id INT,
employee_name VARCHAR(40),
employee_hire_date DATETIME,
employee_termination_date DATETIME,
employee_billing_rate INT,
CONSTRAINT project_employee_pk
PRIMARY KEY (employee_id)
);

CREATE TABLE project (
project_id INT,
project_name VARCHAR(40),
project_budget INT,
CONSTRAINT project_pk
PRIMARY KEY (project_id)
);

CREATE TABLE project_hours (
project_id INT,
employee_id INT,
time_log_date DATETIME,
hours_logged INT,
dollars_charged INT,
CONSTRAINT project_hours_pk
PRIMARY KEY (project_id, employee_id, time_log_date),
CONSTRAINT proj_hours_fkto_projectemployee
FOREIGN KEY (employee_id) REFERENCES project_employee,
CONSTRAINT proj_hours_fkto_project
FOREIGN KEY (project_id) REFERENCES project
);

INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1002, 'Year 2000 Fixes',999998000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1005, 'TCP/IP Implementation',415000);

INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);

INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-JAN-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-JAN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-JAN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-FEB-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-MAR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-MAR-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-APR-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-APR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-MAY-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-MAY-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-JUN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-JUN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-JUL-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-JUL-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-AUG-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-SEP-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-SEP-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-SEP-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-OCT-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-NOV-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-NOV-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-DEC-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-DEC-1998',3.00,507.00);

And here's the SELECT statement that I've wrote so far, but I'm not sure how to get the JOIN working correctly. Tips?

SELECT
EMPLOYEE_NAME AS 'Employee''s Name', PROJECT_NAME AS 'Project Name', HOURS_LOGGED AS 'Hours Logged'
FROM
PROJECT_EMPLOYEE, PROJECT, PROJECT_HOURS
WHERE
PROJECT_HOURS.PROJECT_ID = PROJECT.PROJECT_ID AND PROJECT_EMPLOYEE.EMPLOYEE_ID = PROJECT_HOURS.EMPLOYEE_ID;

View 5 Replies View Related

Help With SQL Join Statement

Jul 22, 2007

View 2 Replies View Related

Join Statement

Oct 12, 2005

Hi.........Its been awhile since I've touched SQL statements, so I need some helpwith writing a JOIN statement to query 3 tables.The dB has 3 tables with valuesApplications-Application_code(Primary key)-Application_nameApplications_Installed-Computer_name(Pri key)-Application_code(Foreign key/sec key)Workstation_info-Computer_name(Pri key)-Serial_numberWhat I want to do is query the tables for a particular Application codeand name from Applications, so that it returns the values of computernames with the matching values from Applications_installed andWorkstation_info.So I need to do aSelect * from applications where applications.application_code='XXX'join (this is the part I'm stuck how do I tell it to match theapplications.application_code =applications_installed.application_code) then match the computer namesfrom Applications_installed.computer_name with that ofworkstation_info.computer_nameNot sure if I'm explaining this properly....Can anyone help.......

View 5 Replies View Related

Join Statement Required

Oct 23, 2006

I have the following tablestblFavouritesFavouriteSince datetimeUserCodeOwner int (the usercode of the user whose favouritelist this is)UserCodeFavourite int (the usercode of the user who has been added to the favouritelist of usercodeowner)EXAMPLE DATA10/14/2006 7:32:30 PM 4 710/16/2006 11:24:01 PM 4 510/16/2006 10:55:08 PM 5 4tblUserDataUserID uniqueidentifierUserName nvarchar(50)UserCode intaspnet_UsersUserID uniqueidentifierLastActivityDate datetimeNow I need a join statement that selects the fields aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSincefor all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4Could someone provide me with the join statement because I dont get it anymore :)

View 9 Replies View Related

Select Statement With Join?

Apr 26, 2006

Hi all. I'm selecting all customers and trying to count alll the orders where at least one item has the itemstatus of "SHIPPED" on their order. Each customer will have only one order. I'm trying to see if I can do this in one query. Is it possible?? Is it something like below?

SELECT customers.id,COUNT( orders.id) AS 'total',
from customers
LEFT JOIN orders ON customers.id=orders.id AND orders.itemstatus="SHIPPED"

View 2 Replies View Related

Delete Statement Using A Join Plz Help

Mar 2, 2005

well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another
database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113'

PLZ help

View 1 Replies View Related

CASE Statement Within Join

Oct 18, 2005

Is it possible to have a join with case statement in it?

i.e.

select * from a inner join b on
case [x] then a.xid = b.xid
case [y] then a.yid = b.yid?

View 3 Replies View Related

CASE Statement In A JOIN

Oct 16, 2007

Hi, I have two tables TABLE_A and TABLE_BTABLE_A has rows like this:PROJECT_IDTASK_ID TASK_NAME1 100 One Hundred1 110 One Hundred Ten1 120 One Hundred Twenty2 200 Two Hundred3 300 Three Hundred3 310 Three Hundred TenTABLE_B has rows like this:PROJECT_IDTASK_IDAMOUNT1 10010001 11011002 NULL20003 3003000I want to inner join TABLE_A and TABLE_B such that if TASK_ID is available in TABLE_B, then join should happen on TASK_ID (on TABLE_A.TASK_ID=TABLE_B.TASK_ID), if TASK_ID is not available the join should happen on PROJECT_ID.For example for PROJECT_ID=2, there is no TASK_ID in TABLE_B (in this situation the join should be ON PROJECT_ID)How can we do a CASE like situation here?Thanks in advanceqA

View 14 Replies View Related

Probs With INNER JOIN Statement

Apr 19, 2004

Hi folks,

I got a strange Problem with this statement:

select * from [db1].[dbo].[table1] AS db1
INNER JOIN [db2].[dbo].[table1] AS db2
ON (db1.Text = db2.Text)

Text fields are both nvarchar(50)

I think this should work, but it doesnt?
I got a SQL Server Error 446

I know, text compares are not that good, but it exists no other way....

Thanks for any advice!!!

View 10 Replies View Related

Syntax To Join With And Statement

Jan 17, 2014

I inherited a query and I am getting an error of Unsupported literal in join in the INNER JOIN FRDM.dbo.MEMBER_SUBSC FRDM_dbo_MEMBER_SUBSC2

ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_CK = FRDM_dbo_MEMBER_SUBSC2.SBSB_CK AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX
= '00')statement. Specifically the AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX
= '00')part.
SELECT frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.GRGR_ID AS 'group number'

[code]....

View 1 Replies View Related

Sum Commission In A Join Statement

Oct 28, 2014

I have two Tables: Employees (id, first name, last name..)

And Sales(id, value, ..., commission, idsalesperson)

One employee is making many sales and i want to add what commission did he took from his sales. Basically i want to see like this:

Full name(first name + "" + last name)as FullName SUM(Commission)

Can I also sort by year in the same statement the commission he took? The commission value is a calculated field based upon the value of the transaction.

View 3 Replies View Related