T-SQL (SS2K8) :: Join Clause With BETWEEN Statement
Aug 12, 2014
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–
I have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?
Example: Given a query: But let’s take a simple one here:
Query(1) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID Where ct1.zipcode = '14124' AND ct1.Name = 'Bob'
Query(2) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID AND ct1.zipcode = '14124' Where ct1.Name = 'Bob'
I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?
SELECTCASE WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name ELSE RTRIM([program_name]) END ProgramName , Val1.UqID , Val1.UqIDStr
I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.
I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.
I would like to know, whether the Query 1 is valid for deleting child records?
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff] FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId] WHERE [User].PracticeID = '55';
Query:2
DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
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
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
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.
Select memberfrom NameListInner join Memberson (Left(Namelist.NameID,5) = Members.IDOR (left(namelist.SSN,9) = Members.ssnOR (Left(namelist.CustID,9) + '*01' = Members.CustID)wherenamelist.name <> ''How do I speed up a process like this? Can I create indexes on themembers table based on a functionLike an index based on the left(members.id,5)or should these statements go into the where clause?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
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.
When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.
DECLARE @CutOffTid int SELECT @CutOffTid = isnull(max(ccas_id),0) FROMdbo.fbs_trans (NOLOCK) print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int SELECT @MinPeriod = Min(period) FROM agr.dbo.atrans WHERE agrtid >@CutOffTid
Insert into deltavalues values ('1002', 2000,5000) Insert into deltavalues values ('1003', 1000,0) Insert into deltavalues values ('1004', 500,0) Insert into deltavalues values ('1005', 1000,2000)
--I have below query , basically getting emnployee table balance between deltavalues table baseval column and highval column
select * from employee e join deltavalues d on e.planid = d.planid where e.balance between d.baseval and d.highval
but here what i am requesting you is how can i change the highvalue to not include if it is 0
for example above example plan 1003, 1004 has highbal as 0 so i want to write a query to get employee data between 1000 and anyvalue (> 1000 - no limit)
same way for 1004 between 500 and no end values (infinity)
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
I think I am getting 0 records returned... because.... I am joining the third value based on a zip code. The two tables if directly compared to each other would never have an = match. SELECT t2.company_name, t2.firstname, t2.lastname, modelname, configname, format, version, username, t2.zip,
t2.country FROM EtechModelRequests JOIN CC_Host.dbo.USR_SC as t2 ON Cast(t2.user_id As char) = username --JOIN --Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON PostalCode = zip WHERE RequestDateTime > CONVERT(DATETIME, '2007-09-1 00:00:00', 102) AND interfacename LIKE '%download%' AND
result=0 AND country='CA' --AND t3.PostalCode Like 'z1x%' ORDER BY company_name I was trying to do it by using a Where clause AND t3.PostalCode Like 'z1x%' that I will later turn into an Input Parameter after I get it working. Is there anyway to trim the PostalCode to the first three characters during the join process? Something like Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON LEFT(PostalCode, 3) = zip Not sure I got the LEFT function syntax correct even. Help appreciated.
I'm joining one table on to another table using one of 2 possibile fields (so table 1 key one can either match table 2 key 1 or key 2)... When the first key is null for a record, the script is to attempt to join using the second key instead. It is possible to have both values present, in which case the first one should be used.
I've taken a few runs at this so far:
... from table1 t1 left join table2 t2 on (t1.key1 = t2.key1 or t1.key1 = t2.key2)
If either t2.key1 or t2.key2 are populated, this works. Unfortunately, it's bringing back multiple records if both key1 and key2 are populated. Question # 1... Is there a different relational operator I can be using instead of OR that would logically look like 'if thie first key didn't find anything try the second instead'?
As an alternative, I've put the NVL to use...
NVL(t2.key1, t2.key2) = t1.key1
That seems to work, but it's pretty heavy on the server. Any suggestions on how else to handle this scenario would be greatly appreciated
view 1 I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.
view 2 notes field I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.
I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)
so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.
In layman's terms, what is the difference? Any examples? Thanks in advance.
I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes
SELECT SEP_ACCOUNT.sep_acct_cde as Account, FUND.fund_cde as FUND, BRKG_FACT.accum_unit_cnt as Units_Purchased, BRKG_FACT.transaction_amt as Amount_Purchased FROM BRKG_FACT SEP_ACCOUNT FUND where BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM brkg_fact.SEP_ACCT_ID_NUM = 5 and brkg_fact.product_cde <> 'MM' and brkg_fact.transaction_amt <= 0 and brkg_fact.source_sys_id_num <> 3 and brkg_fact.source_sys_id_num <> 5 and BRKG_FACT.trans_process_dte >= '1/1/2008' and BRKG_FACT.trans_process_dte <= '1/2/2008' order by fund_cde
current output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LCCS -0.18216 -5.31000
wanted output ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U BWDS NULL NULL U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GAFR NULL NULL U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LIGE NULL NULL U LCCS -0.18216 -5.31000
I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped. I need a to show the count of each events that have happened to it within a certain time period. This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table. What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count) Any thoughts? I'm a complete newbie at this by the way.
Thanks
Code Snippet
SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit FROM DeviceEventDurationLog RIGHT OUTER JOIN DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate) GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit ORDER BY DeviceStatusWords.DeviceName
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause? My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined.
Filtering in where clause....
Code:
select... FromtProject p with (noLock) jointProjectCall pc with (noLock) on P.ID = pc.project_id jointStore S with (noLock) on pc.store_id = s.id jointZip Z with (noLock) on Z.zip5 = s.zip5 jointManager M on M.ID = case ... end leftjoin ( selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed fromtCall C whereAnswer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum wherepc.removed = 0 andp.cancelled = 0 andp.deleted = 0 ands.closed = 0 ands.deleted = 0 andyear(getDate()) between year(P.startDate) and year(P.expDate)
Filtering in joins...
Code:
select... FromtProject p with (noLock) jointProjectCall pc with (noLock) on P.ID = pc.project_id and pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) jointStore S with (noLock) on pc.store_id = s.id jointZip Z with (noLock) on Z.zip5 = s.zip5 and s.closed = 0 and s.deleted = 0 jointManager M on M.ID = case ... end leftjoin ( selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed fromtCall C whereAnswer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum
Having problems rewriting my join condition using the "inner join" syntax.
My query, working with an intersection table:
SELECT Description, EmailAddress FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur WHERE r.RoleID = ur.RoleID AND u.UserID = ur.UserID
This works fine, but i want to write it using 'inner join' style, so I tried:
SELECT Description, EmailAddress FROM Accounts_Roles r, Accounts_Users u INNER JOIN Accounts_UserRoles ur ON r.RoleID = ur.RoleID AND u.UserID = ur.UserID
which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.)
Any ideas as to how I'm screwing this up would be appreciated.
Table Master Table Dimension ID Code Price ID Name 1 A44333 5000 1 "Scanner" 2 D442 3000 2 "Notebook" 3 D6644 4000 3 "Banana"
I join both tables on ID and search one time for ID and another time for Name. Looks like
(a) SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id WHERE master.id=1 AND Code like 'A44' (b) SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id WHERE Name = 'Scanner' AND Code like 'A44'
Why does query (b) take longer than query (a)? Dimension has 12 Rows and Master has about 24M Rows.
For index I did Create Index IX_Master_ID on Master(ID) Create Index IX_Master_Code on Master(Code) Create Index IX_Dimension_ID on Dimension(ID) Create Index IX_Dimension_Name on Dimension(Name)
I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out?
I have a SQL query where am using WHERE clause as a result of which the NULL values are getting filtered...can u please help me to tranform this query into LEFT OUTER JOINS so as to avoid this filtration....my query is
SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE FROM EALPS_DRWREVSPLJTS
A,EALPS_DRW_REV_SPL B,EALPS_DRW_REV_LINE C,EALPS_WELDTESTCRIT D,EALPS_ACTV_SEQ E WHERE B.SPOOL_ID=A.SPOOL_ID AND
B.LINE_ID=C.LINE_ID AND D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID AND E.ACTIVITY_CODE='VT' AND E.JOINT_ID=A.JOINT_ID
