Join Statement Required

Oct 23, 2006

I have the following tables
tblFavourites
FavouriteSince datetime
UserCodeOwner 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 DATA
10/14/2006 7:32:30 PM 4 7
10/16/2006 11:24:01 PM 4 5
10/16/2006 10:55:08 PM 5 4
tblUserData
UserID uniqueidentifier
UserName nvarchar(50)
UserCode int
aspnet_Users
UserID uniqueidentifier
LastActivityDate datetime
Now I need a join statement that selects the fields
aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSince
for all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4
Could someone provide me with the join statement because I dont get it anymore :)

View 9 Replies

Table Join, Advice Required

Jan 27, 2007

Hi,
I have the following two tables in MS SQL 2000
1.Products:
ProdID intProName char(10)
2.Orders:
OrderID intProdID intOrderDate DateTimeQuantity int
I want to join these two tables to form the following result format:
_________Prod1__Prod2__Prod3__Prod421/01/07__1______0_______1_____0___22/01/07__5______1_______2_____3___23/01/07__8______0_______1_____2___24/01/07__3______3_______4_____3___25/01/07__2______0_______1_____4___26/01/07__1______2_______6_____2___
So the first row would have the product name, Left hand column has the order date and then the sum of all the orders within
Any pointers on how to achive this would be great.

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

UPDATE Statement Syntax Help Required C# && SqlDataSource Control

May 3, 2007

Hi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser();
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text;
what next
I've not got a clue as to what to write next. There is a try / catch statement after this using :
rowsAffected = customerDataSource.Update();
which remains at 0 no matter what I try.
Any help greatly appreciated.

View 15 Replies View Related

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
EID   COLA   COLB
1   name   A
1   age   23
1   city   hyd
1   email   abc@live.in
1   mobile   45126
2   name   B
2   age   43

[code]....

how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies View Related

SQL Server 2012 :: Unable To Get Required Result Using Update Statement

Jun 18, 2014

I am unable to update the data using record by record below scenario.

Required output:

patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.

Current Output:

Calculation: From index_admission discharge date to next admit_visit date,

1) if the diff is having less than 30 days, readmission=1 and Index=0

else readmission=0 and Index=1 should be update.

For checking this every time should check using the latest index_admission discharge_date.

To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.

UPDATE Readmission
SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM
(SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission
WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub)
, FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),

[Code] ....

Expected Result:

View 5 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

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

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

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

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 View Related

SQL Join Statement Problem

Jul 20, 2005

Hi,I have the following SQL statement which is pulling a few details froma database. As you can see, there is only the one table from which iam creating a temporary copy.The reason I do this is because in the table i only have the 'standIn'listed by integer and i want to return the 'standIn' by name.I hope this is clear enough.The statement works but i am now noticing that it lists multiplereturns in SQL Analyser e.g it is listing three different rows for oneuser and these have all been past StandIns for the user in question.It is not a problem at the moment but it may be and i would like toknow why it is doing this. Can i change the statement to stop this, ihave been messing with the join part but no luck.Any help greatly appreciated.-----------------------------------------------------SELECT T2.FirstName AS StandIn_FirstName, T2.LastName ASStandIn_LastNameFROM tblStaff AS T1LEFT OUTER JOIN tblStaff AS T2ON T1.StaffNo = T2.StandInWHERE (T1.NTUserName = 'auser')

View 13 Replies View Related

Using The Same Table Twice In The Same Join Statement

Apr 8, 2008

I have a table which represents a machine, it has two transducer heads. I have set a table representing the different transducers which will fit on to each of the heads. I want to join the trans one index to the transducer table and then join transtwo to the transducer table also. I've tried twice and keep getting a sql error on executing.

Oh, as extra trouble I want to put this into a stored procedure.

Here is the monster which now works without both transducers:

declare @wo integer

set @wo = 90001

SELECT w_o_tbl.w_o_id, flaw_tbl.flaw_id, w_o_tbl.class, w_o_tbl.quanity, flaw_tbl.part_serial, job_tbl.number_passed, job_tbl.number_failed, job_tbl.operator,

job_tbl.Reject, job_tbl.db_transfer, w_o_addem_tbl.spc_notes1, w_o_addem_tbl.spc_notes2, w_o_addem_tbl.expedite, cust_tbl.cust_name,

flaw_tbl.flaw_gain, flaw_tbl.backwall, flaw_tbl.length, flaw_tbl.depth, flaw_tbl.tech_init, job_tbl.alarm_lvl, job_tbl.filter, job_tbl.noise_lvl,

part_no_tbl.part_no, part_no_tbl.part_name, technique_tbl.tech_no, instrument_tbl.serial_no, instrument_tbl.model_no, instrument_tbl.manuf,

