[dba-SQLServer] Newbie needs help with select statement

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






More information about the dba-SQLServer mailing list