Problem With JOIN Statement To Remote Server

Apr 23, 2008

I'm trying to use the following JOIN to access tables on a remote server.

SELECT *
FROM mywebsite.myweb_db.dbo.[VOD] LEFT JOIN mywebsite.myweb_db.dbo.[VOD_Category]
ON mywebsite.myweb_db.dbo.[VOD].Category_VOD = mywebsite.myweb_db.dbo.[VOD_Category].IDX_VODC

If I do a select on one table to the remote server it works. (i.e. SELECT * FROM mywebsite.myweb_db.dbo.[VOD]

Once I do a JOIN I get the following error, does anyone know why? Thanks. :)

The number name mywebsite.myweb_db.dbo.VOD' contains more than the maximum number of prefixes. The maximum is 3.

View 2 Replies

SQL Server 2008 :: Select Queries With Join Sometimes Fail On Some Remote PCs

Jul 22, 2015

I have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.

I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.

There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.

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

SQL Server 2008 :: Optimizing Join With CASE Statement

Jul 20, 2015

I have data that I want at multiple granularities, 5,15,30 and 60 minutes. To reduce repetition, I have put them all in the same table, so that there is a column for 5,15,30 and 60 minutes, with a filtered index on each of the columns that removes the nulls. This means that each day will have 288 slots, but only 24 of the slots are filled in for 60 min data, and all of them are filled for 5 minute data.

I have another column that specifies the interval granularity, and my first thought was to access my data through a join, where I can use a CASE statement, and depending on the data granularity necessary, it will look at a different column:

INNER JOIN Data d ON
AND d.settlement_key =
CASE st.interval_granularity
WHEN 5 THEN [5_min_settlement_key]
WHEN 15 THEN [15_min_settlement_key]
WHEN 60 THEN [60_min_settlement_key]
ELSE NULL END

Despite the presence of the indexes on the columns, then the process seems to be quite slow, I think probably due to the fact that any query plan isn't going to know beforehand which of the columns it is going to use for any given dataset, until it actually starts to run, so it may not be optimised.

How I could optimise this based on the given structure? Maybe there are hints to be added to the join, or maybe I can clear the query plan each time the SQL is run? My other option for dealing with the data of different granularity was to use one column and repeat the data multiple times, each at the different granularity, but this makes my data, row and table sizes much higher, as we are adding just a column for each additional granularity. Would this work any better in future versions of SQL server, maybe with column store indexes?

View 5 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement

Jan 7, 2014

I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).

The structure of the periods table is as follows:

[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL

The stored procedure is currently:

USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] ....

What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.

For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23

So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.

View 1 Replies View Related

SQL Server 2012 :: Capture Statement From Missing Join Predicate Event?

Jan 27, 2015

After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.

The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.

I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run

select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169

it gets me the SQL being run at that time not the one that causing the event.

View 3 Replies View Related

Inner Join Using A Query On A The Same Remote Database

Jan 20, 2000

I have an sql 7.0 server. I need to run a query on a remote oracle server then take that output and inner join it with another table on the remote oracle server... then I need to put it a table in a database on my sql server... Anyone know how to do it? Help would be greatly appreciated...

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

Remote Access Server Configuration Option And Remote Query Timeout?

Jun 2, 2015

- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?

- what is the impact of changing the remote query timeout (on the same page) from default value?

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

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