I have two tables:
tblUserData
UserName
UserCode
tblBlogs
UserCode
BlogText
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...
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.
for complex views should I use "where" statements or "joins" in terms of performance?
Which one is faster?
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 RelatedHi 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
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
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
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
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;
I have 3 tables, a Store_Tbl for stores,
a Store_Mall_Tbl to associate stores in malls
and a Store_ATM_tbl to associate ATM types in
stores.
Store_Tbl
--------------
StoreID|StoreName
-----------------
1|GAP
2|Banana Republic
3|Broadway
4|May Company
5|Sears
Store_Mall_Tbl
--------------------
StoreID|MallID
-----------------
1|1
2|1
3|1
4|1
5|1
Store_ATM_Tbl
------------------
StoreID|ATMID
------------------
1|1
2|1
3|1
How can I run a query w/ appropriate outer joins to fetch
StoreName and ATM availability given an ATMID and a MallID
Ex:
Input: ATMID=1, MallID=1
Output:
StoreName|ATM
---------------
GAP|YES
Banana Republic|YES
Broadway|YES
MayCompany|NO
Sears|NO
Please Help!
Boybles
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 RelatedI 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 RelatedHi 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"
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
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?
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 RelatedHi 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!!!
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]....
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.
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
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.
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 RelatedI 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)
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
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.
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.
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 RelatedHi,
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
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.
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
[Code] .....
I am trying to use this logic into a query:
Select P.S,E.S,E.R
from Pack P(nolock)
join Exp E on P.Id=E.O
on E.R is null
case when E.R is not null then ''
else ''
end
where P.s='PLT000044'
I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.
I'm very new to SQL and have been assigned to modify an existing Store Procedure. The existing is pasted below:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE RPT_ACTIVITY_LEDGER
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS
SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4'
FROM ACTIVITIES
WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*************
The goal is to add another table called EMPLOYEES with fields E1 and E2 as part of the returned data set. I need a join statement but I can't figure out the syntax that will actually pull the data. Any help would be appreciated. Thanks.
The following sql statement is rejected by asp.net (working with sqlserver). It says something is wrong near "TableFlights". But I don'tsee anything wrong with it.Any help is appreciated:SELECT TableFlights.IdFlight, Concat = (TableFlights.OrganizerLastName+ ', ' + TableFlights.OrganizerFirstName + ' Flt:' +TableFlights.FlightNumber + ' on ' +Convert(char(10,TableFlights.FlightDate,101)) FROM TableFlights INNERJOIN TablePassengers ON TableFlights.IdFlight=TablePassengers.IdFlightWhere TablePassengers.Email = 'marvin@micro-net.com' Order ByTableFlights.OrganizerLastName, TableFlights.OrganizerFirstName-- Marvin
View 1 Replies View Related