Stuart McLachlan
stuart at lexacorp.com.pg
Sat Jan 31 21:00:23 CST 2009
With 731000 rows, I doubt that it's a menu management table as we know it in access. Looking at the field names, I'd guess that it is a link table. Looks like the clown who designed the system built one humungous link table for the whole system rather than using Keys. You'll probably need and additional where clause here: 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' AND swb.table_right = 'contacts_organization_types' On 1 Feb 2009 at 2:16, Asger Blond wrote: > 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 LiveTM 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 > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >