[dba-SQLServer] Newbie needs help with select statement

Asger Blond ab-mi at post3.tele.dk
Sat Jan 31 19:16:05 CST 2009


I don't think this is your linking table.
A table named "switchboard" (assuming that your "switboard" is a typo) is
normally used for managing menus, and you can't be sure that this kind of
table will do as a link between your contacts and contacts_organization_type
tables.
You could try this query (but as said I would look for another possible
linking table):

SELECT ctc.*, cot.organization_type,
FROM contacts AS ctc
INNER JOIN switchboard AS swb on ctc.ID = swb.table_left_id
INNER JOIN contacts_organization_type AS cot on swb.table_right_id = cot.ID
WHERE swb.table_left='contacts'

Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jean-paul
Sendt: 1. februar 2009 01:22
Til: dba-sqlserver at databaseadvisors.com
Emne: Re: [dba-SQLServer] Newbie needs help with select statement


ok  there is a table called switboard with 731,000 and i *think* it maybe it
the fields are;
 
 id        table_left  table_left_id  table_right
table_right_id
402611  contacts   226946       contacts_organization_types   726
 
 
is this what i need?
 
         Jean-Paul Natola > From: ab-mi at post3.tele.dk> To:
dba-sqlserver at databaseadvisors.com> Date: Sun, 1 Feb 2009 00:45:24 +0100>
Subject: Re: [dba-SQLServer] Newbie needs help with select statement> >
Jean-Paul,> > In that case I guess Francisco is right: you have a so called
"many-to-many> relationship" comprising tree table: contacts ->
"junctiontable" <-> contacts_organization_types.> What you need is find the
name of this "junctiontable" in your database (I> know this is easier to say
than to do...)> If you find a table in your databases which seems to be a
link between your> contacts and contacts_organization_types table then
please tell, and be sure> someone on this list will escort you the rest of
the way.> > Asger> > -----Oprindelig meddelelse-----> Fra:
dba-sqlserver-bounces at databaseadvisors.com>
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jean-paul>
Sendt: 1. februar 2009 00:11> Til: dba-sqlserver at databaseadvisors.com> Emne:
Re: [dba-SQLServer] Newbie needs help with select statement> > > the
contacts table does NOT have organization type- it has id, first, last,> add
, cty, st, zip, tel, fax, dte_created, email, etc.. but nothing> regarding
organization type- I will try you code and let you know- if been> on this
for the last 30 hours racking my brain- on a good note i read and> learned
alot since yesterday btw, I'm using MySQL and doing this via> phpMyAdmin- if
it makes any difference Jean-Paul Natola > From:> ab-mi at post3.tele.dk> To:
dba-sqlserver at databaseadvisors.com> Date: Sat, 31> Jan 2009 23:58:09 +0100>
Subject: Re: [dba-SQLServer] Newbie needs help with> select statement> >
Francisco may be is right about a third junction table> between your
contacts> and contacts_organization_types tables. But perhaps> it's more
simple that> that.> > You told us the tree fieldnames of your>
contacts_organization_types table> (id, organization_type, and>
internal_only) but you didn't tell us all names> of the 24 fields in your>
contacts table.> > My question is: do your contacts table have a field
named> something like> "organization_type_id"?> > In that case you might
have a> simple one-to-many relationship and your query> would be something
like:> >> SELECT ctcs.*, cot.organization_type FROM contacts AS ctcs> INNER
JOIN> contacts_organization_types AS cot ON ctcs.organization_type_id> =
cot.id> _________________________________________________________________>
Windows Live™ Hotmail®
more than just e-mail. >
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howi
> tworks_012009> _______________________________________________>
dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com>
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
http://www.databaseadvisors.com> > > >
_______________________________________________> dba-SQLServer mailing list>
dba-SQLServer at databaseadvisors.com>
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
http://www.databaseadvisors.com> 
_________________________________________________________________
Hotmail® goes where you go. On a PC, on the Web, on your phone. 
http://www.windowslive-hotmail.com/learnmore/versatility.aspx#mobile?ocid=TX
T_TAGHM_WL_HM_versatility_121208 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com






More information about the dba-SQLServer mailing list