Query Help - joining tables/union

dsrnu
dsrnu used Ask the Experts™
on
Hi everyone,

I was wondering if there was a way to join two tables together where:

- pull all information from table 1
- pull added information from table 2, if it exists (combine like table1.id = table2.id)

I tried using join (left, right, inner, outer, etc.) but no luck.
Table: seller

----------------
- id - user_id -
----------------
- 1  -   50    -
- 2  -   53    -
- 3  -   67    -
----------------

Table: user

-------------------------------------
- id - username - email             -
-------------------------------------
- 1  - jdoe     - jdoe@gmail.com    -

....

- 50 - suprman  - suprman@gmail.com -
- 51 - wndrwmn  - wndrwm@gmail.com  -
-------------------------------------

Desired Output:

Pull all data from "user" table and include "seller" data, if user.id = user_id exists.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I think you only want a user to be returned if there's also seller data available for that user? In that case use an inner join (just JOIN or INNER JOIN and not LEFT JOIN or RIGHT JOIN).
So you'd get:

SELECT * FROM user u JOIN seller s ON u.id=s.user_id;

If you want a resultrow for every single user no matter if it exists in the seller table too, then use an OUTER JOIN instead:

SELECT * FROM user u LEFT JOIN seller s ON u.id=s.user_id;

In the latter case you'll get NULL values for the seller columns in your result if the user doesn't exist in the seller table.
You can also use this syntax:

SELECT *
FROM user u, seller s
WHERE u.id = s.user_id;

Hope it helps.

Author

Commented:
rubene:

the second query was what i was looking for =)

when i get the results, how would i rename s.user_id to IS_SELLER so it's more meaningful?

Author

Commented:
SELECT u.id AS user_id, u.username, s.user_id AS is_seller FROM

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial