Transact SQL :: Transform Duplicate Rows From Query Results To One Row

Jun 16, 2015

I have three tables, Accounts, AccountCustomer and Customers, and the data-relationshiop between are defined according to the image below:

I created also a query (the sql-query below), displaying the customers for every account that is on the table "Accounts", and I got the results, as we can see in the image below:

SELECT A.AccountID,
c.CustomerNo,
c.Surname,
c.Name,
c.TaxNum
FROM Accounts A
left join AccountCustomer ac on ac.AccountID = A.AccountID
left join Customers c on c.CustomerNo = ac.CustomerNo
order by A.AccountID;

As we understand, an "AccountID" have multiple customers, so I want to transform tha multiple results to one row, grouping by AccountID (one account belongs to one or many Customers), like the image below:

I tried to use row_number()-expression to get this, but I didn't make it. So my question is, how can I alter my sql-query to get the final result like image above?

View 6 Replies


ADVERTISEMENT

Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?

Nov 30, 2007



I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.

each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.

Ideas?
Thanks,
Ad.

View 7 Replies View Related

Transact SQL :: Delete Duplicate Rows From Table?

Jul 21, 2015

I have a table with more than 1000 records. The columns look like this

Import id   |   Reference   |  Date  |  Username  | Filename

Some of the rows are duplicates except for the fact that they have different values for the first column alone (import id)

11111      test   21-07-2015   xxxxxx   yyyyyy
22222      test   21-07-2015   xxxxxx   yyyyyy

Is it possible to delete these kind of duplicate rows by ignoring the import id ?

View 9 Replies View Related

Transact SQL :: How To Compare Duplicate Rows Within A Unique Process

Nov 9, 2015

We write to a log file each time a job runs. We give each job a unique batchid. I want to compare the run times of each step/record between two batch ids: '20150101888' and '20150101777'. Column Mins in the number of minutes each step ran. I am having trouble comparing the rows that have generic process and stepname – Trans Switch in this example. A new process within a batchid starts with a 'XX', 'Load'.

So I want to compare CA's Trans to CA's Tran Switch and ER's Trans Switch to ER's, etc. There can be multiple Trans Switch per process. There should be the same number between each batch, but no guarantees that  something might change. Also, Trans Switch is not the record right after the new process (CA, ER) in production.

I have just made a very simplified example.

/**
Want to compare 20150101888 to 20150101777 and end up with this result set. Notice that the duplicate process/step within a process has the process (CA and ER in this example) and a sequential number added to it: 'CA Trans 1'. Need this to pull out the largest time differences.

Time difference, process, step, mins1, mins2, batchid1, batchid2
-6, CA, Load, 17, 23, 20150101888, 20150101777
0, CA Trans 1, Switch, 8, 8, 20150101888, 20150101777
-6, CA Trans 2, Switch, 9, 15, 20150101888, 20150101777
-4, ER, Load, 7, 11, 20150101888, 20150101777
-4, ER Trans 1, Switch, 7, 11, 20150101888, 20150101777

**/

[Code] ....

View 4 Replies View Related

Transact SQL :: Temp Table - Display Duplicate IDs In Multiple Rows

Jun 30, 2015

I have a temp table with the following columns and data 

drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)

[Code]....

I use the rank function on that table and get the following results

select rank() over (order by DLR_ID,KPI_ID,BRND_ID  ) Rown,* from #temp

I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)

View 7 Replies View Related

Duplicate Query Results

May 15, 2001

I have duplicate results in from my query. I am using the distinct clause but this does not seem to be working. In the example below, I do not want the 79.15 to repeat multiple times eventhough the first column has all distinct values. I am using distinct in my select but this is not stopping the duplicates on the second row. Any guidance would be greatly appreciated. Thanks

820.2179.15
820.2279.15
997.379.15
48679.15
038.979.15
428.079.15
263.979.15
276.579.15
250.0079.15
276.879.15
401.979.15
396.379.15
397.079.15
31179.15
365.979.15
E88579.15
E849.779.15
663.3173.59
646.6173.59
599.073.59
V27.073.59

View 3 Replies View Related

Transact SQL :: EXCEPT Not Showing The Results - Zero Rows Returned

Sep 10, 2015

I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.

View 5 Replies View Related

Looping Query Results - Show All Duplicate Records

Feb 4, 2015

Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..

SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR

View 5 Replies View Related

Need To Get Rid Of Duplicate Rows In A Query

Dec 12, 2007

Hello I am fairly new to SQL and having spent much time over the manual I decided to ask for help. So here's my deal.

I've got a query with 5 tables that I join together


Code:

SELECT * FROM Map
INNER JOIN ThreatCategory
INNER JOIN Threat ON
ThreatCategory.threatCategoryID = Threat.threatCategoryID
INNER JOIN Threat_Map
ON Threat.threatID = Threat_Map.threatID
ON Map.mapID = Threat_Map.mapID
LEFT JOIN person on map.contentPersonID = person.personID
WHERE (((DATEDIFF(dd, Map.dataAcquisitionDate, GETDATE()) > map.goodForDays) and (map.expired = '1'))
or (map.expired = '3'))



The problem is the table Threat_Map is a many to many mapping between the Map table and the Threat table. Eg) A map can have more than one threat and a threat can have more than one map. I know this is not the best way to have a database set up but its out of my hands as to changing the database. What I need help with is this.

My application checks as to whether a certain field in the Map table is expired or out of date (as in the query). If so it gets some required information from the other tables using those joins. However, I don't want to get information for the same Map.mapID that's expired twice. I don't really care which ThreatID I get from the Threat_Map table I just need to get one of them to meet the objects standards. However, so far this seemingly simple task has eluded me. I'd like to do this in SQL. Is there perhaps a way to do this. If not I guess I'll just take care of it in the application.

-Alex

View 1 Replies View Related

Query Returning Duplicate Rows

Oct 6, 2004

I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.

SELECT
DrawingVouchers."PlayerID",
DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID",
CDS_PLAYER."LastName", CDS_PLAYER."FirstName",
CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1"
FROM
{ oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON
DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID")
INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON
DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID")
INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON
CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"}
WHERE
DrawingVoucherNumbers."VoucherNumber" >= 37806 AND
DrawingVoucherNumbers."VoucherNumber" <= 37813

View 2 Replies View Related

Transact SQL :: How To Prevent Duplicate Records In Query

Nov 18, 2015

How can I prevent duplicate records in the query

With
Property AS
(
SELECT
*
FROM dbo.MulkiyetBase
),
Document AS
(
SELECT

[code]....

View 4 Replies View Related

Transact SQL :: Query Is Trying To Insert Duplicate Record?

May 18, 2015

My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.

-- Inserting new preferred supplier into R5CATALOGUE

DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG,
CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)

[code]....

View 5 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

To Eliminate Duplicate Rows From The Result Of The Query

May 30, 2007

Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join
on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe
row for the person and the different categories he belongs to can come up in single field as comma sepatated string
Now the results are like this:
firstname lastname adress category
abc           xyz          aaa       a
abc          xyz           aaa       b
abc          xyz           aaa        c
I want like below:
abc         xyz           aaa       a,b,c
I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..

View 7 Replies View Related

Transact SQL :: Query To Combine Each Duplicate Found Entry

Nov 19, 2015

I am wanting to create a query so that I can combine each of the found duplicates into one entry.

An example of this is:

Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059

In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:

Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059

Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.

View 2 Replies View Related

Transact SQL :: Use Query Results As Select Criteria For Another Query

Jul 10, 2015

I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.

While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.

View 6 Replies View Related

How To Remove Duplicate Rows From Full Join Query

Jan 26, 2008

I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- 
AID     BID      BNAME          DDATE                                   DAUTHOR
1          1          abcxyz              2008-01-20 23:42:21.610        c@d.com
1          1          abcxyz              2008-01-20 23:41:52.970        a@b.com
1          2          xyzabc              2008-01-21 00:17:14.360        c@d.com
1          2          xyzabc              2008-01-20 23:43:17.110        a@b.com        
1          2          xyzabc              2008-01-20 23:42:43.937        a@b.com
1          2          xyzabc              NULL                                      NULL
2          3          pqrlmn              NULL                                      NULL
2          4          cdefgh              NULL                                      NULL 
Now, I want unique rows from the above result set like :- 
AID     BID      BNAME          DDATE                                   DAUTHOR
1          1          abcxyz              2008-01-20 23:42:21.610        c@d.com
1          2          xyzabc              2008-01-21 00:17:14.360        c@d.com
2          3          pqrlmn              NULL                                      NULL
2          4          cdefgh              NULL                                      NULL 
I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
 

View 8 Replies View Related

Transact SQL :: How To Get Query Results To CSV File

Sep 16, 2015

After running a query (from the Query Builder) in SQL Server 2008 sometimes I can right-click on the results pane and "Save results as CSV file", other times it's not an option.  After running a query for 24 hours (several million record results) I can't seem to do anything with the results. I have my settings:

Options | Query results | SQL Server | Default Destination for Results

set to "Results to File" and a path entered, but it doesn't work.  Is there wording I can add to the end of my SQL statement such as "TO FILE xxx.csv" or something?

View 4 Replies View Related

Full-text Query (Freetexttable) Returning Duplicate Rows

Aug 31, 2007

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:




Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC






vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

View 4 Replies View Related

Transact SQL :: Send Email Only If Query Contains Results

Aug 8, 2015

I’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.

I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.
 
My code less the personal information
  
EXEC msdb.dbo.sp_send_dbmail     
@profile_name
= '',
       @recipients
= '',
       @subject
= 'Database Integrity - Import Errors',

[Code] ....

View 5 Replies View Related

Transact SQL :: Generating XML (serialized) From Query Results

Aug 20, 2015

I didn't work with XML before and so I'm posting this question on how we can generate serialized XML. I have the following table -

DECLARE @Population TABLE (CountryId INT IDENTITY(1,1), CountryName VARCHAR(15), StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryName, StateName, PopulationCount)
VALUES ('USA','California',300000),
('USA','Chicago',500000),
('Australia','Queensland',550000),

[Code] ....

Please note that I can have another country with 10 states or 30 states, so State Name is dynamic. Can this be done in SQL ? or we have to use .NET ?

SQLServer2014
-12.0.2000.8(X64)

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

Column In Query Results To Number Rows Sequentially

Apr 20, 2015

I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?

View 6 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Transact SQL :: Query Distinct Results Based On Date Range

May 21, 2015

I have a data structure like this

UID , Name, amount, start date                               End Date
     1      A         10         2015-05-01 00:00:00             2015-05-01 23:59:59
     2      A         10         2015-05-02 00:00:00             2015-05-02 23:59:59
     3      A         10         2015-05-03 00:00:00             2015-05-03 23:59:59
     4      A         10         2015-05-04 00:00:00             2015-05-04 23:59:59
      5      B         10         2015-05-05 00:00:00             2015-05-05 23:59:59

[code]...

View 5 Replies View Related

Transact SQL :: Query To Give Results Of All Configured Alerts Occurring History

May 14, 2015

We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?

View 9 Replies View Related

Transact SQL :: Execute Select Query By Fetching Results Form Excel Sheet?

Jul 9, 2015

I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying

"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;

View 5 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases.  Each test case can have multiple bugs associated to it.  I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription  BugID BugStatus
1                TestCase1                       1      Closed
2                TestCase1                       2      Open
3                TestCase2                      11     Closed
4                TestCase2                      12     Closed
5                TestCase2                      13     Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

Transact SQL :: How To Transform Data

Jul 30, 2015

how I can transform this input (in SQL Server 2008 R2)

SiteID    EdateTime
L5500     2015-07-30 05:00:48.000
L5500     2015-07-29 05:00:49.000
To be…
SiteID    StartDate                                  EndDate
L5500     2015-07-29 05:00:49.000         2015-07-30 05:00:48.000

View 6 Replies View Related

Transact SQL :: Filter - Join Query Rows

Nov 14, 2015

Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).

SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty  as CastedQty, MI.CounterStartup 
FROM        MouldItem MI
JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty
FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid

[Code] ....

View 2 Replies View Related

Transact SQL :: Query To Make Rows To Columns

May 18, 2015

I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)

name language
-------------
ron   english
ron   french
ron   spanish
andy  english
andy  hindi
kate  english

Desired output

name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron  english   french    spanish
andy english   hindi
Kate english 

View 4 Replies View Related

Transact SQL :: Split And Transform Words

May 1, 2015

I have an application I write a textbox: 'SQL SQL' MICROSOFT 'SQL SQL'When I click a button, I wanted to receive as stored procedure parameter:@ String = 'SQL SQL "OR MICROSOFT OR MICROSOFT OR' SQL SQL '.

View 2 Replies View Related

Transact SQL :: Query To Identify Overlapping Date Rows

Aug 18, 2015

I have a table with multiple rows per staff person.  Each of these rows has staff_id, start_date, and end_date.  Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the start_date and end_date of a different row, then I have to flag these records as being identical. 

How can I do this?  I have tried doing a Cross Apply because I thought that would do Cartesian product (comparing every row), and I've also tried temp tables. But I haven't gotten either of these to work. Here is some dummy data:

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#staff_records')
) DROP TABLE #staff_records;
create table #staff_records
(
staff_id varchar(max),

[Code] ....

View 12 Replies View Related







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