SQL Server 2012 :: Get XML Output For Multiple Tables With 1:M Joins?

Jan 22, 2014

Assume i have 3 tables

Person(personname,age)
Children(childname,personname)
car(carname,personname)
A persone can have multiple cars
A person can have multiple children

Its not possible to display the results in SQL rows and columns.

If i try to it will give

PersonName Carname Childname
Sachin Audi C1
Sachin Maruti C1
Sachin Audi C2
Sachin Maruti C2

Instead of writing seperate queries the application wants to receive an xml output as follows

<person>
<pname>sachin</pname>
<car>audi</car>
<car>bmw</car>
<cname>c1</cname>
<cname>c2</cname>
<person>

How to get this output ?

View 1 Replies


ADVERTISEMENT

Joins In Multiple Tables

Dec 7, 2013

Table 1:

id amount
1 100
2 200
3 300
4 400

Table 2:

id amount
1 100
1 100
2 200
3 300
4 null

Table 3:

id amount
1 null
2 200
2 200
3 300
3 200
4 null

id is common for each tables , how can i get output like this:

Collapse | Copy Code

id t1 t2 t3
1 100 200 null
2 200 200 200
3 300 300 500
4 400 null null

I am stuck with this query .

View 1 Replies View Related

Joins Between Multiple Tables?

Feb 4, 2015

I have a general question concerning joins. Below is a table scenario:

SELECT *
FROM TABLE_A T0
INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column]
LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]

Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?

View 1 Replies View Related

SQL Server 2012 :: Foreign Key References Multiple Tables

Feb 12, 2014

Is there any possibility to create a foreign key references more than one tables.

say for example,
Create table sample1(id int primary key)
Create table sample2(id int primary key)

Create table sample3(
id int primary key,
CONSTRAINT fk1 FOREIGN KEY REFERENCES sample1 (ID),CONSTRAINT fk1 FOREIGN KEY REFERENCES sample2 (ID))

this shows no error while creating, but in the data insertion it shows error..

View 8 Replies View Related

SQL Server 2012 :: Joining Three Tables With Multiple Record?

Dec 9, 2014

We have Three Tables in sqlserver2012

Master Table

OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz

Child1 Table

OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))

Child2 Table

PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White

We want to have result like

OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow

1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red

I have tried

;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum
From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)

[code]....

it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record

View 2 Replies View Related

SQL Server 2012 :: Selecting Records From Multiple Tables?

Jul 1, 2015

i have this query in a proc

declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,

[code]....

where @reasons and @departure_code can be multiple.

View 2 Replies View Related

SQL Server 2012 :: Select Large Data From Multiple Tables

May 10, 2014

In a Library Management database we have these tables

1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc

In document table I have 500,000 records.

I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?

View 3 Replies View Related

SQL Server 2012 :: XML Import Into Multiple Tables With Data Translation

Jul 17, 2014

I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....

I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.

The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.

The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.

It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.

View 5 Replies View Related

SQL Server 2012 :: Count From Multiple Tables For Same Column Exists In Database?

May 19, 2014

i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

View 9 Replies View Related

SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables

Jan 15, 2015

I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now

Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1

Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.

Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1

I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.

View 9 Replies View Related

SQL Server 2012 :: How To Perform Update Query That Involves Multiple Tables

Aug 31, 2015

I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:

SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an

[Code] ....

I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?

The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.

View 9 Replies View Related

Multiple Joins In SQL Server

Jan 4, 2008

Hi I have a requirement where in i haev to convert the SQL from Oracleto the one which will run on the SQL server.in the Oracle Query i am doing multiple joins, between some 13 tables.and some of these joins are inner joins and some are Left outer joins.table1 inner joined with table 2table2 inner join with table3table2 inner join with table4table2 left join with table5table5 left jin with table6table6 left jin with table7table7 left jin with table8table8 left jin with table9Any idea how to achieve this??Tianaren

View 2 Replies View Related

SQL Server 2012 :: How To Reduce Joins In Query

Nov 13, 2013

I have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.

View 9 Replies View Related

SQL Server 2012 :: Null Values With Joins?

Aug 14, 2015

CREATE TABLE A (ID INT IDENTITY (1,1))
CREATE TABLE B (ID INT, EMPID VARCHAR(10))
INSERT INTO A DEFAULT VALUES
GO 5
INSERT INTO B VALUES (1,'E23')
INSERT INTO B VALUES (1,'E24')
INSERT INTO B VALUES (2,'E23')

from the above code i would like to get output like

ID EMPID
1 23
2 23
3 null
4 null
5 null
1 24
2 null
3 null
4 null
5 null

I'm trying like

select a.id, b.empid from (
select * from a cross join (
select distinct empid from b) b
) a

left outer join b on a.id = b.id but i get repetitive rows.

View 7 Replies View Related

JOINS To Sub-Queries -vs- JOINS To Tables

Aug 11, 2005

SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig

View 3 Replies View Related

SQL 2012 :: Join Multiple Tables

May 12, 2014

I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.

i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records

I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below

SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],

[code]....

View 4 Replies View Related

SQL 2012 :: Error On Join With Multiple Tables?

Sep 22, 2014

I am trying to pull in columns from multiple tables but am getting an error when I run the code:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "a.BOC" could not be bound.

I am guessing that my syntax is completely off.

SELECT
b.[PBCat]
,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]

[Code] ....

View 2 Replies View Related

SQL 2012 :: Truncating Multiple Tables (with Dependencies)

May 11, 2015

I got a request to truncate some tables on our testing servers.There are only 11 tables and i could go in and truncate them one after the other, but i need a script that i can use to truncate all the tables at a go. The tables also have no dependencies between them. A script to accomplish the same task if the tables had dependencies ...

View 4 Replies View Related

SQL Server 2012 :: How To Get Most Recent And Oldest From Joins To A Child Table

Jul 23, 2014

I have the following tables in my DB

Employee table - This table has EmployeeID, Name, DOB.

EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.

EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.

I want my query to output me the following fields...

EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary

Here is a piece of code to generate sample data sets

DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')

DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')

[Code] ....

Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.

selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,

[Code] ....

View 5 Replies View Related

SQL 2012 :: Delete Old Data From Multiple Tables Before Inserting New

Jun 9, 2014

My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.

For this one I need to write 4 individual delete statements.

Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?

[URL] .....

I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error

View 2 Replies View Related

SQL 2012 :: Multiple Joining Tables - Duplicate Records

Jul 14, 2014

I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.

select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,

[Code]....

View 2 Replies View Related

SQL 2012 :: Create View From Multiple Tables That Have 1 To M Relationship

Jul 31, 2014

I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.

for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score

and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error

Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );

I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.

View 5 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

Mar 2, 2015

I have the following results:

ID, Office1
1, Testing
1, Hello World

What i am trying to do is to get this result:

ID, Office1, Office2
1, Testing, Hello World

how i can accomplish this task.

View 3 Replies View Related

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.


My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related

SQL Server 2012 :: Format Output As Date

Feb 28, 2014

I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.

, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'

View 3 Replies View Related

SQL Server 2012 :: Show Empty Output With 0?

Oct 7, 2014

I have a sql query that gets the count of exams held in each month.

Below is the code that I have used.

select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid

The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.

I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.

I tried using case but it does not work.

View 6 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

SQL Server 2012 :: Comma Separated Column Output

Nov 19, 2013

I need a query which results the output as comma separated

for example ,

select name from tablename;

say for example this produces the following,

Name
Jack
John
Smith

I need the follwoing,

Jack,John,Smith

View 2 Replies View Related

SQL Server 2012 :: Query For Comma Delimited Output

Jan 29, 2014

Below is the sample data

create table #sample
(name varchar(100),
id int)

insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')

I need output as attached ...

View 5 Replies View Related

SQL Server 2012 :: How To Go To Next Line In Output And Print A Table

Aug 13, 2014

I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:

This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'

How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'

Also how can I create a table in this variable, something like this:

(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME

(Course Name) (Last Completed) (Now due in Month/year)

My T-SQL code:

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)

[Code] ......

View 2 Replies View Related

SQL Server 2012 :: Dual Purpose OUTPUT Parameter

Oct 21, 2014

Assume I built a stored proc (dbo.testproc) that will return the OUTPUT parameter @RandomInteger.

I could pass a specific value for the parameter...

EXEC dbo.testproc 7

Or I could return a value from the proc...

DECLARE @ReturnInteger
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger

But I want to do both which, if this actually worked, might look like this...

DECLARE @RandomInteger
SET @RandomInteger = 7
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger

I want to pass a specific value and return the result from the proc. Do I need to use two parameters for this?

View 7 Replies View Related

SQL Server 2012 :: OUTPUT Clause Returning Wrong Row?

Nov 6, 2014

I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.

Consider this script:

declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)

select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e

[Code] ....

The code works -- that is, the duplicate row is deleted. However the output clause returns:

abcdern
123451

So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?

View 9 Replies View Related







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