Been struggling with this issue all morning and beginning to get a headache. Essentialy my problem is this. I have a gridview control that ive bound to datasource where the select statement is using an inner join to retrieve textual info from one table in refernce to an object_id contained within both. i.e
SelectCommand="SELECT 'multiple fields from both tables'
FROM ZCRMTAB_ACTIVITY INNER JOIN ZCRMTAB_ACT_LOG
ON
ZCRMTAB_ACT_LOG.OBJECT_ID = ZCRMTAB_ACTIVITY.OBJECT_ID
WHERE (ZCRMTAB_ACTIVITY.REF_CUST_NO = @REF_CUST_NO)
ORDER BY ZCRMTAB_ACTIVITY.CREATED_AT DESC">
My problem is that this returns individual row results and are displayed on individual lines. Where the object_id is the same i want all the textual info to be displayed within the same row.
I have this database running (ignore that the ERD below was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the users table and the orderContents table I need to make a query that:
Collects the users' names and any products they have bought (preferably shown by title not ID) as well as still showing the users that have not ordered any products.
I get the feeling there's a left join involved, but can't quite see how to do it.
i'm new to this and i am trying to create a statement that would add the values together for Hong Kong and China and then delete the Hong Kong road. (this is a example what i am trying to acheive)
I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4 A 1 1 A 2 2 B 2 3 B 5 5
it should look like:
A 1 1 2 2 B 2 3 5 5
Here is my query:
SELECT * FROM [TLC Inventory].dbo.['2014 new$'] WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%' AND dbo_Product_Info#description NOT LIKE 'NR%' AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'
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 )
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = 0,
bControlRiskIsAffirmed = 0,
bCombinedRiskIsAffirmed = 0,
bApproachTypeIsAffirmed = 0,
bCommentsIsAffirmed = 0
FROM EngagementAuditAreas WITH(NOLOCK) ...
And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),
bControlRiskIsAffirmed = (numControlRiskID IS NULL),
bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),
bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),
bCommentsIsAffirmed = (parent.txtComments IS NULL)
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Hiiiiiiiiiiii, How to join 2 field to be 1 (alias). for example, i've 2 field (FirstName & LastName), then i want join thats field to [Name]. How to do that using SQL server 2000 Please advice.. Thanks
I am tryin t to update the tbl1_ID from the tbl2_ID. How do I do a Update Join that will do a comparison on the param column and value columns so that I could get the correct ID into tble 1. Tbl1 is my destination table and tbl2 is my source. Please Help.
ID Tbl1_ID tb1Param tbl1value
1NULLParam1 0 1NULLParam2 F 1NULLParam3 2 3NULLParam1 0 3NULLParam2 E 3NULLParam3 0 5NULLParam1 0 5NULLParam2 F 5NULLParma3 2
tbl2_ID tbl2Param tbl2value
100param1 0 101param1 1 102param1 2 103param1 3 104param1 4 105param2 E 106param2 F 107param2 H 108param2 HF 109param2 HS 110param2 L 111param2 LS 112param3 0 113param3 1 114param3 2 115param3 3 116param3 4 117param3 5 118param3 6
Here is what Im trying to do if you can understand this.
Update Tbl1 SET tb1ID = B.tbl2_ID FROM tbl1 AS A JOIN tbl2 AS B ON A.tbl1Param + A.tbl1Value = B.tbl2Param + A.tbl2value
Folks,My secnario involves two tables - ObservationRegister, and Person.ObservationRegister contains most of the "useful" fields, includingthe UserID of the person that raised the record, and the UserID of theperson to whom the record was assigned for action. I need to write aquery to return all values in the ObservationRegister record, butinstead of returning the UserIDs, I need to look up the actual name,by looking up the name and userID in the Person table... doing thatonce (for just one of the UserID fields) is easy - a quick inner joindoes the job - but I effectively need to join to the Person table"twice", for different keys....Help? Please!? :)Steve
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
Is there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?
In our warehouse DB, there is a master location table, an inventory location table, and physical table for counting all product in the warehouse. The master location table has one record per location, but there could be multiple items in that location so my outer join from the master location to the inventory table returns something like:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY from M_LOC M LEFT OUTER JOIN C_INVT C ON M.MASTER_LOC = C.INVT_LOC order by M.LOC_CODE
My join is returning 4 rows for location 01-03-A which I understand, but I'm wondering if I can sort within the join or make some temp tables so that instead of:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY, E.AS CNTD_ITEM, E.CNTD_LOT, E.CNTD_QTY from M_LOC M LEFT OUTER JOIN C_INVT C ON M.MASTER_LOC = C.INVT_LOC LEFT OUTER JOIN E_PHYS_INVT E ON M.MASTER_LOC = E.LOC_CODE order by M.LOC_CODE
We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.
I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server?
SELECT first.name,first.country, second.name, second.string FROM first LEFT OUTER JOIN second ON first.name = second.name WHERE first.date='2015/02/24'
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.
I need a bit of help with a join. I have 2 tables :
TradeSummary has fields : SymbolID, CurrentPrice, TotalValue
Trades has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary and also get TradeValue from Trades for the record for max(ExecutionTime) tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..
on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4
any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?
I have run into a problem that I am stuck on. I have 3 tables lets call them a,b, and c. What I want to do is left outer join a with b on a common value, then replace any null values in this result with a default value, and then left outer join table c on the previously joined table (on the columns that I just replaced certain values on).
I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime, R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime, CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked FROM Production.DownTimeReason R left JOIN Production.DownTimeHistory D
I have 2 tables ... order table (shipping city and destination city) and a referential city table:
1:New York 2:Chicago 3:Atlanta etc...
In the order table the shipping address and destination address have the identity's a values, I want to write a join to show the names of the cities instead. I'm doing something like this, is there a way to do it in one select?
;with srcemkt as ( select ID, OrderNumber, b.MarketCenterCity as 'SourceMKT', from Orders as a join MarketCity as b
I know this is an easy one, but for some reason i keep gettin the wrong results. This displays something like this : Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams
But i want it to show all the SR_Name's ..like this: Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams NULL null Tom Jones NULL null Kim Jones NULL null Jon Travis
Any help will be grately appreciated! thanks!
Code Block ALTER PROCEDURE [dbo].[PROC_RPT_SR_DAILY_ACTIVITIES] (@Region_Key int=null, @Daily_activity_statistics_datetime datetime ) AS BEGIN
I have a datatable : Data_Table and a look up table: Lk_table. Myfield that I use in Inner Join is defined in both the data and look table.
So I build my query like this:
SELECT * FROM dbo. Data_Table INNER JOIN
dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield
The pb, sometimes I have myfield still with its default null value in the datatable: Data_Table. So, I end up getting 0 record when I execute the query shown above.
How do I turn that around so that even if myfield in Data_Table is Null, I still get the records from Data_Table. (I don t want a set of records including all possible values from the look up table: Lk_Table)
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
I am trying to join two tables together, on the same field except they have different data types, see the properties below
Code: TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME 1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI 2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS in the code for the join,
Code: left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
I have tried using the CAST function on one side of the join then on both, to no avail...
I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
