[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> > > Asger> -----Oprindelig meddelelse-----> Fra: dba-sqlserver-bounces at databaseadvisors.com> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jean-paul> Sendt: 31. januar 2009 23:20> Til: dba-sqlserver at databaseadvisors.com> Emne: Re: [dba-SQLServer] Newbie needs help with select statement> > > value is differnt in con_org_typ> > id org_typ internal> 804 NGO Y> 805 EDU N> 806 MED Y> 807 DON Y> > > and in the the tables the primakary key is id Jean-Paul Natola >> Date: Sat, 31 Jan 2009 13:57:26 -0800> From: fhtapia at gmail.com> To:> dba-sqlserver at databaseadvisors.com> Subject: Re: [dba-SQLServer] Newbie> needs help with select statement> > so is the ID value for each row in> contacts_organization_types = 1 for each> row?> > this maybe why you get all> the organization types as discussed earlier. you> will need a unique> identifing value between the contacts table and the>> contacts_organization_types.> > what you want to do is join the values> between your contacts table with a> related field in your> contacts_organization_types table. that is... your>> contacts_organization_types table requires an additional field OR... you>> need a more meaningful value for the id field in the>> contacts_organization_types table.> > so let's assume that your first row in> your contacts table is:> id first name last name email> 1 jp natola> > and> let's assume that the corresponding value for this row in>> contacts_organization_type is NGO.> > Then you will need a value in the> contacts_organization_table that can join> the value from your contacts> table to just give you a single row from the> contacts_organization_type> table.> > Generally I start with a contacts table and follow with a> organization type> table, the Organiztion type table contains a pkid field> that is set as the> identity value and is auto-incrementing. likewise I have> the same setup for> the contacts table. then I build a 3rd junction table> that joins the values> from the contacts table to the values in the> organization type table thus my> junction table looks like so:> > PKID>> FKContactID> FKOrgTypeID> > this table contains the ID from the contacts> table in FKContactID and the> the ID from the Organization Table in field> FKOrgTypeID.> > Thus my new select statment would look more like this:> >> SELECT ctc.*, cot.orgtypename,> FROM Contacts AS ctc> INNER JOIN> ContactsOrgJunct AS COJ on ctc.ID = COJ.FKContactID> INNER JOIN> Contacts_organization_Type AS COT on COJ.FKOrgTypeID = COT.ID> > the> Junction table must be updated with each corresponding organization type>> field so that you have proper results...> > I hope this is more clear> > >> -Francisco> http://sqlthis.blogspot.com | Tsql and More...> > > On Sat, Jan> 31, 2009 at 10:09 AM, jean-paul <jnatola at hotmail.com> wrote:> > >> > SELECT> * FROM contacts_organization_typesLIMIT 0 , 30> >> > id organization_type> internal_only Jean-Paul Natola > Date:> > Sat, 31 Jan 2009 10:05:55 -0800>> From: fhtapia at gmail.com> To:> > dba-sqlserver at databaseadvisors.com> Subject:> Re: [dba-SQLServer] Newbie> > needs help with select statement> >> Jean-Paul,> I guess I misunderstood you> > a bit on what you were trying to> achieve.> let's change the terminology a> > little bit so that we're all> talking the same> language,> > A field is also> > known as a column so that> would be ID, First_name,> Last_name, the results> > are rows... so you are> receiving 24 rows with the> fields ID, First_name,> > Last_name, email> > in> order to know what to join by, can you do a select of> > the 2nd table and>> let me know the field names?> > select * from> >> contacts_organization_types> -Francisco> http://sqlthis.blogspot.com |> >> Tsql and More...> > > On Sat, Jan 31, 2009 at 8:54 AM, jean-paul <> >> jnatola at hotmail.com> wrote:> > >> > the contacts table has 24 fields id> >> first_name last_name etc.., I want> > every single record and field from> >> that table.the contacts_org_types table> > has three fields id> >> organization_type and internal, I want only the> > organization_type field>> > from that table, so , I *believe* that i should see> > 25 fields in my> >> result 24 from table 1 and 1 from table 2- thanks> > Jean-Paul Natola >> >> From: markamatte at hotmail.com> To:> > dba-sqlserver at databaseadvisors.com>> >> Date: Sat, 31 Jan 2009 12:49:06 +0000>> > Subject: Re: [dba-SQLServer]> >> Newbie needs help with select statement> > > I> > hate to make> >> assumptions...but the subject says 'newbie'...and Francisco> > suggested> the> > fields to join on...not Jean Paul.> > Jean Paul, what fields> > from> each> > table are you trying to join on(the actual field names)? > > If> >> the> > example below was used 'as is' and there is an 'ID' field in each> table> >> > but its not the relationship between these two tables...won't> give the> >> > results.> > As long as I am 'assuming'...I think the scenario> might be a> >> > translation table...and the first attemp produced a> cartesian join(no> > joins)> > and listed each contact having every kind of> cust_org_types...so> > the> > confusion might be what fields to join on.> >> Francisco's Example> > modified:>> > > SELECT * FROM contacts AS ctcs> INNER> JOIN> > contacts_organization_types AS> > cot ON ctcs.cust_org_types => cot.id> >> > I'm guessing there is a> > cust_org_types field in the contacts> table...and> > there is a value that needs> > to be translated?> > If I am> off> > target...please let me know.> > Thanks,> >>> >> >> _________________________________________________________________> >> >> Hotmail(R) 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>> >> _________________________________________________________________> >> Hotmail(R) 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> > _________________________________________________________________> Windows Live™ Hotmail®:…more than just e-mail. > http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore> _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> 
_________________________________________________________________
Windows Live™ Hotmail®:…more than just e-mail. 
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore_012009