Asger Blond
ab-mi at post3.tele.dk
Sat Jan 31 16:58:09 CST 2009
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