Query To Combine Two Tables Based On Third Table

Feb 18, 2015

I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:

Table A:

Table B:

Table C:

So what query do I need write to have table like below?

View 3 Replies


ADVERTISEMENT

SQL Server 2014 :: Query To Combine Two Tables Based Third Table

Feb 18, 2015

I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:

Table A:

Table B:

Table C:

So what query do I need write to have table like below?

Table D

View 7 Replies View Related

How To Combine Two Tables (not Query)

Apr 6, 2005

I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.

I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.

First, the new flat file is missing two columns (not critical data).

Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).

I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.

Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!

View 3 Replies View Related

SQL Server 2012 :: How To Combine Data From Different Tables Using A Query

Dec 17, 2013

I have a set of tables that look like what I have shown below. How I can achieve the desired output ?

CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);

INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);

[Code] ....

I have a query currently that looks like this . @Month and @Year are supplied as parameters

SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]

[Code] ...

Right now I see an output like this : for a particular value of @Month and @Year

SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :

[Customer],SalesofProductA, SalesofProductB, SalesofProductC

How it can be done ?

View 2 Replies View Related

Write Code To Combine Two Tables And Then Return Maximum Value Of One Table

Sep 17, 2012

I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!

View 11 Replies View Related

How To Combine 2 Query From Different Table

Apr 5, 2012

I have write two query but its only work one at a time need your expertise what i am doing wrong.

[Category].Category AS project_type,
[SalesManager].SalesManager As Manager,
FROM [Category] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [Category].ID = Projects.Category
FROM [SalesManager] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [SalesManager].ID = Projects.SalesManagerID

View 2 Replies View Related

Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?

Aug 1, 2007

I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.

i have a derived table that makes the following results:



ID Status Name

2 1 "A"

2 2 "B"



I want to get the following:

ID Name1 Name2

2 "A" "B"



but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.

View 1 Replies View Related

Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?

Aug 1, 2007



I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name
2 1 "A"
2 2 "B"

I want to get the following:
ID Name1 Name2
2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

View 5 Replies View Related

Combine Text Field Based On Value

Feb 27, 2014

I have one table with text column(varchar50) and 2 value columns(INT)

it looks like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4

What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text3 ext4

I have tried to use CASE with no luck.

edit:
output like this is also fine
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
5, null, text3 ext4

View 6 Replies View Related

Create Query Based On A Field That Won't Be The Same Value In Both Tables

Jul 20, 2005

I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!

View 2 Replies View Related

Management Studio - How To Index Tables Based On Query - Please Help

Mar 20, 2007

