Query To Find Matching Result?

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


ADVERTISEMENT

Pattern Matching Or Find And Replace In SQL Query

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

How To Find Matching Profiles?

May 6, 2006

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

View 7 Replies View Related

Find Matching Sets Of Rows

Apr 11, 2006

Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a

View 7 Replies View Related

How To Find Rows With Matching Numbers In One Table

Oct 19, 2007

Hello All,

I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.

I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.

Any help will be appreciated.
Thanks,

View 7 Replies View Related

Transact SQL :: Find Parts Matching In Inventory

Sep 15, 2015

We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it.  The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number.  Below is what I have so far but it only matches exact part numbers.  One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match.  Both PartNumbers are varchar(30). 

SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake
FROM dbo.PartsInventory INNER JOIN
dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN
dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN
dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake
WHERE (dbo.RepairOrderLines.RecordID = 46001)

View 3 Replies View Related

How Can I Find The Matching Table From A Group Of Tables? (difficult To Explain...)

Aug 9, 2007

Let's say I have a list of IDs called EntryID and each EntryID can belong to ONE table out of a group of six, what is the best way to get a listing of these?

For example:

select r.*
from #Reminders r
left join mytable1 mt1 on (r.EntryID = mt1.EntryID)
left join mytable2 mt2 on (r.EntryID = mt2.EntryID)
left join mytable3 mt3 on (r.EntryID = mt3.EntryID)
left join mytable4 mt4 on (r.EntryID = mt4.EntryID)

As you can see, #Reminders has one field called EntryID (and many rows).

In my example above, only ONE of those tables will actually be able to join but I have no idea which one has the matching EntryID.

What is the best way for me to do this? I want to grab "ReportStatus" from the corresponding "mytable"... (each "mytable" has a ReportStatus column)

View 5 Replies View Related

T-SQL (SS2K8) :: Find Matching Phone Of Person Based On Relation Type - Duplicates

Aug 11, 2014

I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.

Below is the schema structure:

Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)

The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).

View 9 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

Find Next Value And Store The Result With Incremental

May 27, 2008

Please help!
Please look at the sample data below; I need help to change the value of data from (Before) - (After)

Before
Station Cycle_Index
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1


When Station = 'D_CC' then Cycle_Index suppose to start a new cycle. So my problem is trying to find the next 'D_CC' and store the incremental cycle # in a New_Cycle column.

After
Station Cycle_Index New_Cycle
D_CC 1 1
C_CC 1 1
C_CV 1 1
REST 1 1
D_CC 1 2
C_CC 1 2
C_CV 1 2
REST 1 2
D_CC 1 3

Thanks mucho for your help!

Updated:
Sorry to confuse you by changing the text color!

Case when Station = 'D_CC' then Cycle_Index = Cycle_Index + 1; based on the prior cycle # and continuos to find next 'D_CC' until the end; regardles whatever in between the prior D_CC to next D_CC.

Bottom line is searching the next value from Station = D_CC then cycle # in Cycle_Index column need to be incremented by 1 and stored the new cycle # in New_Cycle column as the actual cycle.



View 5 Replies View Related

SQL Query For Matching

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

Need Help With SQL Query. Keyword Matching.

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

A Query That Chooses Non-matching Items

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

Need A Query To Return Rows Containing ALL Matching Fields

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

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 View Related

Returning Matching/Non Matching Records

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

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Same Query Gives Result With Different Column Sequence When Used In Query Analyzer

Feb 25, 2012

When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.

result in Excel 2003 SQL OLE DB query

col-A col-B col-C
values...

Result with Query Analyzer and VBA Macro

col-c col-B col-A
values...

View 3 Replies View Related

Re-display Result Set Without Re-running Query In Query Analyzer?

Apr 9, 2006

I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View 6 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Return Result From One Query As A Column In Other Query

Jun 3, 2004

I'm having a bit of a trouble explaining what I'm trying to do here.

I have 3 "source" tables and a "connecting" table that I'm going to use

tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)

They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID

One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:

ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]

I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)

Kirikiri

View 1 Replies View Related

Save Query Result At CSV Or TXT File From The Query

Dec 10, 2007

We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.

Thanks
Amit K Patel

View 7 Replies View Related

How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).

Jan 28, 2008

Hi, 
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. 
I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC 
Whose result set is as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  
On modifying the query to:- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC 
I get the result set as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
4
Swings
p@q.com
2008-01-27 15:12:51.000

I want the result set as follows:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO  
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
 

View 7 Replies View Related

Getting Result Of Query Inside Another Query

May 22, 2007

Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.

View 4 Replies View Related

How To Get Sum() Result In Query?

Mar 15, 2008

