Joining two tables with many to many relationship

How can I join two tables with many to many relationship? Any idea?
On Mon, 27 Sep 2004 12:27:47 +0300, Comfort wrote:

>How can I join two tables with many to many relationship? Any idea?
>
Hi Comfort,
A many-to-many relationship is usually implemented through a linking
table. For example, let's say each employee can work on multiple proects
and each project can be worked on by multiple employees. Your tables would
look somewhat like this:
CREATE TABLE Personnel (EmpID char(10) NOT NULL,
other columns,
PRIMARY KEY (EmpID)
)
CREATE TABLE Projects (ProjID int NOT NULL,
other columns,
PRIMARY KEY (ProjID)
)
CREATE TABLE Assignments (EmpID char(10) NOT NULL,
ProjID int NOT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Personnel,
FOREIGN KEY (ProjID) REFERENCES Projects
)
This structure also allows you to store extra information about the
relations (e.g. when was the employee assigned to a project, wat role will
(s)he play in the project, etc)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Can u please tell the SQL Select syntax for joining two tables with many to many relationships. The two tables don't hold the foreign keys, but the third table as u said, holds them. Then how to perform join?
Thanx
A.P

Quote:

Originally posted by Hugo Kornelis
On Mon, 27 Sep 2004 12:27:47 +0300, Comfort wrote:

>How can I join two tables with many to many relationship? Any idea?
>
Hi Comfort,
A many-to-many relationship is usually implemented through a linking
table. For example, let's say each employee can work on multiple proects
and each project can be worked on by multiple employees. Your tables would
look somewhat like this:
CREATE TABLE Personnel (EmpID char(10) NOT NULL,
other columns,
PRIMARY KEY (EmpID)
)
CREATE TABLE Projects (ProjID int NOT NULL,
other columns,
PRIMARY KEY (ProjID)
)
CREATE TABLE Assignments (EmpID char(10) NOT NULL,
ProjID int NOT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Personnel,
FOREIGN KEY (ProjID) REFERENCES Projects
)
This structure also allows you to store extra information about the
relations (e.g. when was the employee assigned to a project, wat role will
(s)he play in the project, etc)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Joining the table relationship from different database

Hi,
Did someone know how to join the table relationship
from different database.
DB1 - Database
Customer - Table
CustID _- Field
Cust_Name - Field
DB2 - Database
CustHist - Table
HistID - Field
Cust_ID - Field
I want to enforce the relationship for this two table in
different database. Let said, I delete one customer
record in DB1. The sql server will enforce the data
integity for me.
Any ideas, Thanks
DaronHi,
You can't enforce the foreign key relationship over two separate databases.
What you need to do is create insert and update triggers on the table and do
it that way.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> Hi,
> Did someone know how to join the table relationship
> from different database.
> DB1 - Database
> Customer - Table
> CustID _- Field
> Cust_Name - Field
> DB2 - Database
> CustHist - Table
> HistID - Field
> Cust_ID - Field
> I want to enforce the relationship for this two table in
> different database. Let said, I delete one customer
> record in DB1. The sql server will enforce the data
> integity for me.
> Any ideas, Thanks
> Daron|||Thanks, I dont think this is an efficient way to enforce
the table relationship. Because I need to write the insert
and update triggers to my related table. How about my
related tables are many. This really make me headache.
Welcome more ideas.
Daron
>--Original Message--
>Hi,
>You can't enforce the foreign key relationship over two
separate databases.
>What you need to do is create insert and update triggers
on the table and do
>it that way.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL
Scribe Documentation
>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
Server Extended
>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.
Free icon extraction
>program
>http://www.ag-software.com. Free programming tools
>
>"Daron" <darontcw@.hotmail.com> wrote in message
>news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
>> Hi,
>> Did someone know how to join the table relationship
>> from different database.
>> DB1 - Database
>> Customer - Table
>> CustID _- Field
>> Cust_Name - Field
>> DB2 - Database
>> CustHist - Table
>> HistID - Field
>> Cust_ID - Field
>> I want to enforce the relationship for this two table in
>> different database. Let said, I delete one customer
>> record in DB1. The sql server will enforce the data
>> integity for me.
>> Any ideas, Thanks
>> Daron
>
>.
>|||Daron,
There is no other way to enforce relationships over different databases.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:075001c38e10$ece30df0$a301280a@.phx.gbl...
> Thanks, I dont think this is an efficient way to enforce
> the table relationship. Because I need to write the insert
> and update triggers to my related table. How about my
> related tables are many. This really make me headache.
> Welcome more ideas.
> Daron
> >--Original Message--
> >Hi,
> >You can't enforce the foreign key relationship over two
> separate databases.
> >What you need to do is create insert and update triggers
> on the table and do
> >it that way.
> >
> >
> >--
> >I hope this helps
> >regards
> >Greg O MCSD
> >http://www.ag-software.com/ags_scribe_index.asp. SQL
> Scribe Documentation
> >Builder, the quickest way to document your database
> >http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
> Server Extended
> >Property Extended properties manager for SQL 2000
> >http://www.ag-software.com/IconExtractionProgram.asp.
> Free icon extraction
> >program
> >http://www.ag-software.com. Free programming tools
> >
> >
> >"Daron" <darontcw@.hotmail.com> wrote in message
> >news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> >> Hi,
> >> Did someone know how to join the table relationship
> >> from different database.
> >>
> >> DB1 - Database
> >> Customer - Table
> >> CustID _- Field
> >> Cust_Name - Field
> >>
> >> DB2 - Database
> >> CustHist - Table
> >> HistID - Field
> >> Cust_ID - Field
> >>
> >> I want to enforce the relationship for this two table in
> >> different database. Let said, I delete one customer
> >> record in DB1. The sql server will enforce the data
> >> integity for me.
> >>
> >> Any ideas, Thanks
> >> Daron
> >
> >
> >.
> >sql