We are using SQL2005.
I have a stored proc that runs a Select query based on a complex view.
The sproc has two input date parameters (StartDate and EndDate).
We are experiencing SQL timeout problems when the sproc is run with certain Start and End Dates.
We have run the SQL Profiler and created a trace (trc) file (We've used the 'Default' trace configuration).
We have used the trace file in SQL Server Management Studio to try and automatically create indexes on some of our tables.
Unfortunatly SQL Server Management does not make any index recommendations.
I think we are not capturing the right information in our trace file to allow SQL Server Management Studio to do its job.
How do I use SQL Profiler to capture a trace of my sprocs query, so that it can be used by SQL Server Management Studio, to recommend index changes?
Any help appreciated.
Reagrds,
Paul.

View 1 Replies View Related

Query Based On Two Temp Tables Runs Forever

Oct 28, 1998

View 1 Replies View Related

SQL Server 2012 :: Increment Query Based On Two Tables?

Feb 17, 2014

I have two tables with this info:

TABLE 1

COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB

TABLE 2

COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347

I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:

COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352

I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.

View 5 Replies View Related

Need Help To Create Tables Based On Word Doc Table

Nov 23, 2007

Hello,

I'm looking for the best solution for creating database tables based on
a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.

What I assume is I would need first to start of by creating two tables.
One table would consist of the fields of the horizontal upper most data e.g.
conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?

Any help would be much appreciated.

Thanks

Karl

View 3 Replies View Related

Summarized Report From One Table Based On Information From Other Tables

Dec 11, 2004

Hi,

These are my tables

auth:
id, level, base

info:
aid, name

store:
aid, itemID

aid is the ID that comes from the auth table.

The final output that I plan to get is

Name Number of Items

where name comes from the info table, and number of items comes from the store table.

Query something like:

show the total number of items for all those users (in the fashion Name, Number of items)from the store table, for all those whose level=something and base=something in the auth table.

For example,

if there are 10 users whom match level=something and base=something in the auth table,

What I want displayed is:
------------------------
Name Number of Items
------------------------
user1 10
user2 15
user3 20
....
user10 30
-----------------------

which if broken up is:
select name from info where aid=X
select count(*) from store where aid=X ..

for all those whose base=something and level=something from auth.


Thanks,
Shashi

View 1 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId    -    SiteId
1   -   5
1    -   9
1    -   16
1    -   18
1    -   20
1    -   27
1    -   31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

View 10 Replies View Related

Merge Two Tables Into A Single Table Based On The Matching Timestamp

Aug 2, 2013

Two tables:

table #1 = timestamp, servername1(never changes), value(int)
Table #2 = timestamp, servername2(never changes), value(int)

What I need to do is merge the two tables into a single table based on the matching timestamp:

timestamp, servername1, servername2 as headers then values under
"1/1/1-1:10:1" , "10" , "10" - as an example
"1/1/1-1:20:1" , "20" , "21" - as an example
"1/1/1-1:30:1" , "1" , "5" - as an example

Powershell or MSSQL 2008 queries work.

I have a powershell script atm that can create the table and then throw it back into the database but its very slow because of the amount of records.

The table is for some performance stat collection app.

Table #1 records a timestamp, the servername (which is the same over and over in the table) and the value of the performance object.
Table #2 is the same collection but for a different server.

The layout in the database is terrible but this is what I get to work with.

View 3 Replies View Related

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?

Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

View 5 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View 2 Replies View Related

Max Query( Based On Two Table)

Mar 19, 2007

i've two table Rate and Topic

Sample Table

Table name==>Topic
---------------------
Topic|TID|
-----|---|
hddgg|1
trgsd|2
vnvnv|3
sdfsd|4
dfsdf|5
dsdff|6
........
........
-----------------
above table TID is primary key

next table

Table Name==>Rate
-------------------
RID|TID|Rate
---|---|--------
1--|2--|4
2--|5--|3
3--|2--|3
4--|2--|2
5--|1--|1
6--|2--|1
7--|2--|2
8--|5--|3
9--|1--|4
.........
........

above table RID is primary key and TID is foreign key..

i need Top 3 Topic but based on Maximum Rate of value

any query.........

View 2 Replies View Related

Combine 2 Tables

Sep 20, 2007

I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.

View 5 Replies View Related

Combine Two Tables Into One RS

Mar 5, 2005

Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.

Here is a sample of the two databases:

Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124

Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745

I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85

I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745

Any ideas or solutions will be greatly appreciated.
Thanks

View 2 Replies View Related

Combine Tables

Jan 31, 2008

Im trying to combine 2 unrelated tables to create a new table with all the data. Both tables have the same number of rows. if i have 2 tables with 1 column each and 5 rows in each column, eg.

tb1
c1:
1
2
3
4
5

tb2
c2:
a
b
c
d
e

if i run the query

SELECT tb1.[c1], tb2.[c2] INTO tb3 FROM tb1, tb2

i get
tb3:
c1: c2:
1 a
1 b
1 c
1 d
1 e
2 a
2 b
2 c
........

how do i get
tb3:
c1: c2:
1 a
2 b
3 c
4 d
5 e



thanks,

View 9 Replies View Related

How Do U Create A Table Based On A Query

Jan 23, 2002

Hi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe

View 2 Replies View Related

Run Query Based On Values In Another Table

Mar 29, 2007

Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:Need help to do this:Open Table_AWITH TableADO UNTIL .EOFRead value from TableA.ColumnARun SQL Statement on TableB based on valueMove to the next recordLOOPENDHow do I do this in TSQL?Thanks,Bubbles

View 9 Replies View Related

Combine Tables From Previous Db To New Db (uhh...?)

Nov 2, 2003

I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.

My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?

Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6

New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?

View 6 Replies View Related

Combine 4 Tables And Get Statuses

Apr 11, 2008

If i have to get the statuses from four different tables but want to get first table disabled , second table active and also have statuses field from the 3rd and 4th table how would i do that?

ALM,
PV,
AD,
ACE

select * from all tbls
where alm.id = pv.id
and alm status = 'disabled'
and pv status = 'active'

I want to include the status field from 3rd and 4th table.

THanks

View 1 Replies View Related

Combine 2 Columns From 2 Tables

May 23, 2008

Hi,

I have 2 tables called Table A, Table B,

In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1

Now want a output like

Data1,
Data2,
Data3

Please help me to get this....

Thank you,
Senthil

View 4 Replies View Related

Transact SQL :: How To Combine 4 Tables

Jul 6, 2015

I have 4 table 

TBLA
id 
1
2
3
----

TBLB
ID COUNT
1 2
2 2
3 2
----

TBLC
ID COUNT
1 2
2 2
----

TBLD
ID COUNT
1 3
2 3

NEED TO GET OUT PUT LIKE BELOW

ID aCOUNT bCOUNT cCOUNT dCOUNT
1 2
2 3
2 2
2 2
3 2

View 2 Replies View Related

How To Combine 2 Tables Data

Oct 6, 2015

I have 2 tables. first table contains name and age and the second table contains class and roll num.  I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.

View 8 Replies View Related

Select Query - How To Get Result Based On Given Table

Sep 14, 2014

Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -

Result should be -

ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two

Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three

select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing

View 2 Replies View Related

Query Table Based On Multiple Keys

Jul 23, 2005

Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725

View 6 Replies View Related

Combine 4 Tables Without Repetitive Records

Oct 29, 2006

How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.

The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.


Thanks

View 9 Replies View Related







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