jean-paul
jnatola at hotmail.com
Sat Jan 31 10:17:59 CST 2009
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,> > > 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> _______________________________________________> 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/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_012009