Joining tables based on string key - bad idea?

The data already in the tables allows me to obtain what I need by drawing a relationship based on two columns that have nvarchar values but I have noticed that generally tables are related through integer keys. I want to know whether there are downsides to doing this, specifically if the join condition could 'wrongly' fail due to the string nature of the join criteria and thus cause missing rows in my resulting table.

Its typically not that great of an idea because it is going to take SQL SErver longer to join on these fields because they are larger than integer fields. The larger the field value, the longer it takes to compare (and in this case, join).
Tim|||

As Tim indicated, string values for JOINs is generally not a good idea. It has to do with how many bytes of data that has to be stored and read from the indexes. The shorter the values, the quicker index searching becomes.

However, if the string values are 'short' ( < 10 characters ), and the columns are indexed, it will most likely perform fine for you. The variables include the total number of rows in the table, amount of table activity, etc. I would NOT allow these string keys to be easily (if ever) changed.

|||

If you use character columns...

it's also a good idea to put adequate constraints on the columns or define foreign references to help ensure the quality of the data, so you don't end up with broken relationships

Joining and Union Issue

Hi,
I have a problem which is hard to describe but I will try. I have table
that has a relationship one to many with another table and it has a
relationship many to many inside it self. Each record had a field that is
called fparent and fparent can relate to the primary field fcomponet. I am
trying to query this table based on pulling all the records based on a join
with the main table. However, inside this resultset there could be more
records to pull if the fparent has more records associated with it. Right
now my query does a 3 level look up (UNION three temp tables together)
however there is a possibility of a 99 level lookup. Is there a way to do a
loop to lookup the multiple level association inside this table?
ThanksJake,
You'd probably get a better response if you posted the DDL (incl PK and FK),
sample data and desired resultset.
See:
http://www.aspfaq.com/etiquette.asp?id=5006
HTH
Jerry
"jake" <jp@.broncos.com> wrote in message
news:%23fByDfA1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a problem which is hard to describe but I will try. I have table
> that has a relationship one to many with another table and it has a
> relationship many to many inside it self. Each record had a field that is
> called fparent and fparent can relate to the primary field fcomponet. I
> am trying to query this table based on pulling all the records based on a
> join with the main table. However, inside this resultset there could be
> more records to pull if the fparent has more records associated with it.
> Right now my query does a 3 level look up (UNION three temp tables
> together) however there is a possibility of a 99 level lookup. Is there a
> way to do a loop to lookup the multiple level association inside this
> table?
> Thanks
>|||Here is the DDL
CREATE TABLE [dbo].[inbm] (
[fcomponent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fcrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fetm] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fparent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fprev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fqty] [numeric](15, 5) NOT NULL ,
[freqd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fltol] [bit] NOT NULL ,
[fnoperno] [numeric](4, 0) NOT NULL ,
[timestamp_column] [timestamp] NULL ,
[identity_column] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Basically, I query this table based WHERE fparent = value and I get a
resultset. In that resultset there could be a fcomponet that is a fparent
for another fcomponent record and so on. I need to be able to get all the
sub levels under when fparent = value.
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uMTmvOB1FHA.1252@.TK2MSFTNGP09.phx.gbl...
> Jake,
> You'd probably get a better response if you posted the DDL (incl PK and
> FK), sample data and desired resultset.
> See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> HTH
> Jerry
> "jake" <jp@.broncos.com> wrote in message
> news:%23fByDfA1FHA.3376@.TK2MSFTNGP14.phx.gbl...
>|||Jake,
See if these help:
Maintaining Hierarchies
http://www.windowsitpro.com/Article.../8826.html?Ad=1
and
Manipulating Hierarchies with UDFs
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=16123&DisplayTab=Article[
/url]
HTH
Jerry
"jake" <jp@.broncos.com> wrote in message
news:OMTCWJC1FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Here is the DDL
> CREATE TABLE [dbo].[inbm] (
> [fcomponent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fcrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fetm] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fparent] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fprev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fqty] [numeric](15, 5) NOT NULL ,
> [freqd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fltol] [bit] NOT NULL ,
> [fnoperno] [numeric](4, 0) NOT NULL ,
> [timestamp_column] [timestamp] NULL ,
> [identity_column] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> Basically, I query this table based WHERE fparent = value and I get a
> resultset. In that resultset there could be a fcomponet that is a fparent
> for another fcomponent record and so on. I need to be able to get all the
> sub levels under when fparent = value.
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uMTmvOB1FHA.1252@.TK2MSFTNGP09.phx.gbl...
>

