CODEWITHSUNDEEP
phpmysql
Humphrey
Humphrey

Reputation: 2817

How to select from two tables and display only what you want?

I want to select from 2 different tables.

In the first table I want to select all, but I will display only what I want.

In the second table I want to select only the profile picture but even if a user does not have a profile picture his data from the user table should be selected.

I am using inner joins. Below is my code: 

SELECT * FROM
 tish_user INNER JOIN tish_images
 ON tish_user.user_id = tish_images.user_id
 WHERE tish_images.prof_image = 1

Upvotes: 0

Views: 125

Answers (5)

Red Gabanan
Red Gabanan

Reputation: 166

Try this:

Suppose you want to display the userID, firstname and lastname from the tish_user table and the prof_image from the tish_images table.

  SELECT tish_user.userd_id, tish_user.firstname, tish_user.lastname, tish_images.prof_image
FROM tish_user tish_user LEFT JOIN tish_image tish_image ON tish_user.user_id=tish_images.user_id WHERE tish_image.prof_image=1

I think this will do.

Upvotes: 1

usrNotFound
usrNotFound

Reputation: 2810

Try this way

SELECT * FROM
tish_user, tish_images
WHERE tish_user.user_id = tish_images.user_id
AND
tish_images.prof_image = 1;

I think this might help you.

Cheers bro

Upvotes: 1

Nick Pickering
Nick Pickering

Reputation: 3185

To select from two different tables, you should specify values from each table that you want, not using catch-all *. Using a LEFT JOIN instead of an INNER JOIN lets you connect the tables you are querying from on a single point. You can query any kind of relationship between the tables at that point.

This query will give you all the userids in tish_user returning the matching tish_images.prof_image record if prof_image is 1, NULL otherwise. 

SELECT 
    tish_user.user_id, 
    tish_images.prof_image 
FROM
    tish_user 
LEFT JOIN tish_images
ON tish_user.user_id = tish_images.user_id
AND tish_images.prof_image = 1

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT *
FROM
 tish_user U
    LEFT JOIN tish_images I
 ON U.user_id = I.user_id
   AND = I.prof_image = 1

Upvotes: 1

Vlad Preda
Vlad Preda

Reputation: 9910

Use LEFT JOIN instead of INNER JOIN.

SELECT * FROM
tish_user LEFT JOIN tish_images
ON tish_user.user_id = tish_images.user_id
WHERE tish_images.prof_image = 1

Explanation

LEFT JOIN selects all rows in the left table, even if there are no entries in the right table (in which case the columns for the right table will be NULL)

Also check RIGHT JOIN, it does the same thing with the right side :)

Upvotes: 1

Related Questions