SQL HELP (Should be simple)

Results 1 to 2 of 2

Thread: SQL HELP (Should be simple)

  1. #1
    JD007 Guest

    Default SQL HELP (Should be simple)

    Desired Result: 1 row per AppID <BR><BR>Problem: One to many relationship on 2 tables and the same field name needs to be combined on the 1 row per AppID. (Also cannot create temp table and require for solution) (To be clear see example)<BR><BR>(Channel)Table 1:<BR>ChannelID, Name<BR>1, JD Dogs<BR>2, KT Dogs<BR><BR>(Contacts) Table 2:<BR>ChannelID, ContactID, Address<BR>1,1,123 Main St<BR>1,2,John Jacob<BR>1,3,123 Main St<BR>2,1,234 West St<BR>2,2,Katie Farwell<BR>2,3,234 Main St<BR><BR>Issue: As you can see the client is storing the OwnerName in the address field where contactID = 2<BR><BR>Desired result<BR>ChannelID,Name,Address,OwnerName<BR>1,JD Dogs,123 Main St,John Jacob<BR>2,KT Dogs,234 Main St,Katie Farwell<BR><BR>A straight select should be simple enough but I am stuck. Thanks in advance.<BR>&#062;&#062;JD

  2. #2
    Join Date
    Dec 1969

    Default Many-to-Many?

    I think you have a many to many relationship. One channel can be assigned to many addresses and one address can be assigned to many channels, right?<BR><BR>If that is true, you need a third table to link the two main tables. In that case your tables will have the following schema:<BR><BR>Channel: ChannelID, Name<BR>Contacts: ContactID, Address<BR>Channel_Contact: ChannelID, ContactID<BR><BR>To join the tables use this SQL:<BR><BR>Select Channel.ChannelID, Channel.Name, Contacts.Address<BR>FROM Channel INNER JOIN Channel_Contact<BR>ON Channel.ChannelID = Channel_Contact.ChannelID <BR>INNER JOIN Contacts<BR>ON Contacts.ContactID = Channel_Contact.ContactID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts