Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:





GroupId


CompanyId


UserId





I need to query the users getting the company's and group's names, but I only know how to join one table. Example:





Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name


From Users JOIN Groups ON Users.GroupId = Groups.Id





Hon can I add the companies table in the Join ?





Thanks,


Moshe

View 2 Replies


ADVERTISEMENT

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

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.

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

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.

View 1 Replies View Related

SQL Inner Join Clause And The Where Clause

Jan 21, 2008

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 ?

Thanks

View 6 Replies View Related

Where Clause With LIKE 'xxx%' But There Is A JOIN..

Sep 21, 2007

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.

View 1 Replies View Related

Or In A Join Clause

Apr 22, 2008

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


(and ya, I didn't design this datamodel).

View 6 Replies View Related

JOIN Outside Of WHERE Clause ? ? ? ?

Aug 15, 2007

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)

eg.

VIEW 1
row1 row2 row3 note_row (from view 2)


fsdfs sdfsdf sdfsdf <no note>
sdfs sdfsd sdfsd "note"
sdfsdf sdfsdf ssdfsd <no note>


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?

THANKS!

View 4 Replies View Related

JOIN With ORDER BY Clause?

Dec 1, 2005

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?

View 2 Replies View Related

Differ Bet WHERE Clause && INNER JOIN?

Feb 26, 2004

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.

ddave

View 6 Replies View Related

Help Using An Outer Join When Using A Where Clause

Mar 7, 2008

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

View 2 Replies View Related

Right Outer Join With Where Clause

Aug 23, 2007

Using SQL Server 2005

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

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

Any Benefit From Filtering In Join Vs. The Where Clause?

Jul 29, 2005

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

View 1 Replies View Related

ANSI-92 Inner Join Vs. Where Clause Syntax

Jan 11, 2005

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.

Thanks,
Gordon Z

View 3 Replies View Related

Index / Join / Where Clause Very Slow

Jul 15, 2007

Hello,

first of all, some facts of the case:



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?

Thank you

Silas

View 9 Replies View Related

Left Join With Where Clause Does Not Work

May 25, 2012

I have tables A and B.

Table A has data, but B may have or not.

I have to get -- at least! -- the A table data.

It works when I dont have the where clause:

select * from A left outer join B on A.IDUser = B.IDUser

But it doesn't when I try to use the WHERE clause:

select * from A left outer join B on A.IDUser = B.IDUser
where A.IDUser = 2

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

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–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]

[Code] .....

View 2 Replies View Related

CONVERT WHERE CLAUSE TO OUTER JOIN

May 28, 2008

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



Thanks and Regards,
parama.

parama laha

View 6 Replies View Related

Adding Conditions In The ON Clause Of A JOIN

Feb 12, 2008

Hi Faculties,I have two queries which give me the same output.-- Query 1SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.idWHERE cat.id = 1;-- Query 2SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.id AND cat.id = 1;The first query uses the WHERE clause and the second one has all theconditions in the ON clause. Is there anthing wrong with the secondapproach in terms of performance? Please suggest.Thanks in advanceJackal

View 6 Replies View Related

Filtering Records Through Join Or Where Clause

Apr 22, 2008

Hi All,
Can anybody tell me which of the following is the most efficient query if i have huge tables.


SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1 AND Tab1.Col1 = 5


OR



SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1WHERE Tab1.Col1 = 5

As long as i explored this, Sql Server Query Execution Plan shows the similar cost for both cases. Is there any difference?
If yes why?

Thanks in advance.

Regards,

Sulaman Riaz

View 4 Replies View Related

SQL Query Help - Right Outer Join With Where Clause

Aug 23, 2007

Using SQL Server Reporting services 2005

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 table 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

View 7 Replies View Related

Select Inner Join With Where Clause Problems

Jan 3, 2007

Hello All,

