Simple? Multple ID Matching Varied Content Query.
Feb 23, 2006
this shoud be simple, but my brain's hurting.
I have a table with 3 fields...
IDnum,content,application
eg
123,stop,fb
123,start,fb
123,continue,fb
123,fault,fb
123,start,gt
123,stop,gt
123,continue,gt
123,start,ha
321,stop,fb
321,start,fb
321,continue,fb
321,fault,fb
321,start,gt
I need to know All those unique IDs that have "start" against them, but have not got "stop" against them, I'm not interested in the other content strings.
I need to query it based on a specific application ID.
there will be many records from each IDnum, but only one start for each. I need to know all those IDnums that have "started" and not "stopped"
tia
a
View 1 Replies
ADVERTISEMENT
Apr 2, 2007
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
View 3 Replies
View Related
Jan 12, 2006
Hello All,I've got a DATETIME field, and it includes hour:minutes:second data. I want to do selects where I can simply match on the month, day and year. For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken. Any suggestions?Thanks! -Josh
View 2 Replies
View Related
Mar 23, 2008
Im just curious how i would take multiple columns from multiple tables.... would it be something like this ???
table: Products COLUMNS ProductName, ProductID
table: Categorys COLUMNS CategoryName, CategoryID,ProductID
SELECT Products.ProductName, Categorys.CategoryName,Products.ProductID,Categorys.CategoryID,Categorys.ProductID
FROM Categorys, Tables
WHERE Products.ProductID = Categorys.ProductID
View 1 Replies
View Related
Mar 11, 2004
This is the same issue as deinfed in http://www.dbforums.com/t987543.html
I've done some additional testing and got it down to the below
SQL2000 DB
Linked Server to DB2 using client access odbc, and MS OLE DB for ODBC
Varied results depending on the row size of the views.
See the below examples.
CREATE VIEW BLHDR_TEST
AS
SELECT STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNST CHAR(25)
TOTAL ROW WIDTH (25)
SELECT * FROM BLHDR_TEST
Returns 20971 rows * 25 = 524,725
CREATE VIEW BLHDR_TEST
AS
SELECT STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (37)
SELECT * FROM BLHDR_TEST
Returns 14169 rows * 37 = 524,253
CREATE VIEW BLHDR_TEST
AS
SELECT STNADD1, STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNADD1 CHAR(40)
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (77)
SELECT * FROM BLHDR_TEST
Returns 6808 rows * 77 = 524,216
CREATE VIEW BLHDR_TEST
AS
SELECT STNCTY,STNADD1, STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNCTY CHAR(25)
STNADD1 CHAR(40)
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (102)
SELECT * FROM BLHDR_TEST
Returns 5140 rows * 102 = 524,280
Test #1 Returns 20971 rows * 25 = 524,725
Test #2 Returns 14169 rows * 37 = 524,253
Test #3 Returns 6808 rows * 77 = 524,216
Test #4 Returns 5140 rows * 102 = 524,280
With the similarity of the total byte count returned, I would assume that a buffer or something is being overrun, is this a configuration parameter possibly.
If I perform the select against the linked table as such
Select * (for fields list)
From LURCH_PARADB.S102D4LM.PARADB.BLHDR
I get all rows returned. The issue only exist when accessing the views that are created against the linked server.
Any thoughts or ideas are appreciated.
View 1 Replies
View Related
Jul 26, 2007
Okay I've posted something like this in another area but it doesn't seem to be focused towards the right people. I need ya'll SQL experts!
I have a database table that has 5 columns: Time Status Message Source IP
The Status column has only two choices "up" or "down"
I'm looking for some kind of advanced query that will do the following:
I need the query in my application to pull the table and then match up IP, Status, and Message columns. It should only find One match and then once it has found a match it needs to recognize that one is an "up" status and one is a "down" status and then remove them from the database.
Does that make sense? Is it impossible?
View 1 Replies
View Related
Jul 27, 2005
Hi all,I have two tables:workgroups (wg_id, wg_name)workgroups_keywords (wgk_wg_id, wgk_keyword)Each workgroup has an associated list of one or more keywords.What I want do to at first was given a particular list of keywordsbring back a list of workgroups that have at least one matching keywordassociated with it.I have the following query:select distinct(wg_id), wg_namefrom workgroups, workgroups_keywordswherewgk_keyword in (#QuotedValueList(Keywords.wgk_keyword)#)andwg_id = wgk_wg_idorder by wg_nameThis works great.However, is there a way in a single query to order the returned rows bythe number of keywords that are found to be matching (in other words anorder by relevancy, the more keywords that match the more relevant thereturned row)?Thanks in advance.David
View 2 Replies
View Related
Nov 12, 2007
I have a column with datatype varchar(2000). When I use the select statement to query for this column I only can see the content up 256 characters. How can I query to view the entire string above 256 characters?
Thanks.
View 5 Replies
View Related
Jul 23, 2005
I've never been the most articulate person in the world, so i thoughthe best way to illustrate the script I am looking for help with, is toprovide you with two tables and the results I require.Table 1ProductName1ProductName2QtyAAAA-12BBBB-13CCCC-14Table2ProductNameQtyAA2BB-13DD6Desired ResultsProductNameQtyDD6Regards,Ciarán
View 1 Replies
View Related
Aug 10, 2015
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:
1)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate=
Table2.EndDate
2)Â
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate!=
Table2.EndDate
3)Â
Table1.Firstname!=Table2.Firstname,Table1.Lastname!=Table2.Lastname,Table1.StartDate!=Table2.StartDate,
Table1.EndDate=Table2.EndDate
Table 1 & Table 2 contains the following data:
Table1
First Name
Last Name
Start Date
End Date
City
[code]...
I want this output by writing a single query.
View 4 Replies
View Related
May 17, 2008
Hello, I'm learning about asp .net and I've created a simple application with SQLServer integration. I have 3 text boxes where I can insert text and then use them as parameters for the Select query string of a SqlDataSource which is then bound to a DataGrid.My question is: Is there any default configuration I can use with the SqlDataSource which do not add a given field as a parameter to the WHERE clause if the text box content is empty ? Sure I can sweep through all my text boxes, check if they're empty and dynamically build the WHERE clause with correct parameters, but is theres a built-in, more elegant way to solve this ?Thanks in advance.Arashikage
View 2 Replies
View Related
Oct 29, 2007
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?
View 6 Replies
View Related
Sep 9, 2004
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables).
I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z".
The "All" part is very important.
For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.
I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".
I hope I am clear enough.
Does anyone have any solutions???
View 2 Replies
View Related
Oct 1, 2015
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange AppleÂ
Peter 0 20 0
View 4 Replies
View Related
Feb 18, 2008
I have VS2008 Express and I am using Sql Server Compact Edition 3.5 which is distributed with it.
As I experienced, there is no possibility to use this database from Sql Server Management Studio (because it is version 3.5 and Management Studio demands version 3.1).
So, the only possibility to design a query is to use the built in Query Builder in Visual Studio Express. This query builder does not save the Diagram Pane content layout. I mean, when a query has 10 tables, I place these tables in the Diagram Pane in some order that makes sense (e.g. to make all relations visible). After saving and reopening the query in Query Builder, all tables are on the left side, in a complete disorder.
Is it possible to somehow save the Diagram Pane content along with the SQL ??
View 1 Replies
View Related
Feb 4, 2007
Hi All
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
View 5 Replies
View Related
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
Mar 7, 2003
Hi,
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil
Results I want:
Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1
Thanks a lot,
Heta
View 2 Replies
View Related
Aug 21, 2004
Hi,
I have a very simple question:
declare @treshold int
set @treshold = 10
SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold
I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?
I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?
thanks
View 2 Replies
View Related
Jan 17, 2002
Hi,
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
Thanks in advance for your help
Kevin
View 1 Replies
View Related
Oct 21, 2006
This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id
select
sc.sales_contact_id
, idd.issue_id
, sr.code
from
sales_contact sc
, invoice i
, invoice_line_item il
, sales_region_special_section srss
, sales_region sr
, issue_date idd
where
sc.sales_contact_id = i.sales_contact
and i.invoice_id = il.invoice
and srss.sales_region = sr.sales_region_id
and il.issue_date = idd.issue_date_id
and srss.invoice_line_item = il.invoice_line_item_id
order by
sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id
for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC
B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are
situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
thanks in advance for your help.
View 1 Replies
View Related
Jan 7, 2005
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
Code:
-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------
Would return something like:
Code:
----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
View 3 Replies
View Related
Sep 26, 2006
OLD_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____0
NEW_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____1111
(end result)
FINAL_TABLE
43434_____9258_____9496_____1111
I have two tables ... I am trying to get only the rows that are different.
View 2 Replies
View Related
Sep 19, 2005
Hi folks,
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0
if count(*) from question where serialno > $serial != 0
else
select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
View 5 Replies
View Related
Sep 1, 2006
Hi,
This is probably a simple SQL statement question, so please bear with me.
Basically I have a CustomerMaster with the following fileds
1) CustomerID
2) CustName
3) Address
4) ResTelNo
I want retreive the CustName,Address and ResTelNo if the ResTelNo has occured in the table more than twice.
Could someone please help with this. I would like to use the same query in MS Access as well.
Thanks in advance for your help.
View 2 Replies
View Related
Sep 17, 2004
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
thanks
View 4 Replies
View Related
Feb 29, 2008
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example
Current date=29/02/2008
Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
View 2 Replies
View Related
