Join-Problem (select Two Columns Out Of One Column)
Oct 27, 2007
Hi I'd like to select two columns out of one column from another table. Example from source table:
Code Block
ID ArtNr EAN
1 4711 51323135
1 4712 84651213
1 4713 84351322
2 4711 86431313
2 4714 23546853
1 4714 54646545
2 4715 64684353
2 4716 65435132
I want to join this table to a base table with one select, and generate the following output. The ID identifies If the ArtNr has an EAN1 or EAN2:
Code Block
ArtNr EAN1 EAN2
4711 51323135 86431313
4712 84651213
4713 84351322
4714 54646545 23546853
4715 64684353
4716 65435132
View 20 Replies
ADVERTISEMENT
Feb 1, 2014
I'm trying to join two columns together to form a new column
My code is basically in the form of can't post the actual since it would be cheating--school assignment
SELECT Column1Name,Column2Name, Column3Name,Column4Name,
Column1Name+Column2Name AS NewColumn1
Column3Name+Column4Name AS NewColumn1
FROM OriginalTable
View 1 Replies
View Related
Jan 20, 2015
How to join 2 heap tables with out any common fields.
for example tbl1 has
col1
1
2
3
4
5
and tbl1 2 has
col2
a
b
c
I want the output like
col1 col2
1 a
2 b
3 c
4
5
is this possible with out using row_number()?
View 9 Replies
View Related
Jun 2, 2014
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies
View Related
Mar 28, 2006
Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
declare @inv table (item varchar(50), supplierid int)
declare @supplier table (supplierid int, weight int)
set nocount on
insert into @inv values ('item1', 1)
insert into @inv values ('item1', 2)
insert into @inv values ('item2', 2)
insert into @inv values ('item2', 3)
insert into @supplier values(1, 30)
insert into @supplier values(2, 20)
insert into @supplier values(3, 10)
-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3
select item, ps2.supplierid from @supplier ps2 join
(select item, min(ps.weight)'weight'
from @inv inv join @supplier ps on inv.supplierid=ps.supplierid
group by item) iw on ps2.weight=iw.weight
Is there a better alternative to this?
Thanks in advance,
Mike
View 3 Replies
View Related
Feb 27, 2015
I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
SELECT max([tablePK])
,[orderGUID]
,[tblPeopleFK]
,[tbl_shippingAddressFK]
,[tblBillingMethodFK]
,[shippingMethod]
[Code] ....
View 1 Replies
View Related
Oct 6, 2006
Hi
I can't figure out how to do this and hope you guys can give me a hint....
I want to select all columns from a table except one column:
For example,
A table has 20 columns : {1,2,3.....20}
To select all of columns in a table, i could use
select * from table;
However, I want to select only column {1....19}
I do not want to type the column names in one by one in the SQL query,
is there a way to achieve this in a short form of Select?
Thanks,
View 15 Replies
View Related
Nov 20, 2005
Hello all,
how can I select one or more columns from a table by column-index and NOT by columnname?
e.g.:
SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1
and NOT like this:
SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1
Is that possible in MS-SQL 2000?
Thanks a lot in advance
kind regards
Otto
View 11 Replies
View Related
Oct 31, 2014
‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:
SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’
View 4 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AEDÂ Â Â BNTÂ Â Â 1Â Â Â 1
AEDÂ Â Â BNTÂ Â Â 12Â Â Â 1
AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1
AEDÂ Â Â SNTÂ Â Â 1Â Â Â 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AEDÂ Â Â BNTÂ Â 13Â Â Â 2
AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1
AEDÂ Â Â SNTÂ Â Â 11Â Â Â 7
AFNÂ Â Â BPCÂ Â Â 8Â Â Â 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
Aug 9, 2013
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Sep 10, 2007
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
View 1 Replies
View Related
Aug 20, 2015
The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage.  There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.  The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date. Â
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
View 6 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
  1       |John            |Baker
  2       |Carl             |Lennon
  3       |Marion           |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
  1     |1/1/14    |12/30/14
  2     |1/1/15    |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeIDÂ sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeIDÂ Â AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeIDÂ Â AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeIDÂ Â AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Dec 28, 2003
There is a query:
SELECT info_cost.Ac_Year, info_paym.Ac_Year FROM info_paym FULL OUTER JOIN info_cost ON (info_cost.ID = info_paym.ID AND info_cost.Ac_Year = info_paym.Ac_Year) WHERE info_cost.ID=1882 OR info_paym.ID=1882
Thus I have a table of two columns which values either the same or one of them equals NULL:
Ac_Year Ac_Year
--------- -----------
NULL 1999-2000
NULL 2000-2001
2001-2002 2001-2002
2002-2003 2002-2003
Can I create a column in the query to join this two columns???
Like this:
year
---------
1999-2000
2000-2001
2001-2002
2002-2003
View 1 Replies
View Related
Apr 2, 2007
I have two colums within the same table. The 1st column contains name of products and the second column is used to rename the products so not every row is entered (omly those products we want to rename). I would like to create a new column which updates the name of the products. So if there is no new name it uses the old one, but if there is a new name use that one. I hope this makes sence?
View 3 Replies
View Related
Aug 13, 2007
I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL.
I have four tables. Product Line, Item, Property, and Value.
A Product Line has many items and an item can have many property's and a property can have many values.
I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch.
Any help would be appreciated.
Thanks,
vmon
View 2 Replies
View Related
Aug 2, 2006
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
View 2 Replies
View Related
Jan 11, 2006
I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.
---------------------------
SELECT * FROM jobs
INNER JOIN
(
SELECT contact_id FROM contacts WHERE deleted = 0
)AS ValidContacts
ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)
---------------------------
This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?
Another possible solution is if I were to use "WHERE/IN" like this:
---------------------------
SELECT * FROM jobs
WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0)
OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)
---------------------------
This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.
I'm using SQL Server 2000 on Windows XP Pro.
View 6 Replies
View Related
Apr 13, 2012
I have 2 tables, Licenses and Prices as below
Licenses
User Tariff Licensetype
User1 Tariff1 License1
User2 Tariff2 License1
User3 Tariff1 License2
User4 Tariff2 License2
etc
Prices
Tariff LicenseType Cost
Tariff1 License1 £1
Tariff1 License2 £2
Tariff2 License1 £1.50
Tariff2 License2 £2.50
I need to create a query that will produce a table as follows:
User LicenseType Cost
I have tried the following:
Select Licenses.User, Licenses.LicenseType, Prices.Cost
from Licenses
inner join Prices
on Licenses.Tariff = Prices.Tariff
and Licenses.LicenseType = Prices.LicenseType
[Code] ....
But all three output multiple lines for each user.
View 1 Replies
View Related
Jun 21, 2007
After working with Oracle (can I say that out loud here? ) databases for a couple of years, our company recently switched to SQL2005. So far I really like it, and the switch has been relatively easy for me. Except for one thing...
I'm trying to find out how to join a subquery on multiple columns. In Oracle it would like this:
select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)
But this doesn't work in SQL-T. I've temporarily solved it by doing this:
select * from #temp1 where id_1+id_2 not in (select id_1+id_2 from #temp2)
I don't like concat solutions for joins though (it always feels 'unsafe' to me). Plus, I can't believe there's no way to solve this.
I've searched the FAQ and BOL but I just can't seem to find the solution. Please help. I feel stupid.
View 5 Replies
View Related
Jan 29, 2007
Here's an oversimplified version of a query that I'm writing and wantedto know if there are any performance differences between the two versions.select *from table_a a , table_b bwhere a.col_1 = b.col_1and a.col_1 = 1000versusselect *from table_a a , table_b bwhere a.col_1 = 1000and b.col_1 = 1000All the tests show that they run at the same speed. But I have a verylarge query that joins 5 tables together and I'm trying to get as muchout of it as possible. Currently it runs at 2 seconds which I reallydon't like and would like to get it at under 1 second. So I'm lookingfor every little bit.I've already removed the DISTINCT, which in my test case doesn't doanything, but still took up one second.
View 2 Replies
View Related
Oct 31, 2006
I have a one to many relationships each claim we bill for a client has many status. Things like claim sent, claim received by insurance company, claim rejected. There are 80 different statuses all stored in tbl_status and I need to distill into three columns. I currently have the query below that returns only accepted claims and the date claim was released to insurance company.
I am struggling with how to have multiple columns released; paid, rejected each column can be represented by one or more of the 80 status in the status table.
So I want something like this
Select Case status €˜135€™ or €˜111€™ then Y else N AS Accepted
Select Case status €˜123€™ or €˜444€™ then Y else N AS Released
Select Case status €˜435€™ or €˜909€™ then Y else N AS Aprroved
Select distinct tp.ControlNo, tp.ClaimID, t1.StmtFromDate, sh135.StatusDate
from claims.dbo.tbl_Patient tp Join
(select tc.claimid, tc.StmtFromDate,
from claims.dbo.tbl_Claim tc
where (tc.Name = '6296U1' OR tc.Name = '6296H1')
AND tc.PayerInd = 'A'
AND tc.StmtFromDate BETWEEN '09/01/2006' AND '9/30/2006'
AND tc.SystemCode <> -1 -- Deleted
AND not exists ( Select * from claims.dbo.tbl_StatusHistory sh
where sh.ClaimId = tc.ClaimID AND sh.StatusCode IN('210','844','995','5400','7310','7311','7320','A0','A1','A2','A5','P0','P1','P2','F0','F1','F3','F3F','F4')
) T1 On tp.ClaimID = T1.ClaimId
Left join claims.dbo.tbl_StatusHistory sh135 ON T1.ClaimId = sh135.ClaimId AND sh135.StatusCode = '135'
View 8 Replies
View Related
Sep 12, 2007
I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.
I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.
I have following query which does not produce desired result
SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)
The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.
I'm pretty sure it's simple solution but can not figure out myself.
View 4 Replies
View Related
Jan 7, 2015
I've a table similar to the one below, with a SKU, Category and Cost, and need using a simple select command, split the cost in two columns one for each category (1,2), I used a self-join, and it works, but it doesn't show values not equal in both categories
Declare @Tmp_SKUCatValue Table(
SKU char(7)
,Cetegory Int
,Unit_cost Decimal
);
INSERT INTO @Tmp_SKUCatValue (SKU, Cetegory,Unit_cost)
Values
('sku-001',1,120)
[code].....
The result is as
SKU----------UCost_Cat1-----UCost_Cat2
sku-001------120--------------222
sku-002------126--------------228
sku-003------132--------------234
sku-004------138--------------240
sku-005------144--------------246
-----------------------------------------------------------
but missing the following lines,
SKU----------UCost_Cat1-----UCost_Cat2
sku-006------333--------------null
sku-007------null--------------444
Is ok to not show sku-008 as it is not part of category 1 or 2?
View 2 Replies
View Related
Oct 14, 2015
LeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays
1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10
I have the above table (LeaveEntitlement) which has the above columns.
What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.
For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual
The result should be shown in new column name AnnualLeave (20)
sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick
The result should be shown in new column name SickLeave (10)
Same all leave Types
The table should be shown as the below after executing the query
LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave
1 1 1 20 0
2 1 2 0 10
3 2 2 0 20
is it possible in sql server
View 8 Replies
View Related
Sep 1, 2015
DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))
INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL
[Code] ....
Need query to get the Result as below
PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL
By using case statement we can retrieve the data, but looking for alternate way to do it.
View 4 Replies
View Related
Feb 19, 2008
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0
) AS p
GROUP BY p.id
View 9 Replies
View Related
Aug 7, 2015
I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....
declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
[code]....
View 4 Replies
View Related
Aug 10, 2015
I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
View 4 Replies
View Related
Mar 15, 2014
-- My first Data
create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')
[code]....
View 1 Replies
View Related