trans_tbl_1.serial_no AS Expr1, trans_tbl_1.manufacture, trans_tbl_1.trans_freq, trans_tbl_1.trans_size, technique_tbl.transduc_id2,

technique_tbl.water_path, technique_tbl.scan_speed, technique_tbl.ref_std_id1, technique_tbl.ref_std_id2, technique_tbl.ref_std_id3,

technique_tbl.ref_std_id4, technique_tbl.pulser, technique_tbl.freq, technique_tbl.range, technique_tbl.delay, technique_tbl.velocity,

technique_tbl.aeras_not_accble, technique_tbl.accpt_reject, technique_tbl.couplant, technique_tbl.entrance_angle, technique_tbl.sync,

technique_tbl.scan_index, technique_tbl.insp_id8, technique_tbl.insp_id7, technique_tbl.insp_type_id1, technique_tbl.thickness,

technique_tbl.insp_id6, technique_tbl.insp_id5, technique_tbl.insp_id4, technique_tbl.insp_id3, technique_tbl.insp_id2, technique_tbl.insp_id1,

technique_tbl.tech_rev, prod_form_tbl.prod_form

FROM w_o_tbl INNER JOIN

flaw_tbl ON w_o_tbl.w_o_id = flaw_tbl.w_o_id INNER JOIN

cust_tbl ON w_o_tbl.cust_id = cust_tbl.cust_id INNER JOIN

part_no_tbl ON w_o_tbl.part_id = part_no_tbl.part_id INNER JOIN

job_tbl ON w_o_tbl.w_o_id = job_tbl.w_o_id INNER JOIN

w_o_addem_tbl ON w_o_tbl.w_o_id = w_o_addem_tbl.w_o_id INNER JOIN

technique_tbl ON w_o_tbl.tech_id = technique_tbl.tech_id INNER JOIN

instrument_tbl ON technique_tbl.instru_id = instrument_tbl.instru_id INNER JOIN

trans_tbl AS trans_tbl_1 ON technique_tbl.transduc_id1 = trans_tbl_1.transduc_id INNER JOIN

prod_form_tbl ON technique_tbl.prod_form_id = prod_form_tbl.prod_form_id

WHERE (w_o_tbl.w_o_id = @wo)

View 7 Replies View Related

Case In Inner Join? SQL Statement Help!!

Apr 24, 2007

Hi,

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

I would like to make a INNER JOIN statement and check if the telephone number dont have a starting '0', then append to it and try to do the join so I get both set of data.

for example (this doesnt work however...):

SELECT *

FROM dbo.Calls INNER JOIN
ON

CASE WHEN SUBSTRING(dbo.CallData.TelephoneNumber, 0, 1) = '0'

THEN dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber

ELSE '0' & dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber <--append a 0 at the start

END CASE

AND dbo.Products.FK_Client = dbo.CallDataSets.FK_Client
GROUP BY dbo.CallDataSets.FK_Client

I hope you understand what I am trying to achieve here...

Any help is appreciated!

Many thanks,

Jon

View 6 Replies View Related

How To Use Case Or If Statement In Inner Join ?

Sep 26, 2007

Hi All,

I am in need for to write the following query for a stored procedure in SQL Server 2000. Please observe the T-SQL code first.
Please disregard the numbers and Product Numbers, they are not the correct data. I used them for ease of understanding. But the query is identical.

Code Block

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
CASE WHEN @vchSubscription = 'Weekly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
AND C.dtInsertDate > = @dtIssueDate CASE WHEN @vchSubscription = 'Monthly' THEN

INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
AND C.dtInsertDate > = @dtIssueDate

END

GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

my requirement is join the same table, but depending on the subscription type i have to join to different product numbers.
I hope you understand. I have been trying this since yesterday, but no luck.

Any help would be greatly appreciated.

View 4 Replies View Related

How To Make This Join Statement In SQL

Apr 15, 2008

hi, so first of all, I am using SQL and VS C# 2005 express.
I have 4 tables as follows (I omitted some fields for simplification)

Address (street, city)

Seller (FirstName, LastName, AddressID)
Buyer (BuyerID, Pseudo, AddressID)
Transaction (OrderSerial, BuyerID, SellerID)

now, I want to make an sql statement in order to retrieve the following fields

Transaction.OrderSerial | Buyer.Psuedo | Buyer.AddressStreet | Buyer.AddressCity | Seller.LastName | Seller.AddressCity.

well you get the idea here. the problem I am having is that both seller and buyer are linked to the same table address. so I don't really how to do the join statement here. please help about this.

View 4 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

T-SQL (SS2K8) :: Join On Clause With IN Statement

Apr 25, 2014

I've come across a piece of code which i have never seen before.

ON T.CT_YEAR in
(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)

[Code] ....

The CT_Year column is simply C for current year L for last year, O for Other, N for Next.

The Function simply returns the year value.

View 4 Replies View Related