mail_delivery_master----------------------ml_id       ml_from_mail_id                          ml_to_mail_id                                ml_user_id      ml_subject                     ml_content             ml_file_attached  ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1           aa                                       bb                                                cc              dd                             ee                                                                                                                                                                                                                                                                            2           joe@hotmail.com                          tt@gmail.com,                                   JOHNYJP         Forum answer                   somebody answer          0                1                                                                                                                                                                                                                                                                      3           joe@hotmail.com                          mailme@hotmail.com,                             JOHNYJP         Forum answer                   somebody answer          -1               0                                                                                                                                                                                                                                                              4           joe@hotmail.com                          mailme@hotmail.com,janani@ajsquare.net,         JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                             5           joe@hotmail.com                          janani@ajsquare.net,                            JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                               6           joe@hotmail.com                          dff@gg.com,                                     JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                           7           joe@hotmail.com                          tt@gmail.com,janani@ajsquare.net,               JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                             8           Durai@gamial.com                         yogesh@gamail.com                               durai           test                           test                      0                0                                                                                                                                                                                                                                                                9           janani@ajsquare.net                      devi@ajsquare.net                               janu            test                           hi                        0                0                                                                                                                                                                                                                                                             10          janani@ajsquare.net                      devi@ajsquare.net                               JANANI          test                           Miss u                   -1               2                mail_attachementsml_id       ml_file_name                 ml_file_format       ml_file_size      ml_file_select_from                                                                                  ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1           chocolate                       .jpg                         114528            F: empchocolate.pjg                                                                                2           Tiger Caspian Blue.jpg          .jpg                     1114407      F:imageTiger Caspian Blue.jpg                                                                      3           Autumn.jpg                      .jpg                         66287        F:imageAutumn.jpg                                                                                  4           Ascent.jpg                      .jpg                           63244        F:imageAscent.jpg                                                                                  5           Azul.jpg                        .jpg                              61365        F:imageAzul.jpg                                                                                    6           daisy.jpg                       .jpg                             8197         F:imagedaisy.jpg                                                                                   7           Stonehenge.jpg                  .jpg                       59600        F:imageStonehenge.jpg                                                                              i want the result :ml_id , ml_from_mail_id, ml_to_mail_id  from mail_delivery_master   ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss      

View 2 Replies View Related

Query Result As O/p From SP

Oct 29, 2005

Hi thereI want a stored procedure that contains a Select query. I want SP that return result of query(rows)how could i do this?please help

View 2 Replies View Related

Query Result Help

May 17, 2005

Hi,all,
I ran the two queries and I thought it would be the same, but it's different.
Can you explain to me.

Query 1: result---52 rows
select s.InsuredSurname, s.email from studyUSA s
join interMedical I on s.email=I.email
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and (s.agentcode not like '162%') and (s.agentcode not like '17%')
and s.agentcode <> '130844'


Query 2: result--14 rows

select s.InsuredSurname, s.email from tis_studyUSA s
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and s.email IN (Select I.Email from tis_InterMedical I)
and (agentcode not like '162%') and (agentcode not like '17%')
and agentcode <> '130844'

Thanks!
Betty

View 5 Replies View Related

Using Result Of One Query In Another

Sep 29, 2004

hi,

i've tried searching on this but not having much luck, i'm trying to use the result of one and use it in another. is there any way to do this?

So for example, i would use the result of this query and store it in a variable called @tmp_id (if possible)

SELECT TOP 1 ID FROM tblWines WHERE RefNo LIKE 'AB1234';

then use that variable in another query

INSERT INTO tblLogWines (WineID, Date) VALUES (@tmp_id, now());

i've simplied the context to make it a little more understandable, any help is appreciated.

goran.

View 4 Replies View Related

How/what Should Be The Query For This Result

Jun 28, 2006

I have 1 table "Progress"P_no b_no status build_date----------------------------------------------------------------25 1 First_slab 2006/4/525 1 second slab 2006/5/625 2 first slab 2006/1/225 2 third slab 2006/2/3o/p should be asPno,bno, status, max(build_date)sample o/p can be as below25 1 second slab 2006/5/625 2 third slab 2006/2/3Thanks in Advance.

View 4 Replies View Related

Query: How Can I Get This Result?

Jul 20, 2005

Hello all,I tryed to simplify the problem as much as possible. I'll start with theDDL:----------------------------------CREATE TABLE #MyTable(NoID INT,Type CHAR,DateTransaction DATETIME)INSERT INTO #MyTable (NoID, Type, DateTransaction)SELECT 1 AS NoID, 'A' AS Type, '2004-01-01' AS DateTransaction UNION ALLSELECT 2, 'C', '2004-01-01' UNION ALLSELECT 3, 'B', '2004-01-01' UNION ALLSELECT 4, 'C', '2004-01-02' UNION ALLSELECT 5, 'B', '2004-01-02' UNION ALLSELECT 6, 'C', '2004-01-02' UNION ALLSELECT 7, 'A', '2004-01-03' UNION ALLSELECT 8, 'B', '2004-01-03' UNION ALLSELECT 9, 'A', '2004-01-03' UNION ALLSELECT 10, 'C', '2004-01-03' UNION ALLSELECT 11, 'B', '2004-01-03'----------------------------------What I want is all the same Type which, for a same DateTransaction, as adifferent Type inserte beetween them when data is sorted by DateTransactionand NoID. In this case I would like:Type DateTransaction------ -----------------C 2004-01-02 /* B is between two C (NoID = 5) */A 2004-01-03 /* B is between tow A (NoID = 8) */B 2004-01-03 /* A and C are between two B (NoID = 9 and10) */All of these have for the corresponding date at least one transaction with adifferent type between.In the real situation NoID is an autoincrement field, the PK. And theDateTransaction hasn't any time, just a round date.Any suggestion?Thanks for your time.Yannick