JOIN the only relationship method?

In the old days..with DbaseIII, if two tables were indexed on an ID field, a
t
the top of the program, I would "open" this relationship by using the right
syntax and "boom" ..it's done.. the record pointers are at the top of each
table waiting for a command.
In SQL, it appears the that JOIN statement is what sets up the
"relationship" only when I execute a join with a million records in each
table, it takes 15 seconds for the relationship to be "established". It
appears that during the JOIN, a scan takes place and all the records are in
both tables are "positioned" during a sequential scan of the main table.
Which wouldn't be so bad if it were a one time deal, but it's in a SP that
gets repeatedly called by a client application.
In Dbase III, because of the indexed fields, it knows to just positions the
record pointer at the top of each table..it doesn't have to scan through all
the tables "relating" all the records one by one each time the relationship
is established.
There has to be a quicker way to relate tables in these more modern
languages, but i'm not sure where to start looking.
Thanks,
Don
declare @.msym varchar(200)
declare @.mcat varchar(200)
declare @.msid varchar(200)
select @.msym = 'c'
select @.mcat = 'FINC'
select @.msid = 'Fly'
select distinct m.* from main m
left join sym2 s on s.id = m.id
left join cat2 c on c.id = m.id
left join servid2 i on i.id = m.id
The ID field in all 4 tables are INT and indexed.
The ID field in Main is unique. The ID fields in the other 3 fields are not
unique.The fact that data retrieval isn't constrained by pointers or
hierarchies in the database is a major advantage of relational
databases. SQL isn't truly relational but it does follow the model to
that extent.
Your real question should be "How do I optimize my queries?". But the
query you posted is a woeful example with which to tackle that
question. What's the point of retrieving the entire contents of the
tables in a single operation? Why have you used SELECT * instead of
specifying only the required columns? Why have you used DISTINCT? Very
likely this query will result in undesirable and slow scans and sorts -
but if this is your idea of how to write queries then you should
probably take a course before you do anything more. What are you
*really* trying to achieve?
David Portas
SQL Server MVP
--|||Here's the rest of the Select statement...you may be right, it may be a
design problem, i don't know.
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
or i.servid in (select str from ter_charlist_to_table(@.msid, DEFAULT))
sym, cat and servid are tables with 2 fields...ID and a varchar field. The
ID field's are unique. The varchar field can have repeating values. for
example,
1001 DELL
1004 DELL
1007 DELL
What needs to be returned is all fields from the Main table if the request
is for DELL.
What I'm trying to do is have 1 main table "Main" with unique ID field..be
related to 3 other tables on ID where the ID in these tables is not unique.
There is a varchar field in Main that has values like this:
"DELL,IBM,WHATEVER"...
A client wants all fields returned from Main that have the word "DELL" in th
e
"David Portas" wrote:

> The fact that data retrieval isn't constrained by pointers or
> hierarchies in the database is a major advantage of relational
> databases. SQL isn't truly relational but it does follow the model to
> that extent.
> Your real question should be "How do I optimize my queries?". But the
> query you posted is a woeful example with which to tackle that
> question. What's the point of retrieving the entire contents of the
> tables in a single operation? Why have you used SELECT * instead of
> specifying only the required columns? Why have you used DISTINCT? Very
> likely this query will result in undesirable and slow scans and sorts -
> but if this is your idea of how to write queries then you should
> probably take a course before you do anything more. What are you
> *really* trying to achieve?
> --
> David Portas
> SQL Server MVP
> --
>|||> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
> or i.servid in (select str from ter_charlist_to_table(@.msid, DEFAULT))
Populate a table with those values before you execute the statement. That
way you can ensure the function only gets called once.

