SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are:
PersonSkills:
id, ProductID, Skills
Product:
id, ProductName
Ive been reading and playing around with this without success.
I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.
Hello, I am writing a SP, inside the SP i have a select statement. I also have another SP, when i run it, it sends a list back with all the users that are currently online. Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.
This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online): SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId
This is my second SP, in witch i want to get the online users data: SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers)) Is it possible to exec a SP into a select statement?
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.
The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B
'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table B
I have two tables in the same DB, one named “Client” one named “Case”.
-One column inside Client is “ClientID”, another is “ClientCode”.
-One column inside Case is “CaseID” and another is “ClientCode”, which corresponds to the ClientCode column in the previous table.
I need a file with output that looks like this CaseID+ClientID.
So, an example row from the “Client” table” could be ClientID = 123, ClientCode=999. An example row from the “Case table” could be CaseID=555, ClientCode=999. So, I need output of 555123 .
Scenario: 2 sets of data Set 1 (200 records): firstname, lastname, address, city, phone Set 2 (100 records): firstname, lastname, address, city, phone
I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers
However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.
select xx.firstname, xx.lastname, yy.Firstname, yy.Lastname, xx.address, yy.address, xx.city, yy.city, yy.phone from set2 xx, set1 yy where substring(soundex(xx.Firstname),0,3)=substring(soundex(yy.Firstname),0,3) and substring(soundex(xx.lastname),0,4)=substring(soundex(yy.Lastname),0,4) and substring(xx.address,1,7)= substring(yy.address,1,7) and xx.city=yy.city;
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees 1 Dave 2 Peter 3 John
Site 1 Mon John Tue Peter Wed John Thu John Fri Peter
Site 2 Mon Dave Tue Dave Wed Dave Thu Dave Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).
Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,
If I know that the final trade is:
15 $10
And I have the following trades in my set:
TradeId Qty Price 1 10 $10 2 7 $20 3 5 $10
I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.
In the real situation, the number of trades that I need to check is not set to a specific number.
Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...
Here is a sample SQL code (table and data) to work with.
i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?
I have a need to execute a query in T-SQL on a numeric field in a SQL table. However if there is no exact match I'd likea query that will return the row that is just below my value. As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned. Is this possible? Can anyone help me with such a query? Regards Clive
Find value match in SQL table Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
I'm trying to correct an error that a user is getting when running SQL Server 7.0 SP3, Desktop edition/ Windows NT 4.0 SP6. The error says that C:WINNTSYSTEM32odbcint.dll and C:WINNTSYSTEM32odbccp32.dll are different versions. I tried to install MDAC 2.5 sp1 to correct this problem but this message continues throughout the install. This is causing sqlagent service to hang. Any ideas of how to reinstall ODBC components without reinstalling the OS?
I asked this question in the mysql forums, but I am also interested in any info regarding MSSQL. I've seen databases with search systems with the functionality I seek below. I just have no idea how they are doing it. Thank you.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.
Thank you very much. This is a huge problem for us.
I asked this question about mysql on another forum, but I would also like to know about MSSQL. I may need to switch to this platform if Mysql doesn't work. Here's my problem:
Instead of "match", I am looking for a "match any portion" way to do searches with mysql.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas?
Thank you very much. This is a huge problem for us.
SQL 2000 I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.* into #cl_temp FROM OLT.dbo.NACBTR NA WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And NA.completedDate >= '01/01/2006' and NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*, ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message: The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D
same colored items are grouped by GroupID. Each group contains ItemID, Qty and rate. How can i compare IteamID, Qty and Rate of each group with other group's ItemID, Qty and rate? OR How can i get number of groups with same ItemID, Qty and rate?
I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.
Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.
Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.
Or else any other way to send an email after generating the text file ocntaining the non matching rows.
I want to match dates from two tables, and I want to match the six latest dates. If I have a match a will delete it from one of the tables. I will do a while loop and do a delete for every date that already exist. If ,for example, the third date not have a match the loop must do a break. but if all six is a match they all will be deleted from one table.
how to check it? max(date) max(date) - 1 max(date) - 2 ... Some one ho know and understand the question?
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType, @nodePKID = NodePKID, @biosVar = Bios, @computerNameVar = ComputerName, @diskVolumeVar = DiskVolume, @guidVar = Guid, @macAddressVar = MacAddress, @motherboardVar = motherboard FROM Nodes_Active WHERE case when Bios = @bios then 1 else 0 end + case when ComputerName = @computerName then 1 else 0 end + case when DiskVolume = @diskVolume then 1 else 0 end + case when guid = @guid then 1 else 0 end + case when macAddress = @macAddress then 1 else 0 end + case when MotherBoard = @motherboard then 1 else 0 end >= 4
I have a view where I am joining two very large tables.
I am joining where Brand and then Product match.
My problem is that there are quite a few rows where there is a Brand and Product in Table A but it is not in Table B and the other way around, Products in B that aren't in A and I need ALL of the products to show, not just the ones that are in both tables.
The field names are the same in each table, one is North America and the other is Europe. HOW do I write that?
SELECT dbo.SummaryNA.Brand, dbo.SummaryNA.Product, dbo.SummaryEU.Brand AS Expr1, dbo.SummaryEU.Product AS Expr2, dbo.SummaryNA.Supplier,
I've created C#.net program (behind code style). when I run it in Internet explorer, the following error occurs in IE window. pls instruct me how to handle and correct this error. And how to initialize the connectionstring... Great thank!
Server Error in '/' Application. -------------------------------------------------------------------------------- The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values into another table. IN VB this was simple but the while in TSQL seems a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays --insert values insert into table values (value1, value2) --then increment counters and repeat set @sdate = @sdate + 1 Set @cnter = @cnter + 1 How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data. I ONLY WANT a. records that are only in A but not in B b. records that are only in B but not in A ______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS AS BEGIN DECLARE @Rowcount AS INT DECLARE @First_Name AS VARCHAR(50) DECLARE @Last_Name AS VARCHAR(50) DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50)) CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK PRINT '------------------------------------------------------------------------------------------' PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------' PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Prowess WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Sdk WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
declare @var varchar(50) set @var= 'COLUMNNAME' select ID, a.@var , b.@var from rooper a join jim_rooper b on b.id = a.id join b_rooper bb on bb.id = a.id where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...
