I have two tables (teachers, students) each table has 3 fields (FirstName, LastName, EMail). I'm trying to combine the data from both tables into one so I have a "master list" to send e-mails to all teachers and students via an ASP script/CDO.
I've been reading up on Joins but I can't figure out the SQL to combine data from both tables. Any ideas?
Hello all - I'm relatively new to SQL and has been struggling with the below query. Any help is greatly appreciated. Thanks.
I have the following 2 tables:
I'm trying to get the below output:
Here's the SQL I've been using: SELECT a.PERSON_ID, sum(a.AMOUNT), CASE WHEN b.JOB_CODE = 10 then "Level 2 Seller" WHEN b.JOB_CODE = 15 then "Level 3 Seller" ELSE 'Level 1 Seller' END as "Job_Code" FROM tbl_SALES a, tbl_JOBFUNCTION b WHERE a.PERSON_ID = b.PERSON_ID GROUP BY a.PERSON_ID, b.JOB_CODE
Here's what my results are with the query I've been using:
Each person could have many job codes, but when they have 10 then they're consider "level 2". - If they have 15 then they're "level 3". - If they don't have either 10 or 15 then they're "level 1". - If they have both 10 and 15 they're still "level 3".
I have database with two different tables BaseData and QualityDetails as given below with ID as the Primary key BaseData.
Sent_to_Client  Name ID 1/2/2015  ABC1 1000000 2/24/2015  ABC2 1000001 5/24/2015 ABC3 1000002 3/24/2015  ABC4 1000003
QualityDetails
ID Error 1000000 Â Â 1 1000001 2 1000002 Â Â 0 1000003 Â Â 3
I want to write an SQL Query to get the data by combining both the tables by dates from 2/1/2015 to 5/30/2015 in BaseData table. My table after executing the query should be
ID Name Error 1000001 ABC2 2 1000002 ABC3 0 1000003 ABC4 3
I have a requirment to take data from a large set of tables where thetotal number of these tables may change on a regular baisis andoutput into another table. All the tables willl have the samecolumns. Frequency is being debated but it maybe as much as once perhour.Example1) I need to choose all the following tablesselect * from dbo.sysobjects where name like '_CPY%.2) then I need the followingfor each of the tables found above, I need the outfrom from each ofthose tables to be inputted into another table. basically, I wouldwant the following output from each of the tables found in step 1select machineid,name from _cpy_offermanager_6783) In the end I would have something like dbo.ALLCPY with recordscombined from all other _CPY tablesRon Sorrell
Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer AS DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest ( Zip, UpsRegion, UspsRegion ) SELECT CMRC_ZipRegionUps.Zip, CMRC_ZipRegionUps.UpsRegion, CMRC_ZipRegionUsps.UspsRegion FROM CMRC_ZipRegion, CMRC_ZipRegionUsps GO
Is there any way to do this? Or do I have to manually enter all the entries?
Hello, I have two tables that I would like to combine but with some rules. Table 1 has two columns with Actual Time and Actual People Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex. Planned Time has 20:00,23:00 Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15 Actual People has 5,2,5,3
Output should be like Planned_Time,Actual_Time,Planned_People,Actual_People ,19:00,,5 20:00,19:30,2,2 ,19:45,,5 ,21:15,,3 23:00,,5,
I can do this in asp but if this can be done in sql it would be better. Any ideas appreciated. Thanks.
this is my query: tabel1: select userid,user_name,password,role_code,convert(varchar,expiry_date,101) as expiry_date,created_date,active from usermaster where userid='1' and active='1' if i run this query i will get output like this: userid user_name password role_code expiry_date created_date 1 karthik karthik AD 01/17/20082007-01-24 active 0:00:00.000 1
i have another table which has the following records
tabel 2: select * from code master
codename codedescription AD admin sp supervisor
so in the first query i need the output as like tithe following:
is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.
this is what i want;here's the two tables to be combined:
table-a ______________table-b 15 _ a ____________ ny _____ arena 25 _ d ____________ fg_____ metus 35 _ f ____________ 45 _ f ____________ these two tables above will become table-c like below;
table-c 15__arena 25__metus 35__null 45__null but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?
Hi all, I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS. When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works.. SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS, MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P WHERE FA.QUESTIONID=QS.ID AND FA.INCIDENTID=I.INCIDENTID AND I.INCIDENTID=D.INCIDENTID AND D.MOTHER_PAS_NO=P.PAS_NO AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59' AND QS.QUESTIONNAIREID=2 AND FA.INCIDENTID=4501
and if I want to view the answers to the standard drop down questions, I use the following code...
select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, A.ANSWER from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P, MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS, MAT_ANSWERS A, MAT_INCIDENTANSWERS IA WHERE I.INCIDENTID=D.INCIDENTID AND I.INCIDENTID=QI.INCIDENTID AND D.INCIDENTID=IA.INCIDENTID AND D.MOTHER_PAS_NO=P.PAS_NO AND Q.ID=QI.QUESTIONNAIREID AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID AND QS.ID=A.QUESTIONID AND A.ID=IA.ANSWERID AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59' AND Q.ID=2 AND I.INCIDENTID=4501
However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.
When the value in the second column is null, I need to check the value in the 3th table.
The join between the first and the thirth is:
select C.issueID, min(D.project) as Project from issuerequest as C left outer join ProductCustomer on (C.customer = D.customerID) and (C.product=D.produktID) where actuellproduct='1' group by C.issueID
this gives a result as: 100 | P002 100 | P003 101 | P004 102 | P002 103 | null ...
What I actually want is just 2 columns with in the first IssueID and in the second Project and no duplicate data. Everything (except null) is more important then the same value in column 3.
100 | P002 101 | P003 102 | P002 103 | null
I've tried with except, but can't get all duplicate data out.
Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.
So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50 database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
I am trying to do a really weird combination of an Append/Union query..I'm doing most of my commands through macros and queries.how to do a normal Append/Union/Create queries but I am trying to do something kind of weird. If table X has rows A, B, C, and D, and table Y has a column with rows 1, 2, 3, and 4... I need the values in table Y to be assigned to each individual row in table X. Is there a way to create a new table where the columns merge such that values can be assigned like:
I need to combine data from two tables located in the same database.
The column GID in Table 1 and RealEstateID in Table 2 has a common id
I want to specify a date interval (AcquistitionDate in Table 2) eg 20131001 to 20131030 and get Name from table 1, and combine these to a result table.
I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.
I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B
SELECT a.DATE, a.STATE, b.CITY FROM TABLE_A a LEFT JOIN TABLE_B b ON a.ID = b.ID
I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem: When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
I have two tables - T_Assets and T_Inspections. I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL: SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection) FROM T_Assets INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this. Jagdip
I have a quick question for the SQL community about how to combine 3 tables to get the results needed...
The table names are : setup_zipcode, setup_category, and listing
and inside setup_zipcode it has the columns: zip_id , zip_code, zip_latitude and zip_longitude
and inside the setup_category it has the columns: category_id, category_parent, category_path and category_name
and the final table for listing has the columns: listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country
I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...
So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..
How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?
I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...
$cat is assigned in a query previously on the page...
SELECT * FROM setup_zipcode, setup_category, listing WHERE listing_category LIKE '%-$cat-%' AND listing_zip = zip_code AND category_id = '$cat' ORDER BY listing_title ASC
When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..
I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..
If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
Result: combine tblShipmentHdr + attached tblShipmentDet records with tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005' AS SELECT SHPH.ProductID, SHPH.ReceivedDate, SHPH.ShipmentNo, SHPD.Vendor, SHPD.Quantity, QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ReceivedDate END, QShipQty = CASE WHEN SHPD.TransCd = 'F' THEN NULL WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ShippingQty END, PROD.ProductName, QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null THEN 0 ELSE MEND.OpeningAccrual END FROM dbo.tblShipmentHdr SHPH LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7) LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121) WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
Hi there, I'm trying to generate a report for an old database and I'mhaving trouble coming up with an elegant way of going about it. Usingcursors and other 'ugly' tools I could get the job done but 1) I don'twant the report to take ages to run, 2) I'm not a big fan of cursors!Basically there are tables that track history and each table tends totrack only a specific value housed within a date range. I'm trying tocombine the tables to get a snap-shot of the complete history. I'mhaving problems dealing with the Start/End Dates from the two tablesand building the dates in the final table to be broken down by 'historytype'.Here are a few sample records and the results I'm trying to achieve:Table 1:CAgyHist (ProdID,AgyID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 5, 20061 2 Jan 5, 2006 Jan 25, 20061 1 Jan 25, 2006 NULLTable 2:CInvHist (ProdID, InvID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 23, 20061 2 Jan 23, 2006 Jan 15, 20061 1 Jan 15, 2006 NULLDesired End Result:CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)1 1 1 Jan 1, 2006 Jan 5, 20061 2 1 Jan 5, 2006 Jan 15, 20061 2 2 Jan 15, 2006 Jan 23, 20061 2 1 Jan 23, 2006 Jan 25, 20061 1 1 Jan 25, 2006 NULLMy challenge thus far has been dealing with the dates as they don'tnecessarily correspond - from one table to the other.I am by no means a database expert of any level and any help would begreatly appreciated.Thanks,Frank.
I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins @tblResults tr JOIN dbo.MarketPrice mp ON tr.Item = mp.Item AND tr.[Month] = mp.[Month] AND tr.[Year] = mp.[Year] AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
[code]....
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50 The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28' The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50 The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.
Does anyone have any inkling into how I would do this.
I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.
Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement: CREATE VIEW dbo.vwsfwebitemswine AS SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName FROM dbo.Item INNER JOIN dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)
As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:
What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.
I am working with a database derived from text documents. One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column. I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result). How do I combine the rows with the same DocumentID into a single row of text data? This will put the entire text content of each document in its own row.
TEXT001 table as it is
DocumentID
SectionNo
SectionText
1
1
Paragraph 1 of Document 1
1
2
Paragraph 2 of Document 1
1
3
Paragraph 3 of Document 1
2
1
Paragraph 1 of Document 2
2
2
Paragraph 2 of Document 2
New TEXT table
DocumentID
SectionText
1
Entire text of Document 1
2
Entire text of Document 2
I realize that I can use €œunion€? to combine tables with the same data type, but that is not what I am trying to do. Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command. If anyone can tell how to do this, I would appreciate your help.
More modestly, I tried to use the €œGroup By€? clause to combine the SectionText data using this query:
SELECT DocumentID, SectionText FROM TEXT001 GROUP BY DocumentID
And got this error message:
Msg 8120, Level 16, State 1, Line 5 Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be €œsummed€?, so I tried including it in the GROUP BY clause:
SELECT DocumentID, SectionText FROM TEXT001 GROUP BY DocumentID, SectionText
And got his error message:
Msg 306, Level 16, State 2, Line 5 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?
Hi allI need to add data from two Excel sheets (both on the same workbook)to an existing table in my SQL DB.The problem is that each sheet holds different fields for the samerecord, though the records are sorted by row numbers.(I had to split the fields to different sheets because Excel has alimit of 256 fields in each sheet)My sheets are quite large (~55,000 rows and 200 columns each) and I'llhave to repeat this action many time, so some kind of an automationwill nice (VB, DTS package?)I am new to SQLServer, so an example will be most helpful.Many thanks,Ilan
