I've been struggling with this one for quite some time now and would appreciate some help:-
I'm trying to get a list of schools that only has male students. the closest I have so far:-
SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
FROM Schools
INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID)
WHERE (FORUM_MEMBERS.UserType=4)
GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;
The result is a list of schools:-
SchoolName, M_SEX, no_of_students
school 1, Male, 11
school 1, Female, 20
school 2, Male, 8
school 2, Female, 1
school 3, Male, 12
school 4, Male, 14
school 5, Male, 8
school 5, Female, 1
But what I really need is schools with only male students:-
SchoolName, M_SEX, no_of_students
school 3, Male, 12
school 4, Male, 14
We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?
I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name FROM tblTable GROUP BY Name ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
I have a table with several foreign key relationships. I am scripting with PHP and was wondering if anyone knows how to query the database to show the relationships that a table has. I have been using MySQL, but for a number of projects have to use MS SQL.
Data: insert into product_stocks values(1,'A',30) insert into product_stocks values(2,'A',70) insert into product_stocks values(3,'A',60) insert into product_stocks values(4,'A',40) insert into product_stocks values(1,'B',90) insert into product_stocks values(2,'B',60) insert into product_stocks values(3,'B',70) insert into product_stocks values(4,'B',40) insert into product_stocks values(1,'C',40) insert into product_stocks values(2,'C',50) insert into product_stocks values(3,'C',80) insert into product_stocks values(4,'C',90)
Result Set: product_type product_id no_of_units --------------- ------------- -------------- A 2 70 A 3 60 A 4 40 B 1 90 B 3 70 B 2 60 C 4 90 C 3 80 C 2 50
i.e The result set gives the top 3 products in each product_type based on the no_of_units.
I have a warehouse table but I don't know which query will update warehouse inside of information ? Thus, how to write a query list all query have include this warehouse table name in there ?
Say the table has a primary key, and the latest value on it is 100, then all the records on that table are deleted... If I INSERT new record, the key will be 101... Can I find out what the key is going to be before inserting a new record to the table?
I have a query that can tell me the columns in a table. In this case "Contact". What I would like to add to my query is if the column is part of the primary key. Can that be done? how so?
Code Snippet SELECT c.name AS column_name, c.column_id, SCHEMA_NAME(t.schema_id) AS type_schema, t.name AS type_name, t.is_user_defined, t.is_assembly_type, c.max_length, c.precision, c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;
Hey - I know that I can find space information about SQL Server. Allocated space, free space, used space, data space, etc... BUT is there a way that I can query how much total/available space is on the actual drive? For example, let's say that I have SQL installed on the D drive of a Server; i also have another application on that drive. I know that I can query how much room SQL Server is using, but can i query how much total/available space is on the drive? Any help appreciated.
I've tried everything I can think of to find all the records in a table column (lastname) that contain an apostrophe. I know they are there (O'Brian, D'Marcus, etc.) However, I keep getting syntax errors.
I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .
The table structure is defined and i cannot edit it .
Please let me know if we could have a single query to do this .
Now I want to run a select query that will only bring the ones that have ‘CA’ or ‘US’ as the first two characters.
I can do that by running this:
select * from tDocISIN where sISIN like 'CA%'or sISIN like 'US%'
But my other where clause I want to add is only bring back the ones that have CA or US as the first two characters and any other character after the first two or in the rest on the string.
So US7636516745 is an ISIN I don’t want to bring back and US763TY16745 is an ISIN I do want to bring back.
The character that does exist after the first two characters will be anywhere within the rest of the string.
Hope that is clearly explain any help would be great.
Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
I have two tables in my database. Some matching rules are associated with these tables and I want a query which will retrieve those data based on the matching rules provided below:
I am using the SSIS Import & Export Wizard to make packages importing data from Access 2003 into SQL Server2005.
In the Access database I am using, there are about 100 tables and 4 select queries.
When I ran the SSIS wizard, it found the first query fine, and the package was made successfully.
Now, I need to make a package to import data using one of the other select queries (it has two subqueries.)
When I run the wizard, the "Select Source Tables and Views" dialog opens and shows all 100 tables, plus only two of the queries, not all 4. Two are missing, including the one I need to do the import.
I cannot see why these two queries would be excluded. One is a compound query (the one I need) the other is not. Is there a limit to the number of objects the dialog can show?
If anybody has had this problem please let me know. For now it's a real show stopper.
I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks -Kyle
Query1: SELECT TOP 5 A,B,C from Table1 where A > 10 or B < 10
Query2: SELECT @@rowcount
After 'Query1' if 'Query2' is executed will return 5.
But how to get the total resultset number of rows for 'Query1' (without TOP) by executing the 'Query2' or others. The above option is available in mysql.
I have a table called MessageBoard. It has a column called Messages. A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write? For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
Greetings, I'm trying to come up with a way (efficiency isn't the most important thing here) ..to show how a person is related to another person on my website. The table structure is like so (simplified) ID | UserName| FriendName 1 | Danny | Mike2 | Mike | Danny3 | Steve | Jason4 | Jason | Steve5 | Steve Danny6 | Danny | Steve There are 2 rows each for every 'relationship' - I chose this design to simplify queries like "give me all of danny's friends" etc. With this stucture, I want to be able to display to a user how they are "related" to someone that they may not necessarily be friends with. For example, lets say you are logged in as "Jason" and you are looking at "Dannys" homepage. You are not Danny's friend..but you are related to him through Steve (because you are a friend of steve, and steve is a friend of jason) I'm stumped trying to come up with a decent way to get this type of information..either in a sproc...or doing it in the business layer (c#). I think recursion is the way to go, and I could limit it to 4,5,6 levels whatever... again, efficiency isn't an issue here...I just want to somehow pull the data cleanly. Any ideas?
Not sure how to do this. I think I need to use a Group By query. I have a "Products" table with the following fields:
Program# Number UPC Item Item# Size Dept
Everything is specific to the Program#. In other words, for every instance of a Program# there can be more than one Product, which is specified by the "Number" field. So: SELECT * FROM Product WHERE [Program#] = '12345' should return this:
In which case I have to fix them (the "Number" field) with an update query sp they are numbered sequentially. Luckily, this doesn't happen very often.
Can someone help me with a query that will return only those records with duplicate values in the "Number" field? I am not sure how to construct this query which I will use as a stored procedure.
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
