Join A Table With Different Tables But With A Field In Common?
Jan 27, 2014
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.
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".
now i want to relate and join the table so that i can create a view with the following coloumns such as
start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this:
SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost, dbo.table2.dialed_digits FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)
This is feeling very hard for me, but is surely very easy for many of you. I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
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?
i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.
i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.
the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?
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...
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 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 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.
Hello, I have an aspx page with a text box. The user may enter a large portion of text including symbols like ' or ". On form submitting I would like a database (text or varchar(MAX) ) field to be updated with the value of the text box...The query that I have created for this purpose is the following: "string query ="UPDATE Article SET A_Content='" + content + "' WHERE A_Id=" + id; As I said before the user may (and in most cases must) enter special characters like ' (example: bla bla blah it's very good bla bla blah).The problem is that ' or " close the query string (SQL) and interpreter things that I am reffering to a field's name instead of the fields value (I think) resulting the following error:The identifier that starts with...is too long.Maximum length is 128.Unclosed quotation mark after the character string ...I think that it must be a very common problem with many solutions but for some reason I cant find anything on the web.What is the common practice to overcome the problem?Is there a function that might transform text into SQL acceptable text?Should I create methods for Replacing ' with other characters like ^^^ (text can be quite big...)?Thanx for any suggestions!
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember]( [dimMemberId] [int] IDENTITY(1,1) NOT NULL, [dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)), [MemberCode] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](250) NOT NULL, [LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters) Last Name DOB CurrentAddress1 MemberCode SSN SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names?
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
I have two tables that contain product SKUs (12-character strings): Table 1Product IdSKU... Table 2ProductVariantIdSKU... I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers. Thanks! Duncan
I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1 EXEC sp2 EXEC sp3
Job2
EXEC abc1 EXEC abc2 EXEC abc3 EXEC abc4 EXEC abc5
Job3
EXEC xyz1 EXEC xyz2 EXEC xyz3 EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1. procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:
Select distinct a.file_auth_nbr from table1 as a join table2 as b on a.file_auth_nbr = b.file_auth_nbr join table3 as c on a.file_auth_nbr = c.file_auth_nbr join table4 as d on a.file_auth_nbr = d.file_auth_nbr join table5 as e on a.file_auth_nbr = e.file_auth_nbr ......etc., etc.
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating 1aaa3 2bbb3 3ccc0
NOTE : For getting the average I used this queary SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID = tbl_documents.docID
Hellow Folks. Here is the Original Data in my single SQL 2005 Table: Department: Sells: 1 Meat 1 Rice 1 Orange 2 Orange 2 Apple 3 Pears The Data I would like read separated by Semi-colon: Department: Sells: 1 Meat;Rice;Orange 2 Orange;Apple 3 Pears I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as: select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID , all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this? I tried to create a function to get the latest sentence for a chargeID like the following: select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it? thanks,
I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Looking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
How to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL DROP TABLE dbo.[AllDates] CREATE TABLE dbo.[AllDates] ( [Date] date not null PRIMARY KEY ) ;WITH Dates AS
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a SET Con_Mailings = STUFF((SELECT '; ' + c.ListName FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait) INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait) ON b.ListID = c.ListID WHERE b.EntityID = a.TmpContactID FOR XML PATH('')),1,1,'') FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
CREATE TABLE [Table 2] ( Id varchar, MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
