I want to pull ALL users from Table1 and include information that person may have in Table2. As of now, it is only including users that have something in Table2. How do I include everyone?
Thanks
I am new to SQL, as old as it is. I am not new to programming Inormally just use Access.I have two tables for a little project manager I made. After updates Isent an email to the user. I need to populate the user based on the"Assigned To" field I use, but I only log the username and not theemail address. Is there a way to associate the "Assigned To" to theUser's account in the "User" table so that I can pull thier emailaddress through code?ThanksChuck
View 5 Replies View RelatedHi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.
Thank you,
Lisa
I am trying to create a query that will pull a variety of data from three different tables. I've had to join four tables because one set of data is in a completely different table.
I am expecting one row of data but instead I get 4 rows. I suspect this is because I am joining a table indirectly. Here is the code:
SELECT
SDH.BatchID,
('0' +
CONVERT (varchar (10), SDH.WorkerID)) AS VendorID,
convert(varchar,( dateadd(hour,-1,SHIT.EndDate) ),101)
+ ' - ' + SDH.Description
[Code] ....
There is only one applicable row in SDH, but four rows appear when I run the query. All four rows are identical except for the GL.GLCode column, which lists the three GL codes associated with the DeductionItems table. The fourth row is a duplicate of the third.
i am completely new to sql but have a need to create a sort of check registry.
the data comes from two separate files like
checks
datenumnameacctamount
09/17/071747companyAtmz187.50
09/18/071748companyBtmz199.24
09/18/079326company1tmz29.65
09/18/071749companyCtmz1103.54
09/20/079327company2tmz255.01
09/20/071750companyDtmz187.12
deposits
datetypeacctamount
09/17/07deposittmz1100.00
09/17/07deposittmz2200.00
09/19/07deposittmz1300.00
09/19/07deposittmz2400.00
i would like something like this as a result
(1st group - tmz1)
09/17/07 1747 companyA tmz1 87.50
09/17/07 deposit tmz1 100.00
09/18/07 1748 companyB tmz1 99.24
09/18/07 1749 companyC tmz1 103.54
09/19/07 deposit tmz1 300.00
09/20/07 1750 companyD tmz1 87.12
(2nd group - tmz2)
09/17/07 deposit tmz2 200.00
09/18/07 9326 company1 tmz2 9.65
09/19/07 deposit tmz2 400.00
09/20/07 9327 company2 tmz2 55.01
but when i form my statement with an inner join, i miss records, with the outer,
i get a ton of records (more than both tables combined).
i have been working with sql for about week now and can do simple queries but this is
beyond me at this point. any help what so ever would be greatly appreciated.
I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue
--===== Create the test table with
CREATE TABLE #workorder
(
Id varchar(20),
wo varchar(10),
wodate datetime,
amount float
)
GO
CREATE TABLE #revenue
[code].....
I can't see what is going on, this is the situation:
I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:
I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.
So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
------------------------------------------------------------------------------------------------------------------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf"";
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);
IList _tableNames = new ArrayList();
IList _queries = new ArrayList();
############
Code that prepares tables and queries
############
for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}
the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.
I have 3 tables.
Table 1:
ID Name Description
1 ABc xyz
2 ABC XYZ
Table 2:
RoleID Role
1 Admin
2 QA
Table 3:
ID RoleID Time
1 1 09:14
2 1 09:15
1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
This is probably a simple task but I just don't know how to do it. I need to return a recordset of details with a row of totals for selected columns. Something with a result like this:
Location Attendance Showings
======== ========== ========
JOHNS 210 3
SEREN 116 2
total 326 5
I know I could do this with a stored procedure, but I'd prefer to do it with just one sql statement. I tried compute but because I have a total on more than one column, the returned recordset is actually 3 rows.
Thanks in advance for any suggestions.
Hi everyone,
Is there a way in SQL to include and execute another file? For example, I have file1 and I would like to execute the contents of file2 from file1. How can this be done?
Thanks!
Need to find a way to do this. I have two tables, One with codes i.e. S,R,T and other with transactions looks like this:
Transaction table
Emp Trans Tot...
X55677 S 8
X55677 R 2
C22887 S 4
C22887 T 3
F66889 S 9
F66889 R 4
F66889 T 3
Code table
S
R
T
Not sure if the code table even helps. What I want to do is show all three transactions for each employee even if they don't have any (would be zero) like so...
Emp Trans Tot...
X55677 S 8
X55677 R 2
X55677 T 0
C22887 S 4
C22887 T 3
C22887 R 0
F66889 S 9
F66889 R 4
F66889 T 3
How can I get to this. I'm sure there must be a solution already posted for something like this but I'm not sure what to search for.
Thanks for your thoughts.
select *
from dbo.RPT_ContractDetails
where businessname like '%carl's%'
carl's should be a string.
Thanks
Hi, I have the following query stored:
SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)
Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...
As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.
The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.
Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.
I'd get
Prod | Qty | Week
1 23 1
1 12 2
2 10 3
3 22 1
3 15 2
3 12 3
3 4 4
Although this looks fine - what I actually need is:
1 23 1
1 12 2
1 0 3
1 0 3
2 0 1
2 0 2
2 10 3
2 0 4
3 22 1
3 15 2
3 12 3
3 4 4
Does that make sense?
Any ideas on how to do this?
We today have 3 different indexes on a table that look like this
CREATE INDEX IX_01 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX])
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
CREATE INDEX IX_03 ON [TORDLOG] ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [RADNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
I want to combine the index to only 1 index; How do can combine all the include columns to one large? Or shall keep all index. Bit bad on how to handle include columns. Or just create new index with regdat and forget all about include?
CREATE INDEX IX_02 ON TORDLOG ([REGDAT])
OR
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE (ALL COLUMNS)
Hi:I have an application in which I am producing a lists of media outlets based on a number of 6 possible parameters including location, medium, format, scope, language and coverage. I have an interface in which users can select some or all of the possible parameters. Where they don't choose a parameter - e.g. location, I would like the SELECT query to include all locations. I am at the point now of writing 6 X 6 = 36 possible queries to handle the different combinations of possible parameters. This seems a bit excessive and clumsy. I am just learning about stored procedures and was wondering if it is possible to create a procedure that would accept the 6 parameters including ones with null or 0 values and generate a query that would ignore those that were null or zero. Any help in this regard greatly appreciated.Roger Swetnam
View 5 Replies View RelatedHi Guys,
Is it better to create all tables (in store procedure) and then another store procedure to create and apply all relationship between the tables??? , OR
is it better to have each table relationship at the end of the store procedure to create that table and have each table relationship at the end of its create statement???
What are the Advantages and disadvantages of each method?
Thanks,Mehdi
Hi I have a query that return set of records between 2 given timestamps. What I want to do is to include one record before and one record after to the result. For example I have the following dataRow Timestamp1 01/01/20072 15/03/20073 17/04/20074 05/05/20075 10/05/20076 11/06/20077 12/07/2007 My query returns row 3 to 5 for example and I want to include row 2 and 6 to the result as well. How can I do that? Is it possible to do it in 1 query?
View 11 Replies View RelatedHi,
I have a test database and need to to include the aspnet.mdf into my database.
I have generated a script for the aspnet.mdf, but don't know how to run/include this into my own test database.
1. Is there any magical sql-command that do the job in a clean way for me?
2. How can I change the name of aspnet in that auto generated script, if needed at all?
I have: SQL server Express Edition 2005, Visual Web Developer Express Edition 2008
Thank's
/Pepe
I have a table of parts and want to display some statistics from it. The parts table has several fields in it, but the two that I need to use in my sql select queries are "PartUserID" and "DateUseBy", but I don't know how to use the "dateuseby" in the second example.Can someone help me out with #2?1. Display the total number of parts owned by a user:SQL Query: SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId)" OnSelecting="sqlPartCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> </SelectParameters>Code-behind: protected void sqlPartCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; } 2. Display the total number of parts owned by a user that are to be used in the current year. The table has a field "dateuseby" that should be used for the "Where" but I don't know how to get it.SQL Query:How to I include the "usebydate" so that it will use 2008 as the year ? I wrote the following query as an example of what I'm trying to get. SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId, ZCPart.DateUseById = @PartUseById)" onselecting="sqlPartYearCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> <asp:Parameter Name="DateUseByID" /> </SelectParameters>Code-behind: protected void sqlPartYearCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; // ? How do I change the date in the field to be in "2008" format and then put that in the sql query? //e.Command.Parameters["@DateUseByID"].Value = currentYear; //currentYear currentDateTime = DateTime.Now; }
View 7 Replies View RelatedI want to retrieve some data in my gridview using a sqldatasource. Here's the idea. My Gridview contains events. A user can subscribe into one of them. When he subscribes, the event must be removed in the gridview. So when there is a subscription from that certain user for that event, it may not appear. Here's my not working code..SELECT Event.EventID,Event.name, Event.LocationID, Event.Date, Event.StatusFROM Shift INNER JOIN Event ON Shift.EventID = Event.EventID INNER JOIN Subscription ON Shift.ShiftID = Subscription.ShiftIDWHERE (Subscription.UserID <> @UserID) Greetz
View 1 Replies View RelatedI used to be able to write a query like this:
select * from foo;
But after re-creating my database using RESTORE, I now have to do this:
select * from myName.foo;
...where myName is either the name of the database or the name of the database owner. That is, it's the name of the database, but I believe it's also the name of the database owner.
How do I go back to being able to just use the table name?
Thanks!
Hi,I have 2 tables created. One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)PersonID | PersonName PersonID | Month | Year | Total Sales================== ==================================ID1 | Sally ID1 | 1 | 2005 | 1000ID2 | David ID2 | 2 | 2005 | 1500Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)I would like the result to be like the following:PersonID | Month | Year | Total Sales===================================ID1 | 1 | 2005 | 1000ID1 | 2 | 2005 | 0ID1 | 3 | 2005 | 0(For month 4 - 12) Total Sales will be 0 too as no records exist in the first placeID2 | 1 | 2005 | 0ID2 | 2 | 2005 | 1500..........
View 2 Replies View RelatedI have a query with 4 parameters:
Name
Location
Employee Number
Officer Code
There are no null values in Name, location and Employee Number. However, all employees do not have an officer code. So in my query I use a where clause that says WHERE OfficerCode LIKE '%'+@Param4+'%'. I use a % for the default value in my ASP.net data control. The only problem is that the only records returned have non-null values in the OfficerCode field. How can I use a wildcard for a default value and return all records (null and non-null)?
I wanted to create something like this:Select (FirstItem + "/" + SecondItem) AS Itembut I get error. Is there anything wrong with this code?P.S. I'm using mssql 2000
View 2 Replies View RelatedI have this but how can I get all the columns of the row? I only want them distinct per these 2 cols.
SELECT DISTINCT OrdHst.ordernbr, OrdNbr.trans
FROM OrdHst JOIN OrdNbr
ON OrdHst.ordernbr = OrdNbr.ordernbr
ORDER BY 1,2
Could return:
12345 001
12345 AAA
12345 BBB
12345 CCC
12345 PWB
how to include asc near order by
select @sql= ' select u.userid,u.user_name, u.password, c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.' + @columnname + ' like ''' + @recordname + '%'' order by u.' + @columnname
I have about 150 people I would like to send e-mail automatically. Eachperson would get a unique form letter that includes username andpassword I have stored in a SQL table. Is this possible? Helpappreciated. Thanks.Frank*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
when i tried to put an orderby in view...i am getting an error...
we can't have a orderby in view?.