SQL 2012 :: Joint Two Tables On A Non-key Field?
Jul 4, 2015
I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
View 2 Replies
ADVERTISEMENT
Sep 8, 2015
I have two tables like,
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code].....
I want result as the attached image.
Create table query for result is: CREATE TABLE dbo.#Result(
ID varchar(50),
Fee varchar(100),
Bill varchar(50),
A_Date date,
B_Date date,
Status VARCHAR(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime
)
how to achieve this in sql server 2012.
View 6 Replies
View Related
Feb 6, 2015
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
View 6 Replies
View Related
May 2, 2008
hi allCan somebody please explain to me what does it mean when someone saids use joint
indices to join tables?thanks in advance
View 3 Replies
View Related
Jun 10, 2008
CREATE TABLE [dbo].[SEL](
[MAIN_ID] [int] NULL,
[DATE_TAKEN] [smalldatetime] NULL,
[TIME] [int] NULL,
[DAILY_RAINFALL] [int] NULL
) ON [PRIMARY]
insert into SEL
values(194,'6/1/2006 12:00:00 AM',730,11)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',930,4)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1830,10)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1930,20)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2130,14)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2230,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',730,22)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',930,43)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1830,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1930,54)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2130,21)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2230,6)
CREATE TABLE [dbo].[station_info](
[STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATION_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAIN_ID] [int] NOT NULL
) ON [PRIMARY]
insert into station_info
values('SEL','PUCHONG',196)
insert into station_info
values('JHR','BulohKasap',5)
Above script showing table as follow
SEL
MAIN_ID | DATE_TAKEN | TIME | DAILY_RAINFALL
--------------------------------------------------------
194 | 6/1/2006 12:00:00 AM | 730 | 11
194 | 6/1/2006 12:00:00 AM | 930 | 4
..........
..........
202 | 6/1/2006 12:00:00 AM | 450 | 23
..........
..........
*This table storing DAILY_RAINFALL everyday from time to time for each MAIN_ID.
station_info
STATE | STATION_NAME | MAIN_ID
---------------------------------------
SEL | Puchong | 194
JHR | BulohKasap | 5
.........
.........
*This table storing MAIN_ID description. Main_ID is a primary key.
1. 1 day having many TIME. So, we only take which time having MAX(DAILY_RAINFALL) per day per MAIN_ID to do the SUM(DAILY_RAINFALL) for that month.
So far, i've this query
SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS DATE_TAKEN,m.MAIN_ID,m.DAILY_RAINFALL
FROM
(
SELECT MONTH(t.Date_Taken) AS Month,
YEAR(t.Date_Taken) AS Year,
t.Main_ID,
SUM(t.Daily_Rainfall) As Daily_Rainfall
FROM
(
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL')t
GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m
ORDER BY Main_ID,Date_Taken
*Assume GETDATE()=6/10/2007
After run above SQL, I got below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL
---------------------------------------------------
2006-06-01 00:00:00.000 |194 |329
2006-07-01 00:00:00.000 |194 |160
2006-08-01 00:00:00.000 |194 |388
2007-04-01 00:00:00.000 |194 |394
...........
...........
2006-06-01 00:00:00.000 |195 |145
2006-07-01 00:00:00.000 |195 |82
2006-08-01 00:00:00.000 |195 |225
...........
...........
I'm stuck when joint table below. The purpose of joint is to pickup [cumrf1year] column
(if DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101))
and pickup [month_year] value depend on DATE_TAKEN (if DATE_TAKEN=6/1/2006, [month_year]=6/30/2006, if DATE_TAKEN=7/1/2006, [month_year]=7/31/2006).
MAIN_ID is foreign key to relate [longterm_rf_temp] table below.
CREATE TABLE [dbo].[longterm_rf_temp](
[main_id] [int] NOT NULL,
[month_year] [datetime] NULL,
[cumrf1mth] [float] NULL,
[cumrf3mth] [float] NULL,
[cumrf6mth] [float] NULL,
[cumrf9mth] [float] NULL,
[cumrf1year] [float] NULL
) ON [PRIMARY]
insert into longterm_rf_temp
values(194,'6/30/2006',207.94,550.7,850.7,1150.7,1450.7)
insert into longterm_rf_temp
values(194,'7/31/2006',200.64,590.4,858.7,1260.7,1550.7)
insert into longterm_rf_temp
values(194,'8/30/2006',222.64,390.4,958.7,1460.7,1750.7)
insert into longterm_rf_temp
values(195,'6/30/2006',217.94,550.7,840.7,1150.7,1324.7)
insert into longterm_rf_temp
values(195,'7/31/2006',202.64,590.4,858.7,1260.7,1659.7)
insert into longterm_rf_temp
values(195,'8/30/2006',222.64,490.4,958.7,1460.7,1733.7)
After joint, I should get below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL | [cumrf1year]| DiFF | DEV
----------------------------------------------------------------------------------
2006-06-01 00:00:00.000 |194 |329 | 1450.7 | -1121.7 | -0.773
2006-07-01 00:00:00.000 |194 |160 | 1550.7 | -1390.7 | -0.896
2006-08-01 00:00:00.000 |194 |388 | 1750.7
2007-04-01 00:00:00.000 |194 |394 | ......
...........
...........
2006-06-01 00:00:00.000 |195 |145 | 1324.7
2006-07-01 00:00:00.000 |195 |82 | 1659.7
2006-08-01 00:00:00.000 |195 |225 | 1733.7
...........
...........
This is the formula,
DIFF = DAILY_RAINFALL - [cumrf1year]
DEV = DIFF/[cumrf1year]
I almost give up to adjust the above SQL but still fail. Please help me, i'm really-really stuck.
View 9 Replies
View Related
May 3, 2007
I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.
(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'
$manufacturer_id=$data["manufacturer_id"];
(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id
View 3 Replies
View Related
Sep 14, 2006
Hi Guys
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
View 20 Replies
View Related
Mar 6, 2014
I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
select cast((35622.15/2) as decimal(15,2))
Is there any way i can achieve this.
View 4 Replies
View Related
Aug 26, 2005
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View 4 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Feb 21, 2015
I want to learn if its possible to get ID in trigger and set it to another field.
I can do it by using scope_identity and using update command but I dont want to run another update command.
This is a very heavy loaded system that runs maybe 10-30 transactions on every second and I cannot afford that. I come up with a solution by using sequences but if I could use ID would be better.
View 1 Replies
View Related
Apr 7, 2008
i have two tables
Table 1 - AccountRent
Fields are
RentAmount RentYear
100 2004
200 2004
300 2004
400 2005
500 2006
Table 2 - Insurance
Fields are
InsuranceAmount ReceivedYear
100 2002
200 2002
300 2003
400 2003
500 2005
600 2005
I Need to sum these two tables 'Amount' based on the Year The Result for
IncomeAmount Year
300 2002
700 2003
600 2004
1500 2005
500 2006
Regards,
Prabu R
View 12 Replies
View Related
Jul 14, 2014
What sql data type would fit these value? This is timestamp field in the ERP.
2013-05-24-20.03.46.843480
View 3 Replies
View Related
May 11, 2015
is it possible to have a field that is calculated using a User Defined Function, and index that field?
View 9 Replies
View Related
Aug 22, 2003
Do you have any sql statement for find the field difference between two tables?.
View 2 Replies
View Related
Jul 25, 2006
This is my curren code for updaing ESN number. But this is incorrect.
<asp:SqlDataSource ID="ESNTrackingDataSource"
UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId"
OnInit="ESNTrackingDataSource_Init"
OnUpdating="ESNTrackingDataSource_Updating"
OnUpdated="ESNTrackingDataSource_Updated"
runat="server">
<UpdateParameters>
<asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and
Asset
SELECT DISTINCT EsnId, EsnNumber
FROM dbo.ESNTracking
WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
View 1 Replies
View Related
Sep 19, 2014
I am trying to join two tables together using a particular field. The code to reproduce is:
declare @chris as table
(
chrisid int identity (1,1) primary key,
name varchar(100),
SrvStopID int
)
[Code]...
My result is:
chrisidname SrvStopIDchrisidname SrvStopID
1Neal 1 1John 1
1Neal 1 2Jacob 1
1Neal 1 3Jinglehiemer 1
1Neal 1 4Smith 1
2SmithSon 1 1John 1
2SmithSon 1 2Jacob 1
2SmithSon 1 3Jinglehiemer 1
2SmithSon 1 4Smith 1
and my result should be: Just the results in @Neal that match SrvStopID in @Chris
View 3 Replies
View Related
Dec 1, 2005
I have a database with 10 tables
I have a field named CustID .... I want to find out how many tables have this field CustID
Now one way was check up the design of every table & see whether its there or not (the field)
is there any query through which we can get this info Please help
View 4 Replies
View Related
Mar 21, 2006
I have two tables I need to join but there are 2 fields which theycould be joined on.Using the example Tablles, TableA and TableB below;TableAID1 ID2 Qty1 Null 42 A 5Null B 6TableBID1 ID2 QtyNull A 63 B 64 Null 7Null C 8I want to create TableC which will look like this;ID1 ID2 TableA.Qty Tableb>Qty1 Null 4 Null2 A 5 63 B 6 64 Null Null 7Null C Null 8Any ideas?Regards,Ciarn
View 2 Replies
View Related
Apr 17, 2008
I imported data into a database and the first character in an ID Field starts with %. This is causing many problems for the application. Unfortunately, this field exists in 72 of 128 tables in the database. Is there a way to LTRIM every ID field where the first character is %? This is easy in 1 Table but how do I apply it to all 72 tables at once? Thanks for for your assistance
View 6 Replies
View Related
Jun 5, 2014
Query to return values from XML field
create table #temp
(
id int identity (1,1)
,FieldSet XML
)
INSERT INTO #temp
VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')
,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')
SELECT * FROM #temp
Expected Output :
Id Column1Column2Column3Column4
1value1value2nullnull
2nullnullvalue3value4
View 4 Replies
View Related
Aug 23, 2014
I have a Function where I want to put a character in a particular position in a Destination string. For example, I have a 5 character field containing, say, "12345". I would like to put the letter "A" in the third position resulting in "12A45". I'm trying the following
DECLARE @RetVal as varchar(5)
SET SUBSTRING(@RetVal,3,1) = 'A'
I'm getting a compiler error
"incorrect syntax near '@RetVal'. Expecting '(' or SELECT"
Am I using the wrong Function to do this? If so, what should I be using?
View 2 Replies
View Related
Aug 4, 2015
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
[code]...
View 5 Replies
View Related
Aug 9, 2006
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
View 1 Replies
View Related
Jun 11, 2008
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
View 1 Replies
View Related
Dec 27, 2013
So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.
then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert
So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.
So now I'm wondering 2 things...
1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or
2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...
View 3 Replies
View Related
Feb 21, 2014
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below you will find the code I am running:
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int
[Code] ....
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
View 6 Replies
View Related
Apr 30, 2014
How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I know I cannot use SQL Server encryption to encrypt an XML field and I have been getting unpredictable results with a home grown solution.
View 9 Replies
View Related
Aug 21, 2014
I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.
I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.
create table dbo.Movie_Pre_Fix
(
Report_Monthint,
IDint,
Movie_NameVarchar(50)
);
insert into dbo.Movie_Pre_Fix (Report_Month, ID,Movie_Name)
VALUES
(201406,0721312144,'SAW'),
[code]....
View 6 Replies
View Related
Oct 27, 2014
How to limit the length of a database field such that only first 12 characters are shown in the result?
For example, I have show the only first 12 characters of employee name.
View 3 Replies
View Related
Oct 30, 2014
I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.
View 9 Replies
View Related
Jan 16, 2015
I have some data where a bit value changes over time and I want to rank it by the repeating groups, how do I write the SQL so that I get the result below?
I want to sort by create date, and every time the bit changes value we start the rank over.
If I partition by the bit field, it will just group the entire data set by the bit field.
Bit CreateDate Rank
1 3/15/2014 1
1 3/14/2014 2
0 3/9/2014 1
0 3/8/2014 2
0 3/6/2014 3
1 3/4/2014 1
0 2/28/2014 1
0 2/20/2014 2
0 2/15/2014 3
0 2/10/2014 4
View 3 Replies
View Related
Feb 26, 2015
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,
[Code] .....
View 9 Replies
View Related
