Joining to sql statements

[mike509123]

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

 

[PHV]

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

 

[mike509123]

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

 

[PHV]

by doing an inner join you are only grabbing the first note
Really ???
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

 

[mike509123]

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

 

[PHV]

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

 

[SQLSister]

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