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
ADVERTISEMENT
Oct 25, 2007
A question for everyone:
With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
Thanks, in advance,
Brian
View 7 Replies
View Related
Oct 1, 2007
I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:
Code Block
select t1.* from tblconfiguration t1
,tblconfiguration t2
where t1.component = 'AdjProcessUtility'
and t1.servername *= t2.servername
and t1.component *= t2.component
and t1.context *= t2.context
and t1.property = 'proc'
and t2.property = 'fff'
Result:
SQLEDEV1 AdjProcessUtility DuplicatesReport Proc Adjustment.dbo.prcDuplicatesReport
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport Proc Adjustment.dbo.prcValidationReport
When the converted (using SQL enterprise Mgr) runs it returns no rows:
Code Block
SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')
I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.
Here's the table definition:
Code Block
CREATE TABLE dbo.tblConfiguration
(
ServerName VARCHAR(30) NOT NULL,
Component VARCHAR(255) NOT NULL,
Context VARCHAR(255) NOT NULL,
Property VARCHAR(255) NOT NULL,
CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ),
Value VARCHAR(255) NOT NULL
)
I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns
Here is some of the data:
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ExtractAdjFile RunTime 13:25
SQLEDEV1 AdjProcessUtility ExtractAdjFile Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ExtractAdjFile FixedRecLength 71
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteFileHeader Y
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteTempTable Y
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport ReportClass ReportCSV
SQLEDEV1 AdjProcessUtility ValidationReport Ids Validation
SQLEDEV1 AdjProcessUtility ValidationReport RunTime 15:06
SQLEDEV1 AdjProcessUtility ValidationReport Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ValidationReport DefaultFileName Adj_ValidationReport_MMDDYYHHMM.csv
etc.
Any help is greatly appreciated,
Sid
View 16 Replies
View Related
Sep 25, 2005
I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.
Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?
2) If I write a query (shown below), it tries to join table A and B returning large number of rows.
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'
Why does it try to join the table B with A though there is no join specified.
View 12 Replies
View Related
May 20, 2006
Hi,Just curious. Would you use ANSI style table joining or the 'oldfashion' table joining; especially if performance is the main concern?What I meant is illustrated below:ANSI Styleselect * from a join b on a.id = b.idOld Styleselect * from a, b where a.id = b.idI noticed that in some SQL, the ANSI is much faster but sometimes, theold style looks much better.It's ridiculous to try out both styles to see which is better wheneverwe want to write an SQL statement.Please comment.Thanks in advance.
View 1 Replies
View Related
Oct 24, 2001
How to use ANSI-standard JOIN to write follow query which contains two outer join ?
SELECT a.*,b.title as classification,c.title as
employees,d.username,d.password,d.role_id,d.status
FROM DBO.PROFILE a,DBO.classification b,DBO.employee c ,DBO.USER d
WHERE a.user_id = 1 and a.employee_id *=c.id and a.classification_id *=b.id
and d.id= 1
Many thanks
View 1 Replies
View Related
Oct 5, 2006
Hi everyone.. can anyone help me on how to solve my problem regarding on Select.. im using PB6.5 and running on MSSLQ2005 database.. i attached an image for your reference.. thnks!
View 5 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 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
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 14, 2007
<asp:SqlDataSource ID="productsUpdate" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccountType %>"
SelectCommand="SELECT [ProductID], [ProductName], [ImageTitle], [ImageData], [ImageMimeType] FROM [Products] WHERE [ProductID] = @ProductID"
UpdateCommand="UPDATE [Products] SET [ImageTitle] = Null, [ImageData] = Null, [ImageMimeType] = Null WHERE [ProductID] = @ProductID" InsertCommand="INSERT INTO [Products] ([ImageTitle], [ImageData], [ImageMimeType]) VALUES (@ImageTitle, @ImageData, @MimeType) WHERE ([ProductID] = @ProductID)">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ImageTitle" />
<asp:Parameter Name="ImageData" Type="string" />
<asp:Parameter Name="MimeType" Type="string" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ImageTitle" Type="String" />
<asp:Parameter Name="ImageData" />
<asp:Parameter Name="MimeType" Type="String" />
</InsertParameters>
</asp:SqlDataSource> Hi Guys, new to development using visual studio and vb.net 2.0. and SQL express. Have a small problem.I have an update command which im using to change the values of a blob image table to NULL so that i can then insert a new image into the table. This works fine. My problem is that my insert statement has an error and i cant figure this out. if i dont add the where clause it works fine but inserts the new image into the table without the product data, i want to insert the new image into the existing product information. The error message i keep getting is "incorrect syntax near where"Any help would be greatly appreciatedThanks
View 7 Replies
View Related
Feb 6, 2005
I have the code below and the where clause is giving me errors, I have already setup FT Index, I think the error is lack of ' or " or "" or some kinda syntax pls help... as I can figure it out
I passed the word MBAISE
where CONTAINS(*, '+@searchstring+' )
Declare @myStatement varchar(8000)
Declare @LastRec int
Declare @FirstRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec =(@CurrentPage * @PageSize + 1 )
SELECT @myStatement = ' SELECT SalaryStatistical, EmployerID,JobLocation, location, SectorID
FROM #TEMP
where CONTAINS(*, '+@searchstring+' )
AND (LastLogin >= dateadd(d, -' + @HowManyDays + ', getdate())
AND (ID > '+ convert(varchar(20),@FirstRec) +')
AND (ID < '+convert(varchar(20),@LastRec) +')
AND (SalaryStatistical > '+ convert(varchar(20),@SalaryStatistical)+')
'
IF @JobLocation != @myzero
BEGIN
SELECT @myStatement = @myStatement + ' AND (JobLocation IN ('+convert(varchar(90),@JobLocation)+')) '
END
IF @sectorID != @myzero
BEGIN
SELECT @myStatement = @myStatement + ' AND (SectorID IN ('+convert(varchar(90),@sectorID)+')) '
END
SELECT @myStatement = @myStatement
print @myStatement
exec(@myStatement)
GO
View 1 Replies
View Related
Nov 6, 2007
I started reading a very informative book (Inside Microsoft SQL Server 2005: T-SQL Querying) that uses the following example.SELECT orderid, customerid,
COUNT(*) OVER(PARTITION BY customerid) AS num_orders
FROM dbo.Orders
WHERE customerid IS NOT NULL
AND orderid % 2 = 1;
Being new to T-SQL, I don't understand what is going on in the last line "orderid % 2 = 1". I tried searching "Books On Line" with no answer. Is there a good reference or textbook out there I can use to find the answers to specific syntax questions?
View 3 Replies
View Related
Apr 16, 2008
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
View 5 Replies
View Related
Jun 8, 2008
Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As New DataSet() Dim sGetRooms As String = "SELECT RoomID, RoomType, " _ & "RoomName FROM Rooms2 " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomType" Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _ & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomPrice" Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString Using con As New OleDbConnection(sConnect) Dim da As New OleDbDataAdapter(sGetRooms, con) Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10) param.Value = Request.QueryString("RoomType") & "%" da.SelectCommand.Parameters.Add(param) Try da.Fill(ds, "Rooms2") da.SelectCommand.CommandText = sGetroomsizeandprice da.Fill(ds, "roomprices") Catch ex As Exception Label4.Text = "ERROR: " & ex.Message Exit Sub End Try End Using Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID") Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID") Dim dr As New DataRelation("MenuLink", pkcol, fkcol) ds.Relations.Add(dr) DataList1.DataSource = ds DataList1.DataMember = "Rooms2" DataList1.DataBind() End Sub
View 2 Replies
View Related
Oct 17, 2006
I have used this query statement with a SQL Server 2005 database and need to use something similar with an Access database:
SELECT products.*, Category AS Expr1
FROM products
WHERE (Category = @Category)
When I test this in a table adapter there is no preview due to lack of parameter. I seem to recall that Access uses different syntax in the WHERE filter clause (i.e., not @). Can someone help me out with this?
View 3 Replies
View Related
Mar 26, 2008
What is the equivalent for INCLUDE clause (in Create index syntax) in SQL Server 2000.
SQL Server 2005 will support Include clause in Create index syntax . How to attain it in SQL Server 2000
Example : I have below query executable in SQL Server 2005
CREATE INDEX Index_Name ON mytable(col1 ASC) INCLUDE (name,id);
What is the equivalent for the above query in SQL server 2000
Thanks ,
Sushma
View 1 Replies
View Related
Nov 16, 2007
You know how you can go:
Code Block
where control_id in (11111,22222,33333,44444)
or
Code Block
where TextName in ('11111','22222','33333','44444')
and you can do this:
Code Block
where TextName like '11%' or TextName like '22%'
well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.
where TextName like in ('11%','22%',... and so on)? is anything like that possible?
or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )
can you imagine the dynamics if that syntax actually worked?
Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')
View 13 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
Aug 9, 2006
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP.
I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause
The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int."
Thanks
Neil
CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int)
AS
Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000)
CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int )
SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName'
SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END
Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId)
SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId
FROM vw_GetPersonsByCategoryAndType '
EXEC (@SelectClause + @WhereClause +@OrderByClause)
View 1 Replies
View Related
Oct 29, 2015
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')
View 7 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
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
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
Mar 29, 2004
Monitoring a sql 2000 server where a vendor app is running. Looking at a poor performing sql and I saw the following in the where clause
WHERE immunization_mast_.account_id =* p.account_id AND
immunization_mast_.case_id =* p.case_id
What is the meaning of the =*, have not seen this before?
View 2 Replies
View Related
Jun 15, 2004
Hi
I have two databases on my server, I need a simple query with one join between one table from each database.
I looked in the help of FROM clause and found the Argument "table_source" where it explains this :
"If the table or view exists in another database on the same computer running Microsoft® SQL Server™, use a fully qualified name in the form database.owner.object_name".
Can someone please help me fill the variants ??
My DB name is "Forum" the owner is "DBRNDAdministrator" and the table name is "TblUsers", so I tried to write in the FROM clause :
"FROM Forum.DBRNDAdministrator.TblUsers" but it doesn't work... so anyone have any idea how should it be ?
Thanks,
Inon.
View 4 Replies
View Related
Jul 25, 2007
Hello,
I need to write and Ansi Outer Join that has 2 columns from the same table.
I keep getting
Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.
here is what the code looks like. any help is appreciated.
select ...
from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key
where...
View 9 Replies
View Related
Jul 23, 2005
I am trying to figure out some sql syntax, and I could use some help. Thisis my first atempt at joins, so bear with me.I have a table (A) which looks like the followingID Data Source-------------------------1 abcdef 1002 abcdef 1003 abcdef 2004 abcdef 2005 abcdef 200A second table (B) which looks like the followingKey ID------------------------Key1 1Key1 2Key1 3Key1 4Key2 1Key2 2Essentially, A is a table of items, and B is a table of where those itemshave been used (Key1 is like an invoice which has items 1-4 on it, Key2 is asecond invoice with 1 and 2.) Source, in table A, is like the itemsupplier.I would like to get a list of every invoice (Key) that has used a part (ID)from a particular Source.So, for example, I would like to query for source 100 and get back (Key1,Key2) or query for source 200 and get back only Key1.To this end, I tried"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =100)"But I got an empty recordset, so something is amiss.Any help is greatly appreciated.Thanks,-d
View 2 Replies
View Related