Outer Join SQL Help

shmick25

Hey, I am doing a bit of sql here and am getting data from multiple tables. All is good apart from where I have a table with multiple values for a user. Such as a user may have two address for instance. This is taken from the address table.

The sql I have is as follows:

SELECT u.user_id, u.title, u.lastname, u.firstname, u.gender,
u.birthdate, u.hphone u, d.pname, d.citizen, d.program,
a.paddress, a.city, a.state, a.country, a.postcode
FROM USERS u, STUDENT_DETAILS d,
USERS_ADDRESS a
WHERE (user_role = '6') AND (u.user_id = d.user_id) AND
(u.user_id = a.user_id)
ORDER BY u.lastname ASC

The problem is, if a user has two address, it will output their name twice eg

Joe Blogg Address 1
Joe Blogg Address 2.

What I want is:

Joe Blogg Address1, Address 2

Will I need to do a seperate sql statement here to achieve this? or can I do it all in the one statement?

Cheers

TTT

select DISTINCT(a.paddress)

shmick25

Still not working.. it is only throwingout theone address then..
eg: Joe Bloggs address 1

jfjohnson

Select Distinct will only give you 1 row per person if ALL the fields in the select clause are unique throught the return.

With multiple rows, you will need to Identify what address you are after for that person. If you know it is their primary address, then include a condition in the "Where" cluse to pull address_type = "Pimary".

If you are looking to get two rows back with dplicate data in each row with exception to the addresses, you will need to do an join on the address table and the user table with the keys. You will then get only the address row back with no data from the user table because MYSQL will give you NULLS for the "missing record matches from the user table. That is because you have a "Many to 1" relationship to user.

Hope this helps.