mysql query, two select

Question

Hi,

As soon as I apologize because I do not know or be able to explain exactly trouble.

How get value from table user_address.

how to pass user ID in the second "select".

select id, name, age, 
    (select address
    from user_address
    where user_id = ??user.id
    ORDER BY address_name
    LIMIT 1) AS address
from user

Answer 1

As an addendum to what already exists, you should probably not be relying on the specific order of rows in the database to give some sort of semantic meaning. If you have some better way of identifying which address you're after, you could use a join, such as:

select id, name, age, address 
from user
inner join user_address
on user.id=user_address.user_id
where address_type='Home'

(adjust the where clause to whatever)

Answer 2

Hi

I assumed that you want to get something like the first address for a user (each user may have a couple of addresses)

-there is another option that you want to find the first persone that lives in a given address (The solution below doesn't address this case)

SELECT u.id,u.name,u.age,a.ua as address 
FROM
(
    SELECT * FROM users
) u
    INNER JOIN
(
    SELECT userID, MIN(address) AS ua
    FROM user_address
    GROUP BY userID
) a
on u.id = a.userID

The syntax is for SQLServer - if you use MSAccess(you can use First and not min)

Hope it helps Asaf