I have a question about a Select over 2 Tables,
with the Following Scenario (Not all Products (ARTICULOS) haves CARAC's on the CFG_CARAC_ARTICULOS table):

Picture of the tables here:
http://www.pci-baleares.com/pantallazoSql.jpg



We have per example 7 Slots (Motherboard, CPU, VGA Card, RAM, TOWER, etc...)
When we fill the Slot with a CPU-> Then we open the Slot for VGA CARD, we do the Followin Select:

SELECT dbo.ARTICULOS.*
FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN
dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO

Ok it brings up ALL Graphic Cards because they dont depends on CPU

Now we go to the Motherboard Slot
And we make the following Select to obtain the compatible Motherboards:
SELECT dbo.ARTICULOS.*

FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN

dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO

WHERE

((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) AND
((ID_CARAC = 1) AND (VALOR = '775'))

We check the motherboards if they support PCI-E (because we selected a Graphic card of that, and SOCKET 775 because the CPU)

But SQL return 0 Rows, if we do the following Select:
SELECT dbo.ARTICULOS.*


FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN


dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO


WHERE


((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E'))

OR

SELECT dbo.ARTICULOS.*


FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN


dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO


WHERE

((ID_CARAC = 1) AND (VALOR = '775'))

It return Rows, it happens just if the Where clause haves more as 1 specifications...

Any solution for it? It drives me crazy :D

Thanks and regards
Marc Hägele

View 3 Replies View Related

LEFT OUTER JOIN & WHERE Clause Issue

May 5, 2008

Hello,

I have two tables:

tasks
idint
useridint
otherstuff int

sample rows:
1,11,2
2,11,2

times
idint
taskidint
startweekdatetime
statusint

sample rows:
1,1,'04/28/2008',1
2,1,'04/28/2008',1
2,2,'04/28/2008',1


I want to retrieve:
ALL [tasks] with [times].[status] for a specific user for a given week, whether rows exist in [times] or not.

Here's my query:

select tasks.id, times.status
from tasks
LEFT OUTER JOIN times ON tasks.id = times.taskid
where upt.userid=11 AND times.startweek='05/05/2008'

That just doesn't return any the records though for the 05/05/2008 date. Its as though I want to join with a WHERE clause on the [times] table. What can I do?

Thanks in advance,


Brian

View 2 Replies View Related

Two Tables With Left Outer Join && Where Clause

Oct 29, 2007

Hello,I'm trying to link two tables... one for Employees and the other forTimecardsI need to get a list of employees that do not have timecards on anSPECIFIC DATEI tried the follonwingSELECT Employess.EmployeeIDFROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =Timecards.lmpEmployeeIDWHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'But it doesn't work. However, when I comment the date condition out(lmpTimecardDate = '10/24/2007') it works all right but It's not whatI needAnother interesting point... if I use the following query... it worksall rightSELECT Employess.EmployeeIDFROM EmployeesWHERE Employees.EmployeeID not in (select Timecards.EmployeeID fromTimecardswhere TimecardDate = '10/24/2007')I'd like to be able to use the Left Outer Join option.... Am I doingsomething wrong?... or is it that if It doesn't like the condition I'musgin in the WHERE clause (TimecardDate = '10/24/2007')Thanks for your helpPablo

View 3 Replies View Related

Showing Null Values From ON Inner Join Sproc CLAUSE

Jul 19, 2006

Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A
What I did try:
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help

View 1 Replies View Related

Speed Cost For Using Or Clause And Functions On Join Statement

Jul 23, 2005

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?

View 2 Replies View Related

Filter Criteria - Temp Table Join Or Where Clause?

Jul 20, 2005

I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G

View 4 Replies View Related

JOIN Multiple Tables From Multiple Databases

May 23, 2008

Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database.  My question is can you JOIN multiple tables in an SQL Statement from multiple databases.  Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables.  Can I link say tblRegister from Convention to tblUser in Services?
Thanks

View 3 Replies View Related

Best Practice: Use Values In WHERE Clause Or Create And Join Temp Table ?

Dec 28, 2006

Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
 My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
 Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
 Any help gratefully appreciated.
Thanks.
John

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Unique Identifier From String To Use In A Join / Where Clause

Jan 6, 2015

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

[Code] ......

View 6 Replies View Related

SQL 2012 :: Include Columns In Index That Are In Where Clause / Select List And Join

Jun 2, 2014

Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.

View 4 Replies View Related

Multiple Where Clause?

Jun 11, 2007

where exigo_data_sync.orderdetail.itemcode in (B1001, B1001B, B1007, B1007B, B1008, B1008B, B1000, B1000B, B1006, B1006B, B1009, B1009B)

I keep getting a ADO error stating invalid column names...these are not column names they are the data that i want to use in the where clause. What am I doing wrong?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved