Help with SQL

[DHillard]

I have a problem with an SQL query. I have a huge book on SQL, but I don't know what I'm attempting to do is called, therefore I don't know what to look up. I will explain it as simply as possible.

Two Tables.

Table1 would look like this:

PK   PersonnelID
     FirstName
     LastName
FK   MailingAddressID
FK   PhysicalAddressID

Table2 would look like this:

PK    AddressID
      AddressLine1
      AddressLine2
      City
      State
      ZIP

I want to write a query to return the following in the result set:

FirstName, LastName, MailAddressLine1, MailAddressLine2, MailCity, MailState, MailZIP, PhysicalAddressLine1, PhysicalAddressLine2, PhysicalCity, PhysicalState, PhysicalZIP

All of these fields would be in every returned record, whether a Physical Address or Mailing Address existed or not for each person.

Can anyone help me out be either posting a relevent example, or telling me what I should look for in my SQL book.

Thank you.

David

[viperbyte]

What you want to look up in your book is the subject of JOINS. I'll take a shot at it in a lil bit when things settle down here in case you haven't figured it out by then.

[DHillard]

Viperbyte,

I am familiar with joins. But I think this is something more than that. The table that holds the addresses would hold two addresses for each person, so I need to take the two rows for each person and combine them on one row along with the information from the first table.

It would be a simple join if there was only one address ID to link.

David

[VictorN]

What and how did you try and what does not work?

[jcaccia]

You need to include two instances of Table2 in your query (use aliases).

[DHillard]

Figured it out. It's a subquery in the select statement.

VictorN,

Thanks for your help too, but the problem was that I didn't know where to start, or what it was called that I needed to do, therefore I COULDN'T look it up.

I found the following on the internet, and I just need to change the table and field names and this should do exactly what I'm trying to do.

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101

[jcaccia]

You have to get more familiar with joins. I already told you what you need is to reference Table2 twice in your query using aliases. Something like this:

SELECT t1.FirstName, t1.LastName,
    m.AddressLine1 AS MailAddressLine1,
    m.AddressLine2 AS MailAddressLine2,
    m.City AS MailCity,
    m.State AS MailState,
    m.ZIP AS MailZIP,
    p.AddressLine1 AS PhysicalAddressLine1,
    p.AddressLine2 AS PhysicalAddressLine2,
    p.City AS PhysicalCity,
    p.State AS PhysicalState,
    p.ZIP AS PhysicalZIP
FROM Table1 t1, Table2 m, Table2 p
WHERE t1.MailingAddressID = m.AddressID
AND t1.PhysicalAddressID = p.AddressID

[viperbyte]

Hey DHillard; I see you have a solution. Isn't troubleshooting a blast??

[DHillard]

You have the best solution jcaccia