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 values
parsed from a comma delimeted string.

For example:

CREATE FUNCTION [dbo].[udf_filter_patient]
(
@patient_list varchar(2000)
)
RETURNS @patient TABLE
(
patient_id int
)
AS
BEGIN
insert into @patient
select patient_id from patient
-- parse @patient_list stuff excluded
RETURN
END

I have come up with the following two schemes to use these udfs. These
examples are obviously simplified, and I have a handful of stored procedures
that will use between 10 or more of these filters. If the two are
equivalent, I prefer Method 2 because it makes for much neater SQL when
using many filter criteria.

So my question is, will one noticebly outperform the other? Or is there a
better way in which to filter on a list of criteria?

Method 1 :

CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS

CREATE TABLE #patient
(
patient_id int
)

INSERT INTO #patient
SELECT
patient_id
FROM
dbo.udf_filter_patient( @patient_list )

SELECT
*
FROM
open_bills
INNER JOIN #patient on #patient.patient_id = open_bills.patient_id

GO


Method 2 :

CREATE PROC sp__filter_open_bills
@patient_list varchar(2000)
AS

SELECT
*
FROM
open_bills
WHERE
open_bills.patient_id IN ( SELECT patient_id FROM
dbo.udf_filter_patient( @patient_list ) )

GO



Thanks for the help!

Chris G

View 4 Replies


ADVERTISEMENT

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

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

Best Practice Question: JOIN Criteria Vs. WHERE Criteria

May 24, 2004

For example, consider the following queries:


DECLARE @SomeParam INT
SET @SomeParam = 44

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam


Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?

Thanks.

View 3 Replies View Related

Filter Same Group By Different Criteria

Mar 7, 2008

In my report I have
group1 grouped by order, under that is
group2 grouped by unit, under that is
group3 grouped by cost_type, under that is
details

In group2 general expression 1st line is =unit. The 2nd line is =iif(cost_type=Serial, 2, 1)

this works fine(for now). If a unit has cost type of Serial I get two groups of that unit.

The filter expression of group3 is =Fields!cost_type.Value. The Operator is != and the value is Serial.

This works great. for the first grouping of group2.

on the second grouping of group2 there are no values in the columns. I want the second group to be filtered so only the Serial values show.

What am I missing?

Also I have the details visibilty set as hidden=True and ToggleItem=cost_type.

So the second grouping of group 2 can be expanded to show the details.

View 9 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?

View 12 Replies View Related

WHERE Clause Not Picking Up 2nd Field Criteria

Jun 20, 2001

Hello,

I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:

CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int)
AS

SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item]
FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#]
WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM"
GO

TIA,
Bruce Wexler

View 2 Replies View Related

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

Where Clause Filter

Apr 14, 2008

I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:

Select
DisplayColumns...
From
InterestingTables
Where
(Filter1) --W1
AND --W2
NOT --W3
(Filter2) --W4

Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?).
Now, say SetF1ExF2 is the result set of the sql script above.
Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3.
Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2
I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.

TIA.

View 1 Replies View Related

Additional Criteria In LEFT JOIN

Jan 18, 2008

I have a stored procedure which is used to search records in the database based on the data sent from the web page. There are several search fields and all of them are in one table (Table1) except the "CallerName" field which is in a different table (Table2). Since I had to show CallerName also in the gridview apart from other columns, I did a LEFT JOIN (using field CallerNumber) to show all the required fields from Table1 and CallerName from Table2.
Now heres the problem. Since CallerName is a search criteria, its supposed to be in the WHERE clause after the JOIN or in the JOIN clause itself. The problem is, if I put it in WHERE clause, the result set doesn't show records from Table1 which do not have a matching CallerNumber in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber
WHERE T1.CallerNumber = 'some number' AND T2.CallerName = 'some name' If I put it in the JOIN condition, it works just like a LEFT JOIN is supposed to work, showing all the records in Table1 and also those which had CallerName in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber AND T2.CallerName = 'some name'
WHERE T1.CallerNumber = 'some number'

