CODEWITHSUNDEEP
sqljoin
user766346
user766346

Reputation: 3

I need help with two select statements using join

Here are the two statements I want in one:

SELECT pictures.picture, users.fname, users.lname, users.email, users.phone
FROM users, pictures
WHERE users.userid = 1
AND pictures.userid = users.userid;

and

SELECT pictures.picture, users.fname, users.lname, posts.post, 'post' AS 
type
FROM users, posts, pictures
WHERE users.userid = posts.userid
AND posts.postid > 0
AND users.userid =1
AND pictures.userid = users.userid
GROUP BY time

Previously I've been using a UNION statement but I can't do that in this case since the amount of columns in the two statements don't match.

Thanks in advance.

Upvotes: 0

Views: 140

Answers (4)

dmc
dmc

Reputation: 2684

You can use a UNION if you make your column lists match up. For example:

SELECT pictures.picture, users.fname, users.lname, users.email, users.phone, NULL, NULL
FROM users, pictures
WHERE users.userid = 1
AND pictures.userid = users.userid
UNION ALL
SELECT pictures.picture, users.fname, users.lname, NULL, NULL, posts.post, 'post' AS 
type
FROM users, posts, pictures
WHERE users.userid = posts.userid
AND posts.postid > 0
AND users.userid =1
AND pictures.userid = users.userid
GROUP BY time

Upvotes: 1

Bryce Siedschlaw
Bryce Siedschlaw

Reputation: 4226

This may be a dumb question and I may not be understanding something, but why do you need two statements?

SELECT pictures.picture, users.fname, users.lname, 
FROM users, pictures
WHERE users.userid = 1
AND pictures.userid = users.userid;

SELECT pictures.picture, users.fname, users.lname, posts.post, 
    'post' AS type, users.email, users.phone
FROM users
LEFT JOIN posts ON (posts.userid = users.userid AND posts.postid > 0)
LEFT JOIN pictures (pictures.userid = users.userid)
WHERE users.userid =1
GROUP BY time

Hope that helps.

Upvotes: 0

Chris Morgan
Chris Morgan

Reputation: 2080

SELECT pictures.picture, users.fname, users.lname, posts.post, 
       'post' AS type, users.email, users.phone
FROM users 
INNER JOIN pictures ON users.userid = pictures.userid
INNER JOIN posts on users.userid = posts.userid
WHERE users.userid = 1
AND posts.postid > 0
GROUP BY time;

Upvotes: 1

Brad
Brad

Reputation: 5488

SELECT p.picture, u.fname, u.lname, u.email, u.phone, p.post, 'post' AS type
FROM users u
LEFT JOIN posts p ON u.userid = p.userid
LEFT JOIN pictures pic ON pic.user_id = u.userid
WHERE u.userid = 1
GROUP BY time

You may change the LEFT JOIN to an INNER JOIN if you require posts or pictures in the result set.

Upvotes: 1

Related Questions