SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X
Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.
What code i need to write to
I think .. Select * from Items AS IT WHERE IT.Subcategory = ???
SELECT SubCategoryID from SubCategories SCAT INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId WHERE SCAT.ParentCategoryId = X
Now i will to retrieve all rows from a Table called Items where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.
What code i need to write to ?
I think .. Select * from Items WHERE Items.Subcategory = ???
I don't know what code i need to type, anyone can help me
Has anyone find solution for this problem. i also checked http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=298056&SiteID=1 and http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx
Suppose have a Dimension table
DimColor ---------------------------- ColorKeyPK(smallint) ColorAlternateKey(nvarchar(30)) -1 UnknownMember 1 2 Blue 3 Red 4 Black
Color with the ID 1 is empty string
FactOrders --------------------------- OrderID Date Color Quantity
OrderID = 1 Color = 'Black' Quantity = 10 OrderID = 2 Color = 'Red' Quantity = 20 OrderID = 3 Color = '' Quantity = 10 OrderID = 4 Color = 'Blue' Quantity = 5 OrderID = 5 Color = Black Quantity = 10
When i use the Lookup transform it cannot find the ColorKeyPK
The result of the Lookup transform is. ------------------------------ OrderID = 1 Color='Black' ColorKey=4 OrderID = 2 Color='Black' ColorKey=3 OrderID = 3 Color='Black' ColorKey=NULL ----> This is the problem Lookup cannot find empty string. It should be 1. OrderID = 4 Color='Black' ColorKey=2 OrderID = 5 Color='Black' ColorKey=4
A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?
I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.
Hi... Need help with some SQL-code: Im just interesting in how many rows in my table 'Location' that has 'New York' in the column called 'City'.... So I just want to return the number of rows that is macthing... How do I write the sql-part for this??
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Hello. I'm quite new to SQL so have included as many details as i can think of here. The scenario is a wordsearch style puzzle. The user can select their answers in any order and these answers are stored in a table. I need help with the UPDATE statement to compare the users answers against the correct answers for that puzzle. (Note: In the actual scenario there will be 10-15 answers per grid, but i have reduced the number to make testing easier - hopefully the code for a working UPDATE statement will be scalable to account for grids with different numbers of answers etc.)
The Tables: -- These are the correct answers for a given grid (gridid). -- Due to the nature of the puzzle, answers can be in any order. -- Each level may contain one,two,or no 'bonusanswers' which are harder to find, so these are scored separately so bonus points can be awarded. CREATE TABLE correctanswers ( gridid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED, answer1 char(15), answer2 char(15), bonusanswer1 char(15), bonusanswer2 char(15) )
-- These are the user submitted set of answers 'answerid' for level 'gridid'. -- Answers may be submitted in any order. CREATE TABLE useranswers ( answerid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED, gridid smallint, firstanswer char(15), secondanswer char(15), thirdanswer char(15), fourthanswer char(15), )
-- A user (userid) submits their answers which get stored as answerid. -- This table shows the scores for each set of answerid's the user has submitted. -- A high score table for both the individual user, and all users may be created using this table. CREATE TABLE userscores ( userid smallint, answerid smallint, mainmatches smallint, bonusmatches smallint )
The Test Data: -- sample test data -- 2 users userid's '1' and '2' each have two goes on level1 (gridid 1)
-- correct answers for gridid 1 INSERT INTO correctanswers (answer1, answer2, bonusanswer1, bonusanswer2) VALUES ('cat','dog','rabbit','elephant')
-- user submitted answers for gridid 1 INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','rabbit','horse','cow') INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','cat','elephant','horse') INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'rabbit','cat','elephant','donkey') INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'horse','cat','dog','sheep')
-- scores for users attempts - columns 3 and 4 needs calculating INSERT INTO userscores VALUES (1,1,null,null) -- one main answer and one bonus answer, so null,null should be 1,1 INSERT INTO userscores VALUES (1,2,null,null) -- two main answers and one bonus answer, so null,null should be 2,1 INSERT INTO userscores VALUES (2,3,null,null) -- one main answer and two bonus answers, so null,null should be 1,2 INSERT INTO userscores VALUES (2,4,null,null) -- two main answers and no bonus answers, so null,null should be 2,0
I have included the correct new table values for the sample data - basically filling in the two null fields in the 'userscores' table.
I haven't used SQL much but from the little i know then i think the answer will include JOIN, COUNT and IN statements as part of the UPDATE statement...but i haven't a clue where to start with the order/logic etc.
I have looked for sample solutions myself but all the examples i have found so far are to do with exact matches between two tables, whereas in my scenario i need to know how many matches, irrelevant of the order.
Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark
I am pretty new to writing my own queries in SQL and am tied to Access unfortunately. Here is my query as it stands:
SELECT Count(*) AS COUNT_ACT_2_ATTEND FROM [Main Table] WHERE ((([Main Table].ACT_2_ATTEND)="Always" Or ([Main Table].ACT_2_ATTEND)="Frequently" Or ([Main Table].ACT_2_ATTEND)="Sometimes" Or ([Main Table].ACT_2_ATTEND)="Never" Or ([Main Table].ACT_2_ATTEND)="N/A")) GROUP BY [Main Table].ACT_2_ATTEND;
However, if there is not match it won't retun a zero, it just returns no result and so I cannot tell which counts correspond to which matches
Please indulge my ignorance, as I have only been using SSIS for a couple of weeks. I'm trying to create a data warehouse using two input tables. A column needs to be added to one table by using a lookup into the second table. SSIS seems to handle the "no matches" and "single match" cases perfectly. I can't for the life of me figure out how to properly handle multiple matches. SSIS defaults to the first match, but I need to compute the "best" match.
I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches. This is my code:
select d.lwia,
cast((d.closegoal + d1.opengoal) as float)denominator
from
(
select yg.lwia,
cast(count(yg.appid)as float) closegoal
from dbo.wiayouthgoals yg
where yg.lwia = @RWB
-- Attained a goal in the timeframe timely or untimely
and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed
-- Goal due but not attained
or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaingoal <> 1))
group by yg.lwia
)d,
(
-- Closure with open goal
select cast(count(yg.appid)as float) opengoal
from dbo.tbl_caseclosure cc,
dbo.wiayouthgoals yg
where yg.appid = cc.col_idnum
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed
This morning we had an issue with a simple join between 2 tables e.g. table1 and table2. Both tables have 1 column called 'fielda' with datatype varchar.
Let's assume the following values: Table1: hello Hello
Table2: hello
This statement joins the tables: select a.fielda from table1 a inner join table2 b on a.fielda = b.fielda
It returns: hello Hello
???????????????????????????????????????????????????????? What the $#@#$ Why doesn't it return 1 value: hello
Any ideas? The outcome is exactly what we want but I would expected to have to use the following: select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)
What if I wanted to return all EXACT matches? Then what? Change collation?
Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...
something like
SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT); INSERT INTO #ABC VALUES ('2310947',110), ('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned.
If it possible to have an if statement match multiple results, as to not have to use the OR multiple times.
Example: I want to say, if Description equals red or blue or green or yellow or orange or black or white or pink, without having to use OR and OR and OR. Description can match 10 different values.
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile). MATCHED: (email, firstname, mobile) But not MATCH: (lastname, phone) MATCHED: (email, firstname, lastname) But not MATCH: (phone, mobile)
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
FROM (Orders LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID]) INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID, [Orders Subtable].Quantity, Receiving.Quantity, Orders.[Project #], [Orders Subtable].On_Order, [Orders Subtable].[Component #], Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND (([Orders Subtable].On_Order)=True) AND (([Orders Subtable].[Component #])="R02101A") AND ((Receiving.[Component #])="R02101A"));
While I am trying to install SQL server 2005 I get the following message - "Machine does not have a product that matches the installation package". The installation does not occur.
I am using Windows 2000 Professional SP4 and using 32 bit installer - SQLServer2005SP2-KB921896-x86-ENU.exe
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.
(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)
In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.
Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?
I am trying to clean up an old database someone developed. I took the data and grouped the information that matched I then imported it into a trips table. The matching data is all in the where statement. It shrunk 16k records for just under 2k records. Now I am trying to right a query to relate the 2 tables and update the tripid# to the triplist of people who actually attended.
Declare @Trip varchar(500)
Declare @Destination Varchar(500)
Declare @LeaveDate varchar(500)
Declare @LeaveTime Varchar(500)
Declare @ReturnDate varchar(500)
Declare @ReturnTime varchar(500)
Declare @Comment varchar(500)
Declare @RescheduleDate Varchar(500)
Declare @Rec int
Select @Rec = Rec# From Trips
Select @Trip = Trip from [Trips] where Trips.rec# = @Rec
Select @Destination = Destination from [Trips] where Trips.rec# = @Rec
Select @LeaveDate = LeaveDate from [Trips] where Trips.rec# = @Rec
Select @LeaveTime = LeaveTime from [Trips] where Trips.rec# = @Rec
Select @ReturnDate = ReturnDate from [Trips] where Trips.rec# = @Rec
Select @ReturnTime = ReturnTime from [Trips] where Trips.rec# = @Rec
Select @Comment = Comment from [Trips] where Trips.rec# = @Rec
Select @RescheduleDate = RescheduleDate from [Trips] where Trips.rec# = @Rec
Update TripList
SET tripid# = @Rec
WHERE (Trip = @Trip) and (Destination = @Destination) and (LeaveDate = @LeaveDate) and (LeaveTime = @LeaveTime) and (ReturnDate = @ReturnDate) and (ReturnTime = @ReturnTime) and (Comment = @Comment) and (RescheduleDate = @RescheduleDate)
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Hello! Please help me to find web page where guys who passed MS exams share their experience. I remember it should be like BenyenVine or very close... Thank you, Elena
Everytime I search for this text, it seems to pick up stuff beginning with 5, not dash 5. Does something need to be escaped and how would this be done?
Sometimes the simplest things are the most difficult... I'm creating a UDF as below, then executing it but all I get is that the object does not exist. I must be missing something very basic here...
CREATE function dbo.GetColumnLength(@vcTableName varchar(50), @vcColumnName varchar(50)) returns smallint as begin declare @intLength as smallint select @intLength=sysC.prec from syscolumns sysC, sysobjects sysO where sysC.Id = sysO.Id AND sysO.xtype ='U' And sysO.Name = @vcTableName AND sysC.Name = @vcColumnName return @intLength End GO select top 2 * from player, dbo.GetColumnLength('playerdetails','email')
Can someone help on this:I am just learning, and I'm connecting to the the northwindcs.mdf tables /open file is northwindcs.adp.This is the sample installed using msde, which is supposed to be mini sqlserver to learn.Please don't refer me elsewhere, here is what I'm trying to learn:If I want to hit a command button and do the following:1. Find a customerid2. if found, edit the record, if not found, add a new record.How would the below code need to look for this, I'm not even sure theconnection string is correct.I'm getting following error:run-time error 3219operation not allowed in this context.I get the y messagebox, but rst!ContactTitle = "The Owner" doesn't work.When I hit the debug, rst.close is highlighted.Also, how do you handle a no find situation here, I noticed a nomatchdoesn't work.I am real good at programming, but new to the server thing.And finally, is there a way to hit this command button, and do all from astored procedure instead of code? But in background, no user inteventiononce button is hit. Which is better, this code approach or a possiblestored procedure.Please help, if I get this down, I think I'll have the rest wipped. Theconnect string is one big thing confusing me along with handling record oncefound / not found. I'm used of DAO. If some one is willing to help, I canemail detailed real code from a database I'm really working on. I need tolearn this first to convert code.HERE IS SAMPLE CODEPrivate Sub Command16_Click()Dim cnn As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim mark As VariantDim count As Integercount = 0cnn.Open "DSN=NorthwindCS; Provider=SQLOLEDB;Data Source=OEMCOMPUTER;InitialCatalog=NorthwindCS; uid=sa; pwd=;"rst.Open "SELECT * FROM Customers", cnn, _adOpenDynamic, adLockOptimistic, adCmdText'rst.Open "SELECT CustomerID FROM Customers", cnn, _' adOpenDynamic, adLockReadOnly, adCmdText' The default parameters are sufficient to search forward' through a Recordset.rst.Find "CustomerID = 'CHOPS'"If rst!CustomerID = "CHOPS" ThenMsgBox "y"rst!ContactTitle = "The Owner"ElseMsgBox "n"End If' Skip the current record to avoid finding the same row repeatedly.' The bookmark is redundant because Find searches from the current' position.'Do While rst.EOF <> True 'Continue if last find succeeded.' Debug.Print "Title ID: "; rst!CustomerIDcount = count + 1 'Count the last title found.'mark = rst.Bookmark 'Note current position.'rst.Find "CustomerID = 'CHOPS'", 1, adSearchForward, mark'Exit Do'Looprst.Closecnn.CloseDebug.Print "The number of business titles is " & countEnd Sub
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