1st SQL won't work for me because it doesn't show all the records in Table1, even when no search criteria is specified.2nd SQL won't work for me because it shows more than required when just CallerName is sent from the web page as search criteria. It will show all the records where CallerName is "some name" and also all the additional records (since it is a left join).
Can I get the goodness of both in one or do I have to create two separate Stored Procedures?
Thanks all,Bullpit
 

View 11 Replies View Related

Join Tables On Multiple Criteria

Oct 14, 2013

I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.

Here is table a:

naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0

[Code] ....

and here is table b:

naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0

[Code] ....

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]

[Code] ....

Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like

* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)

View 1 Replies View Related

Automatic Select Filter (something Like Global Table Filter)

Apr 15, 2008

Hello,

Here is my problem:


I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?


Thanks in advance.

Best regards,
Beroetz

View 5 Replies View Related

Why Is Left Table In LEFT JOIN Limited By Where Clause On Right Table

Jan 25, 2015

-- Why is the left table in a LEFT JOIN limited by the where clause on the right table?eg

DECLARE @LeftTable TABLE (LeftID INT NOT NULL IDENTITY(1, 1), LeftValue INT NULL)
INSERT @LeftTable (LeftValue)
VALUES (111)
INSERT @LeftTable (LeftValue)
VALUES (222)

[code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

How To Include Variable In CURSOR SQL Filter Clause?

Jul 23, 2005

After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View 1 Replies View Related

Transact SQL :: Case In Where Clause With Column Comparison With Greater Than Or No Filter

Sep 24, 2015

For Below example when @x=1 to retrieve col>0 rows or all rows.

With out another if else blocks or Dynamic sql to solve only in where clause.

select 0 col into #x
union
select 1 col
union
select 2 col

declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END

--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter

View 5 Replies View Related

JOIN Filter

Aug 24, 1999

I have two tables A and B. I want to insert data into tables C and D based on join between A and B (A.column = B.column). What is the best way to accomplish this type of task?

IE-------> If column = xyz then insert into table C
IF column = abc then insert into table D
ELSE do_not_insert

Please help.
Regards

Ziggy

View 1 Replies View Related

RDA Filter With Join?

Nov 20, 2007

I've setup RDA (Sql Server 2005) for my WM5 device and it works great to pull a simple table, even with a simple where clause.

I tried to join to tables to limit the number of records but I'm getting an error saying something about cannot track because of multitable query. So is it only possible to pull simple tables with RDA. If it is, is there a workaround?



Jesus saves. But Gretzky slaps in the rebound.

View 3 Replies View Related

Should We See Replication Filter Where Clause Text In Profiler TextData On SQL Server 2005?

Sep 25, 2007


We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.

Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.

When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.

But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().

We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?

FYI, here's the text of the article filter statement:


SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (

select u.user_sys_id

from tbl_user u

join tbl_territory t on u.territory_gid = t.territory_gid

where t.terr_no_id like (

select

case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())

end

)

)

And here's the statement we ran from Query Analyzer:


declare @id varchar(10)

select @id = 'aultnc'

SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (

select u.user_sys_id

from tbl_user u

join tbl_territory t on u.territory_gid = t.territory_gid

where t.terr_no_id like (

select

case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)

end

)

)

Thanks

View 4 Replies View Related

JOIN Filter Problem

Mar 24, 2008

Hello, I am sort of new to SQL so I thought I would ask you people about this problem I'm having.
Let's say I have two tables defined as follows, where the Ids are the primary keys -

Team Table Player Table

id Name TeamId PName
1 Gophers 1 Carman
2 Pioneers 1 Stoa
1 Wheeler
2 Bozak
2 Butler
2 Ostrow

Now let's say I join them using the following statement

