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.
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.
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'
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:
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.
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
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!
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?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
<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
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
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
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?
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".
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
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?
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
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 ?
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)
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')
I think I am getting 0 records returned... because.... I am joining the third value based on a zip code. The two tables if directly compared to each other would never have an = match. SELECT t2.company_name, t2.firstname, t2.lastname, modelname, configname, format, version, username, t2.zip,
t2.country FROM EtechModelRequests JOIN CC_Host.dbo.USR_SC as t2 ON Cast(t2.user_id As char) = username --JOIN --Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON PostalCode = zip WHERE RequestDateTime > CONVERT(DATETIME, '2007-09-1 00:00:00', 102) AND interfacename LIKE '%download%' AND
result=0 AND country='CA' --AND t3.PostalCode Like 'z1x%' ORDER BY company_name I was trying to do it by using a Where clause AND t3.PostalCode Like 'z1x%' that I will later turn into an Input Parameter after I get it working. Is there anyway to trim the PostalCode to the first three characters during the join process? Something like Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON LEFT(PostalCode, 3) = zip Not sure I got the LEFT function syntax correct even. Help appreciated.
I'm joining one table on to another table using one of 2 possibile fields (so table 1 key one can either match table 2 key 1 or key 2)... When the first key is null for a record, the script is to attempt to join using the second key instead. It is possible to have both values present, in which case the first one should be used.
I've taken a few runs at this so far:
... from table1 t1 left join table2 t2 on (t1.key1 = t2.key1 or t1.key1 = t2.key2)
If either t2.key1 or t2.key2 are populated, this works. Unfortunately, it's bringing back multiple records if both key1 and key2 are populated. Question # 1... Is there a different relational operator I can be using instead of OR that would logically look like 'if thie first key didn't find anything try the second instead'?
As an alternative, I've put the NVL to use...
NVL(t2.key1, t2.key2) = t1.key1
That seems to work, but it's pretty heavy on the server. Any suggestions on how else to handle this scenario would be greatly appreciated
view 1 I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.
view 2 notes field I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.
I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)
so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.
In layman's terms, what is the difference? Any examples? Thanks in advance.
I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes
SELECT SEP_ACCOUNT.sep_acct_cde as Account, FUND.fund_cde as FUND, BRKG_FACT.accum_unit_cnt as Units_Purchased, BRKG_FACT.transaction_amt as Amount_Purchased FROM BRKG_FACT SEP_ACCOUNT FUND where BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM brkg_fact.SEP_ACCT_ID_NUM = 5 and brkg_fact.product_cde <> 'MM' and brkg_fact.transaction_amt <= 0 and brkg_fact.source_sys_id_num <> 3 and brkg_fact.source_sys_id_num <> 5 and BRKG_FACT.trans_process_dte >= '1/1/2008' and BRKG_FACT.trans_process_dte <= '1/2/2008' order by fund_cde
current output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LCCS -0.18216 -5.31000
wanted output ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED U BLCD -0.01137 -1.48000 U BOND -0.01283 -1.67000 U BWDS NULL NULL U CGDE -0.06743 -0.95000 U EQIN -0.13277 -2.39000 U GAFR NULL NULL U GRST -0.11799 -4.07000 U IX4S -0.53996 -12.55000 U LIGE NULL NULL U LCCS -0.18216 -5.31000
I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped. I need a to show the count of each events that have happened to it within a certain time period. This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table. What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count) Any thoughts? I'm a complete newbie at this by the way.
Thanks
Code Snippet
SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit FROM DeviceEventDurationLog RIGHT OUTER JOIN DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate) GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit ORDER BY DeviceStatusWords.DeviceName
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?
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 ?
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