> ere is a varchar field in Main that has values like this:
> "DELL,IBM,WHATEVER"...
Deliminted lists of values in columns are a fundamental weakness. If these
are a discrete known set of values then put them in a related table.
David Portas
SQL Server MVP
--

Join one to many relationship query

I have 2 tables 'contacts' and 'history' I want to search 3 fields the body fields in the contacts table... and also the subject and body field in the history table all looking for like '%Winn%' and where contacts.address is not null.. I am having a heck of a time trying to do this... can anyone help please I want to return address and contactID.

GhornetI think that this should work... I am a little new at this though...

Select contact.contactId, contact.address from contacts
inner join contacts
where (history.body = contacts.body and contacts.address is NOT NULL) and history.body like '%Winn%'

Good luck.|||Sorry I meant:

inner join history|||Won't that join where the bodys are the same.. I don't want that...
Thanks

I have 2 tables 'contacts' and 'history'
I want to search 3 fields the body fields in the contacts table with like '%Winn%'
and also the subject and body field in the history table also looking for like '%Winn%'
I want to make sure that contacts.address and contacts.city is not null..
I want to return firstname, lastname, spouse, salutation, address, city, state, zip and contactID.

Ghornet|||something like this maybe:

select c.contactid, c.address from contacts c
where exists (select 1 from ((select contactid from history where subject like '%Winn%') union (select contactid from history where body like '%Winn%')) x where c.contactid = x.contactid)|||oh, and of course:

...and c.address is not null|||hmmm, I think I missed the body field in the contacts table. Anybody?|||How do they join? By contactID? If so, also, you mention the columns (there are no fields in relational theory, but we all know what you mean) you want to return but not from which table so I assume from the contacts table.

select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject not '%Winn%' and
a.body like '%Winn%' and
b.body like '%Winn%' and
a.address is not null
and a.city is not null

Did I miss anything?|||i think that is what I want thanks a bunch for the help

select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city, a.state, a.zip a.contactID
from contacts a
join history b on a.contactid = b.contactid
where b.subject like '%Winn%' or
a.body like '%Winn%' or
b.body like '%Winn%' and
a.address is not null
and a.city is not null|||I think it is not.

Better put some parenthesis around that complicated WHERE clause just to be sure:

select a.firstname, a.lastname, a.spouse, a.salutation, a.address, a.city,
a.state, a.zip, a.contactID
from contacts a
inner join history b on a.contactid = b.contactid
where (b.subject like '%Winn%' or a.body like '%Winn%' or b.body like '%Winn%')
and a.address is not null
and a.city is not null

blindman

Join one to many relationship

hi all geeks,

I have a problem regarding joins.

I have 2 tables,

Customer ( Customer_code, Agent1, Agent2, Agent3)

Agent( Agent_Code, Agent_name)

and data is like this:

Customer_codeAgent1Agent2Agent3112322343143

Agent Table

Agent_CodeAgent_name1X2Y3Z4P5Q

Now I want to retrieve all customer_code with their corresponding agent names, like

1 X Y Z

2 Y Z P

3 X P Z

, any suggestions please,

thanks a lot.

You can use a query like this

Select Customer_code,

(select agent_name from Agent where agent_code=Agent1),

(select agent_name from Agent where agent_code=Agent2),

(select agent_name from Agent where agent_code=Agent3)

from Customer

|||

thanks a lot Mr. Abdul

|||

if you really need relations use this code:

createtable #Cust( customer_codeint,

Agent1int,

Agent2int,

Agent3int)

createtable #agents(AgentCodeint,

AgentNamevarchar(10))

insertinto #cust

SELECT 1,1,2,3

insertinto #cust

SELECT 2,2,3,4

insertinto #cust

SELECT 3,1,4,3

insertinto #agents

SELECT 1,'X'

insertinto #agents

SELECT 2,'Y'

insertinto #agents

SELECT 3,'Z'

insertinto #agents

SELECT 4,'P'

insertinto #agents

SELECT 5,'Q'

select*from #Cust

select*from #agents

Select customer_code,

a1.AgentName [Agent1Name],

a2.AgentName [Agent2Name],

a3.AgentName [Agent3name]

from #cust

leftjoin #agents A1

on Agent1= A1.AgentCode

leftjoin #agents A2

on Agent2= A2.AgentCode

leftjoin #agents A3

on Agent3= A3.AgentCode

droptable #cust

droptable #agents