SELECT * FROM Team INNER JOIN Player ON Player.TeamId = Team.Id

and it gives me the following -
id Name TeamId PName

1 Gophers 1 Carman

1 Gophers 1 Stoa

1 Gophers 1 Wheeler

2 Pioneers 2 Bozak
2 Pioneers 2 Butler

2 Pioneers 2 Ostrow

But what I want as my end result, and I don't know if this is possible using SQL is this -


id Name TeamId PName

1 Gophers 1 Wheeler

NULL NULL 1 Stoa

NULL NULL 1 Carman

2 Pioneers 2 Ostrow
NULL NULL 2 Bozak

NULL NULL 2 Butler

Can this be done in SQL using JOIN and how would I do it?

View 4 Replies View Related

Join Filter Failure

Jan 26, 2006

Hello there,

I'm experiencing a really weird problem:
I have a SqlCE subscriber connecting via IIS to a SQL Server 2005 database. The database publisher/distributor and the web server are on different machines (IIS being in a DMZ) and I want to avoid the use of Kerberos delegation to share priviledges on the snapshot folder.
I configured the web synchronization to accept basic authentication and obviously the subscriber sends @internetlogin and @internetpassword. Plus, I used a DB authentication on the SQL Server database.
First problem, if i disable on the publisher configuration the anonymous authentication, replication fails (I expected to be an authenticated user)
If i enable anonymous authentication, the replication succeed but ONLY if I don't use join filters. Everything goes well with static filters, overlapping/non overlapping articles, download only tables. Using a dynamic filter results in a "The merge process was unable to deliver the snapshot to the Subscriber" error.

I really can't imagine a way to fix this behaviour.

Thanks in advance

View 10 Replies View Related

Merge: Records Associated By A JOIN Filter Not Being Sent To A Subscriber

Jan 25, 2007

Hi,

I have a merge (SQL 2005 Standard -> Express) topolgoy which is having problems 

The main problem is that the join filters don't seem to work for one area and I am hoping someone can help me with some troubleshooting advice

There are 140+ tables in the topology but the ones causing  particular  pain are a parent child relationship where the child is actually a bridge/linking table to another table.

Therefore although it is a parent child in the replication filters it is the reverse. i.e. the child has the paramterised filter on it and the parent is one level down joined by it's id.  There are other tables joined to this parent table but it stays at three levels deep.  The @join_unique_key therefore is set to 0 as is the partition options for the parent /child relationship.

However, when we synchronise we have a problem. The rows get inserted in to the database in RI order but only the child records are replicated down to the subscriber.

The child table with the parameterised filter has 13 articles joined to it in total and one of the other branches of join filters go down as deep as four levels.  Most though do not.

Does anyone have any suggestions as to why this might be happening?  Any help would be greatly appreciated.

Cheers, James

P.S. I should add this problem only occurs when the edits are made at the publisher.  If new records are added at the subscriber everything is fine.

View 9 Replies View Related

Transact SQL :: Filter - Join Query Rows

Nov 14, 2015

Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).

SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty  as CastedQty, MI.CounterStartup 
FROM        MouldItem MI
JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty
FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid

[Code] ....

View 2 Replies View Related

Join Filter Causes Conflicting Locking Hints

Jul 14, 2006

Hi,

Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).

Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:



Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view.
Command Text: sp_MSsetup_partition_groups
Parameters: @publication = test1



fn_GetIDList is the function used in the original filter.

Thanks for any help



Graham

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

Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria

Jan 22, 2008

Hello Friends,

I have two tables, And also I have Sample data in them.

create table X
(y int,
m int,
v int)

insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1

select * from X

Create table Y
(fy int,
fm int,
v int)

insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0

select * from X
select * from Y

I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m

Using temporary table cannot be done.

Thanks in Advance,
Babz

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

Temp Table Vs Global Temp Table

Jun 24, 1999

I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

View 2 Replies View Related

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







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