Francisco Tapia
fhtapia at gmail.com
Sat Jan 31 15:57:26 CST 2009
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=TXT_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=TXT_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 > >