Combining Columns From 2 Tables
Aug 7, 2014
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.
View 3 Replies
ADVERTISEMENT
Mar 3, 2000
I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.
Do anyone know the syntax??
Thanks, Vic
View 1 Replies
View Related
Jun 19, 2008
Hello everybody,
I have the following problem.
I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.
I would like to make a query that retreives al send emails, its recipients and message_size.
SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles
Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55
But i'd like to get;
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55
Can anyone help me with this query?
Best Regards,
Sidney
View 3 Replies
View Related
Aug 29, 2005
Hi
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
Thanks
View 8 Replies
View Related
Jul 18, 2007
I need to combine my first 3 columns into 1.
SELECT [Category]
,[Sub Category - I]
,[Sub Category - II]
,[MSC #]
,[Price]
FROM [Pricing Analysis].[dbo].[Table1]
View 2 Replies
View Related
Mar 21, 2008
Hey everyone,
I have a windows app with a particular report that has 2 date columns in it. I need to combine these two columns based on a choice that the user makes on a previous form. So the user enters two dates (beginning and ending) and then makes a selection. So if my query brings back
Column1 Column2
1/1/2000 1/1/2004
2/2/2002 2/2/2007
3/3/2005 3/3/2008
.... .....
If the user selects option 1 column1 is displayed on the report, option 2 then column2, and if they select option 3 then columns 1 and 2 must be combined together based on other criteria.
I'm passing a parameter with the selection to my report, is there any way to combine these columns strictly in the report, or would it be easiest to do this in my sql query, and if so how? Keep in mind that I must keep each column separate because after the user makes the selection I have to make certain comparisons to each column before combining them. If any more information is needed then just let me know and I will post it as well.
Thanks in advance,
Aaron
View 4 Replies
View Related
Oct 13, 2005
Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:
order# Part# Addon1 Addon2 Addon3
What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:
Old Table: addon1 Addon2 Addon3
New Table:
Addon1
Addon2
Addon3
Of course I would also provide a link between the part and the applicable addons.
Thanks
View 1 Replies
View Related
Oct 18, 2013
I have a query (SELECT * FROM Pricing) that produces the following results
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23
I want to convert this to the following :
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23
We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23
View 1 Replies
View Related
Feb 23, 2006
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Many thanks.
View 4 Replies
View Related
Feb 8, 2007
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:
UPC ….. CategoryName DepartmentName
12345….. MerlotWine
34556….. ChadonnayWine
Etc…
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.
UPC ….. CategoryName DepartmentName Combined
12345….. MerlotWine Wine~Merlot
34556….. ChardonnayWineWine~Chardonnay
Etc…
I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.
View 1 Replies
View Related
Jul 20, 2005
Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully
View 5 Replies
View Related
Aug 2, 2006
I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?
View 5 Replies
View Related
Oct 5, 2006
I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC
View 10 Replies
View Related
Oct 23, 2001
How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).
Thank you,
Andrew
View 2 Replies
View Related
May 9, 2004
Hello everyone,
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?
Any help would be great. Thank you.
-Alec
View 1 Replies
View Related
May 15, 2007
If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
View 2 Replies
View Related
Feb 2, 2006
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.
View 4 Replies
View Related
Feb 5, 2007
hi
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:
userid user_name password role_code expiry_date created_date
1 karthik karthik admin 01/17/20082007-01-24
active
0:00:00.000 1
so how to combine this two tables inorder to get the codedescription as admin for the roll_code=ad
so please give me query for this
View 18 Replies
View Related
Jun 9, 2007
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 ?
View 14 Replies
View Related
Dec 20, 2007
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.
Would appreciate any help!
Fiona
View 10 Replies
View Related
Mar 11, 2008
I have a database, and I need data from 3 tables.
From the first table I just need the primary key: issueID.
I can make a left outer join with my second table so I can have this result.
select A.issueID, B.projectID
from issuerequest as A left outer join projects as B on (A.issueID=B.referenceID)
group by A.issueID
gives:
100 | null
101 | P003
102 | P002
103 | null
...
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.
View 3 Replies
View Related
Jul 12, 2004
Hi...
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?
Thanks,
Steven Lee
View 2 Replies
View Related
Oct 18, 2004
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.
View 8 Replies
View Related
Jul 2, 2004
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?
Is this even possible?
View 4 Replies
View Related
Jul 1, 2014
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:
X Y
A 1
B 1
C 1
D 1
A 2
B 2
C 2
D 2
A 3
B 3
View 3 Replies
View Related
Apr 29, 2008
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".
Please help.
Thanks,
Jim
View 3 Replies
View Related
Jun 3, 2008
Hey all
I'm trying to get data from two tables that have the same fields. One is a log table and the other is the actual table. I have the following:
SELECT tbl_LCData.* FROM tbl_LogOfLCDataTable, tbl_LCData
WHERE (((SELECT DATENAME(MONTH, tbl_LCData.PrintDate)) = (SELECT DATENAME(Month,getdate())) AND (SELECT DATENAME(MONTH, tbl_LogOfLCDataTable.PrintDate)) = (SELECT DATENAME(Month,getdate()))))
I am trying to display records that were printed this month. I only get data from tbl_LCData. It doesn't display data from the log table. For example:
Contents of tbl_LCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
18, B, Whatever, 11/04/2007
Contents of tbl_LogOfLCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 01/06/2008
So, I want the query to display:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
17, A, Sample, 01/06/2008
The above shows records printed in the month of June!
Is there a join I need to use??
Many thanks,
Rupa
View 18 Replies
View Related
Jan 10, 2014
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.
Something like this (?) : [URL] .....
View 4 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Sep 20, 2007
Hi all,
How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks
Table A
Client Weight Purchase
Tom 10 2
Bill 4 2
John 3 2
Table B
Client Weight Purchase
Jim 2 5
Lee 4 3
Bob 6 7
Combination table (result)
Client Weight Purchase
Tom 10 2
Bill 4 2
John 3 2
Jim 2 5
Lee 4 3
Bob 6 7
View 3 Replies
View Related
May 4, 2007
Hii all.
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 ?
any help is appreciated.
Neil
View 7 Replies
View Related
Nov 24, 2015
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
View 7 Replies
View Related
Jul 17, 2006
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
View 8 Replies
View Related
