Mark A Matte
markamatte at hotmail.com
Sat Jan 31 06:49:06 CST 2009
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, Mark A. Matte> Date: Fri, 30 Jan 2009 21:43:12 -0800> From: fhtapia at gmail.com> To: dba-sqlserver at databaseadvisors.com> Subject: Re: [dba-SQLServer] Newbie needs help with select statement> > In that case simply omit the Where clause no need to have it if you are not> using it.> > SELECT * FROM contacts AS ctcs> INNER JOIN contacts_organization_types AS cot ON ctcs.ID = cot.id> > -Francisco> http://sqlthis.blogspot.com | Tsql and More...> > > On Fri, Jan 30, 2009 at 9:39 PM, jean-paul <jnatola at hotmail.com> wrote:> > >> > you flew way over my head on that one-> >> > in the db all contacts have a value in the org type field from the second> > table> >> >> > I want ALL the contacts from the contacts table> > and Im NOT looking for a specific criteria from the contacts_org_tpes I> > just want it to return the value> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > Jean-Paul Natola> >> >> >> >> >> >> > > Date: Fri, 30 Jan 2009 21:25:49 -0800> > > From: fhtapia at gmail.com> > > To: dba-sqlserver at databaseadvisors.com> > > Subject: Re: [dba-SQLServer] Newbie needs help with select statement> > >> > > Jean Paul,> > > You'll want to define your where criteria... that is:> > >> > > SELECT * FROM contacts AS ctc WHERE NAME = 'Criteria'> > >> > > this way the results are what you expect> > >> > > To join two tables you will want to associate the corresponding fields> > >> > > Such as:> > >> > > SELECT * FROM contacts AS ctcs> > > INNER JOIN contacts_organization_types AS cot ON ctcs.ID = cot.id> > > WHERE ctcs.name = 'Criteria'> > >> > > some interesting things are happening here... first I have used a table> > > alias that's where you see the table name contacts followed by the> > keyword> > > AS then an abbreviation of my liking (no reserved words) such as ctcs> > >> > > so I 'm telling the engine that I will from that point on substitute the> > use> > > of ctcs whenever it expects to see an explicit reference for the table> > name> > > contacts. Also if you take a look at the criteria, you'll notice that I> > > have a field name in there the name I took from your example but it> > should> > > be any valid name in your table that you want to search against... saying> > > WHERE 1 is a true statement, but does not reflect the case of WHERE id => > 1,> > > so you may want to revise your select statement.> > >> > >> > >> > > -Francisco> > > http://sqlthis.blogspot.com | Tsql and More...> > >> > >> > > On Fri, Jan 30, 2009 at 8:58 PM, jean-paul <jnatola at hotmail.com> wrote:> > >> > > >> > > > Hi all,> > > >> > > > So I was attempting to for the first time to get data using the select> > > > statment> > > >> > > > here was my first syntax> > > > SELECT * FROM `contacts` WHERE 1> > > >> > > > it returned all the records (as I wanted) 9050 records> > > > contact name address emaill etcc..> > > > now there is another table called contacts_organization_types> > > > I wanted the corresponding output to come out in the same query> > > >> > > > so i tried this> > > > SELECT * FROM `contacts`,`contacts_organization_types` WHERE 1> > > >> > > > now i got the same results as above- but instead of it adding the> > field> > > > to the list it returned each record from the contacts with every single> > > > cust_org_types for example> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > > first statement results;> > > >> > > > id first name last name email> > > > 1 jp natola> > > >> > > >> > > > second statment results;> > > >> > > >> > > >> > > > id first name last name email organaization type> > > > 1 jp natola NGO> > > >> > > >> > > > 1 jp natola MED> > > >> > > > 1 jp natola Donor> > > >> > > > 1 jp natola Education> > > >> > > > 1 jp natola Media> > > >> > > > How do i modify the statement to just give me the record with the> > actual> > > > value that is in the contacts record> > > >> > > > 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> > >> >> > _________________________________________________________________> > Windows Live™ Hotmail(R)…more than just e-mail.> >> > http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_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™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_012009