Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Joining to sql statements

Status
Not open for further replies.
Jun 5, 2006
28
US
I have two sql statements. The first has a unique primary key on name, whereas the second is a one to many relationship on name. I would like to join the two queries so the result looks like:

John Smith, 234 Jones Rd, 430-293-3928, note1
John Smith, 234 Jones Rd, 430-293-3928, note2
John Smith, 234 Jones Rd, 430-293-3928, note3
John Smith, 234 Jones Rd, 430-293-3928, note4
Bill Jones, 259 Red River Rd, 939-239-4893, note1
Bill Jones, 259 Red River Rd, 939-239-4893, note2

The only stipulation is that you can modify the statements, I have sort of dummied down the statements as they are quite complex. So you can add stuff or put modifiers between the statements. For example if there is a way to just add some sort of union statment in between the two or something else along those lines.


select name, address, phone number
from address_book

select name,notes
from notes_table
 
You wanted this ?
SELECT A.name, A.address, A.phone_number, N.notes
FROM address_book A
INNER JOIN notes_table N ON A.name = N.name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Each person may have several notes, by doing an inner join you are only grabbing the first note. You also modified the original two queries. (I have simplified the queries so they dont take up several pages). I want the queries to remain untouched but rather for someone to help me add something around the query or inbetween the two queries to get the desired results.

Thanks
 
Sorry, your right. My fault. Is it possible to use the two statements i provided but keep them as is and add something in between them or around them to get the same behavior?

Thanks
 
SELECT * FROM (
select name, address, phone number
from address_book
) A INNER JOIN (
select name,notes
from notes_table
) N ON A.name = N.name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
BTW name by itself is probably the single worst thing you could use as a unique primary key because names are not unique. What do you do when you have two John Smiths? Trust me even if this a company list and you don't have any repeat names now, you will eventually. My boss at one company was one of four people with the same name. I'd really rethink using this as the primary key if at all possible.

"NOTHING is more important in a database than integrity." ESquared
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top