View 7 Replies View Related

Need Help With SQL Query And Result Set.

Oct 19, 2007

Good day.

I have a working knowledge of T-SQL but apparently not sufficient to solve my problem.

I have 3 tables defined as:



CREATE TABLE [dbo].[Complaints](

[ComplaintId] [int] IDENTITY(1,1) NOT NULL,

[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,

[DateCreated] [smalldatetime] NOT NULL,

[DateReceived] [smalldatetime] NOT NULL,

[Classification] [tinyint] NOT NULL,

[Misconduct] [tinyint] NOT NULL,

[Status] [tinyint] NOT NULL,

[OccurrenceDateTime] [smalldatetime] NOT NULL,

[OccurrenceCityTown] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,

[OccurrenceRegion] [tinyint] NOT NULL,

[OccurrenceCountry] [tinyint] NOT NULL,

[CreatorId] [int] NOT NULL,

[CreatedFrom] [tinyint] NOT NULL,

[Flags] [tinyint] NOT NULL,

[Summary] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,

[Comments] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,

CONSTRAINT [PK_Complaints_1] PRIMARY KEY CLUSTERED

(

[FileNumber] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]




CREATE TABLE [dbo].[ComplaintSubjects](

[ComplaintSubjectId] [int] IDENTITY(1,1) NOT NULL,

[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,

[SubjectId] [int] NOT NULL,

[SubjectType] [tinyint] NOT NULL,

[SubjectIdentity] [tinyint] NOT NULL,

CONSTRAINT [PK_ComplaintSubjects] PRIMARY KEY CLUSTERED

(

[ComplaintSubjectId] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



CREATE TABLE [dbo].[Persons](

[PersonId] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,

[MiddleNames] [varchar](64) COLLATE Latin1_General_CI_AS NULL,

[LastName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressNumber] [varchar](10) COLLATE Latin1_General_CI_AS NULL,

[AddressStreet] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressCity] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressRegion] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressCountry] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,

[AddressPostalZip] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,

[PhoneHome] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[PhoneWork] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[PhoneMobile] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[PhoneFax] [varchar](25) COLLATE Latin1_General_CI_AS NULL,

[DateOfBirth] [smalldatetime] NOT NULL,

[Status] [tinyint] NOT NULL,

[Type] [tinyint] NOT NULL,

[DateAdded] [smalldatetime] NULL,

CONSTRAINT [PK_Personnel_1] PRIMARY KEY CLUSTERED

(

[PersonId] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Basically, I track complaints and each complaint can have 1 or more subjects associated with it.
The structure is the complaint is referenced in a complaintsubject row and I use the persons table to find out detail of the complaintsubject (firstname, middlename, lastname) complaintsubject just holds Id's to normalize the database rather than have all details of persons table copied into every complaint (pretty basic so far).

My first question is regarding structure...since my complaint to complaintsubject relationship is 1 to many I assume the best way to associate the two is to have a FileNumber field in the complaintsubject linking back to a complaint. Is there any other design that is better suited to this type of relationship?

Second question and most important: I need a way to search by a string that appears in any part of the firstname, middlenames, lastname. I have the query for this and its working. But the problem arises when I have more than one match (i.e. 2 or more people are involved in the complaint where the match string fits both - example say we have a match string of 'an' and in complaint xyz 2 people involved are Sandy Blah and Andy Blue). Now when I do my join to pull out the complaints that involve any people with a name containing 'an' the result set returns the same complaint twice, one for Sandy Blah and the other for Andy Blue). Since I am only interested in certain complaint details and not interested in having the actual people involved returned in the result set, I would like to return only 1 row for each complaint when more than one match is found for the firstname,middlenames,lastname. I could live with having the duplicate rows and process them easily application side, but I am sending a lot of duplicate information over the wire for nothing and would like to optimize this.

I hope I have described this well enough and would greatly, greatly appreciate any help you can provide.

Best Regards.

View 4 Replies View Related

Getting A Query Result Into A Textbox

Apr 17, 2008

This query should always return 1 row with columns visid, cid, visdate, comment. How can I get the value of visdate in textbox1?  This already works when the query is in a vb sub but I want to know how to do it this way too.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"     ConnectionString="<%$ connection string here%>"     SelectCommand="SELECT * FROM [vis] WHERE ([visID] = @visID)">
   <SelectParameters>      <asp:ControlParameter ControlID="Label1" Name="visID" PropertyName="Text" Type="Int32" />   </SelectParameters></asp:SqlDataSource>
<asp:TextBox ID="TextBox1" runat="server" Style="left: 117px; position: relative; top: 160px"></asp:TextBox>

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved