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.

View 3 Replies


ADVERTISEMENT

Sql Query Which Uses Multiple Tables But No Common Field To Join

Jan 29, 2004

Hello-

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".

How do i do this?

View 6 Replies View Related

How To Join Two Tables Having No Common Coloumns And Having No Primary Key Fields In Any Of The Table

Apr 14, 2008

hi friends i am reading a csv file and storing the data in table i have created the tables like this:

coloumn names datatype constraints

start_date_time varchar(50) allownull = true
ani varchar(50) allownull=true
dialed_digit varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true

and the second table as

coloumn names datatype constraints

country varchar(50) allownull = true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true

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...

View 11 Replies View Related

How To Join Two Tables Having No Common Coloumn

Apr 15, 2008

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)

please help me its urgent

View 3 Replies View Related

Field Common In Several Tables.

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

Selecting From Two Tables, And Sorting By Common Field.

Feb 10, 2002

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.

PLEASE HELP ME!!!
Pending deadline doom :(

Thanks,
kaskenasy@publishingconcepts.com

View 1 Replies View Related

Select From Multiple Tables No Common Field

Oct 17, 2005

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.

View 2 Replies View Related

Multiple Tables Grouped By Common Field

Jan 4, 2008

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!!!

View 6 Replies View Related

SQL Server 2012 :: How To Join Tables To Get Only Record With Specific Field Value In A Table

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

Speed/efficiency Of View Vs. Common/nested Table Expression In A Join

Mar 2, 2008



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?

View 2 Replies View Related

Join Two Tables Together Using Particular Field

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

Unable To Join Two Tables Together On Same Field Except Different Data Types

Sep 30, 2013

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...

View 6 Replies View Related

Help With A Query- Select A Top Field And Join It With Another Table

Feb 1, 2008

 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!

View 2 Replies View Related

- Using Inner Join When The Field In My Data Table Has The Null Default Value:

Jul 16, 2006

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)

View 2 Replies View Related

Transact SQL :: Remove Prefix And JOIN Field To Another Table

Jun 30, 2015

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. 

View 2 Replies View Related

Common(?) Problem For Updating A Text Field!

Apr 21, 2008

 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! 

View 3 Replies View Related

SQL Server 2012 :: How To Group Near Duplicate Records Under A New Common Field

Aug 26, 2015

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.

View 9 Replies View Related

Get Common Columns Name Between Tables

Aug 8, 2006

Hi,

Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database

For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)

name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?

THANK YOU

View 6 Replies View Related

Datamining Model Automatically Add Space To Field Name. Can't Use Prediction Join With Original Table

Aug 30, 2007

Dear All,


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?

Thanks and regards,

Tony Chun Tung Siu

View 1 Replies View Related

Union Select Of Two Tables And Join Of Another Table Afterwards

Apr 2, 2008

I have got the following union statement:


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?

Thanks in advance!

View 8 Replies View Related

Need Help With MAX Function Using Two Tables With Common Columns

Apr 9, 2006

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

View 4 Replies View Related

Common Temp Tables In Procedures

Jul 20, 2004

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.

View 1 Replies View Related

Find Common Value In Column For 30 Tables

Mar 21, 2006

Hi,

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.

Any suggestions would be much appreciated,
Jeff

View 1 Replies View Related

Subject: How To Join A Table With Other (result) Tables ? Complex !

Aug 25, 2005

Here is the situation

Table 1 : tbl_documents

docIDdocName
1aaa
2bbb
3ccc

Table 2 : tbl_Rating

ratIDratingdocID
131
251
321
432

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

PLs help me ?

Thx

View 3 Replies View Related

SQL 2012 :: Possible To Find All Tables That Has A Join With Given Table In Database

Jan 7, 2015

I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.

sys.dm_sql_referencing_entities doesn't work here.

Note: i dont want to identify by FK References, for 2 reasons:

1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined

View 1 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

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..
 
 

View 2 Replies View Related

Join Two Tables And Only Return The Latest Data For The Child Table

Sep 24, 2007

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,

View 4 Replies View Related

Check 2 Tables A Delete Records Based On Common Fi

Aug 8, 2007

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.

Thanks

View 1 Replies View Related

SQL Server 2014 :: 2 Tables Combined Left Join To Primary Key Of A 3 Third Table

Sep 1, 2014

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

[Code] .....

View 2 Replies View Related

T-SQL (SS2K8) :: Update A Field In 3rd Table From 2 Joined Tables

Jul 29, 2015

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:

'Mailing1, Mailing2, Mailing3'

View 9 Replies View Related

SQL Server 2014 :: Find Common Link Between Two Tables In Database Without Key Constraints

Jul 16, 2015

Any tool, script, procedure, or otherwise that will solve this problem?

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )

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.

View 7 Replies View Related

SQL Server 2012 :: Find Common Link Between Two Tables In Database Without Key Constraints?

Jul 17, 2015

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
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.

View 2 Replies View Related

Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

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!!

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved