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
ADVERTISEMENT
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
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
Mar 28, 2008
Hi,
I am trying to bcp from a remote server. They have bcp.exe file installed . I guess this is for SQL 2000 server.
I am able to bcp data from SQL 2000 server . But I am unable to bcp data from SQL 2005 server which has servername like "servernameinstancename"
I am getting error " Server does not exists or access denied"
How does the bcp.exe file differ for SQL 2000 and SQL 2005 servers? Do I need to install anything to make this workl?
View 1 Replies
View Related
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
Apr 18, 2008
What is the advantage of using a formal reporting product (such as Microsoft Reporting Services) over hand-writing a repoting app in a general-pupose tool such as ASP.NET?
I've always done the latter in my career. I've followed some Reporting Services tutorials. Here is my impression so far. Is this right?
Pros for using Reporting Services
- Lots of pre-built funtionality "for free": building adhoc reporting interfaces is easier, users can subscribe to reports, have canned reports auto-built every month, etc
- Superior Integration with other products such as SharePoint.
Pros to using plain ASP.NET
- Less learning curve
- Far more flexibility: you can add any feature you want to your intefaces and support whatever logic you can imagine.
View 1 Replies
View Related
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
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
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
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
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
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
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
Oct 29, 1998
We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :
sp executes spA,spB and spC
Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.
In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.
We use SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we get now rows in Crystal.
I enclose an example (with 3 sub- SPs). Help. Martin
CREATE PROCEDURE coral.qryPayEmp8_newtmp @lQryCompanyNumber int,@dtQryPeriodFromDate datetime,@dtQryPeriodEndDate datetime,@dtQryPayrollYearStartDate datetime,@szQryCompanyTaxReference varchar(30) AS
--variables used for cursors
DECLARE
@lCCUniqueID INT,
@lUniqueID INT,
@lEmployeeNumber INT,
@szEmployeeSurname VARCHAR (20),
@szInitials VARCHAR (4),
@szDeptNo VARCHAR (6),
@dtDateLeft DATETIME,
@EdTotalGrossPayThis FLOAT,
@EdTaxableGrossPayThis FLOAT,
@EdTotalGrossPayPrevious FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
@EdTaxPaidThis FLOAT,
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate FLOAT,
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),
--output from qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
@SumOfdEmployeeNIContractedOut FLOAT,
--output from qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT
SET NOCOUNT ON
DECLARE employee_period CURSOR
FOR
SELECT tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,
tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,
tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,
tblEmployees.dTotalGrossPayThis,
tblEmployees.dTaxableGrossPayThis,
tblEmployees.dTotalGrossPayPrevious,
tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,
tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate
FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)
DECLARE employee_ni_categories SCROLL CURSOR
FOR
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,
tblEmployeePeriodDetails.cNICategory
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
OPEN employee_period
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8b @lUniqueID,
@dtQryPayrollYearStartDate,
@SumOfdEmployersNICHoliday OUTPUT
EXEC qryPayEmp8c @lUniqueID,
@dtQryPeriodEndDate,
@PdTotalGrossPayThis OUTPUT,
@PdTaxableGrossPayThis OUTPUT,
@PdTaxPaidThis OUTPUT,
@PdSMPPaid OUTPUT,
@PdSSPPaid OUTPUT
OPEN employee_ni_categories
FETCH FIRST FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
IF (@@FETCH_STATUS <> -1)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8a @lUniqueID,
@szNICategory,
@dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,
@SumOfdEmployersNI OUTPUT,
@SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid,
@szNICategory AS szNICategory,
@SumOfdNIablePay AS SumOfdNIablePay,
@SumOfdEmployersNI AS SumOfdEmployersNI,
@SumOfdEmployeeNI AS SumOfdEmployeeNI,
@SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings,
@SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut
FETCH NEXT FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
END
ELSE
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid
CLOSE employee_ni_categories
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
END
CLOSE employee_period
DEALLOCATE employee_period
DEALLOCATE employee_ni_categories
RETURN
-----------------
CREATE PROCEDURE coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) = 'A',
@dtPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT OUTPUT,
@SumOfdContractedOutEarnings FLOAT OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),
@SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),
@SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),
@SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s),
@SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory = @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory
RETURN
-----------------------------
REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT = 1,
@dtQryPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT
@SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
RETURN
--------------------
CREATE PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT = 1,
@dtQryPeriodEndDate DATETIME = '1900-01-01 00:00:00.000',
@PdTaxableGrossPayThis FLOAT OUTPUT,
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET NOCOUNT ON
SELECT @PdTotalGrossPayThis = tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis = tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis = tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid = tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid = tblEmployeePeriodDetails.dSSPPaid
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))
RETURN
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
