CODEWITHSUNDEEP
mysqlsql
user3925697
user3925697

Reputation: 619

select from 2 tables in a single query

I have 2 tables, article and image.

In order to extract all articles of a particular user, I need to:

How can I achieve the same thing with just a single query?

Upvotes: 3

Views: 25450

Answers (5)

Luke
Luke

Reputation: 4063

You could use a join or a subquery.

Subquery:

The subquery (The second query contained in brackets) will be executed and the ID(s) that are returned will be used to identify which rows from the initial query to grab. So if you imagine the subquery returns (1, 2, 3), the initial query will be looking for rows WHERE articleid is IN that list.

SELECT * FROM image WHERE articleid IN (SELECT id FROM article WHERE userid=100);

Join:

Here you are telling MySQL that the articleid in image is related to the id column in the article table. Then you can just pull columns from either table. This is a watered down explanation so I recommend you read the link. There are various types of JOIN that will result in different results, so you should read about LEFT, RIGHT, OUTER and INNER joins to make sure the resultset fits your needs. In this case, JOIN is equivalent to an INNER join.

SELECT
    image.col1,
    image.col2,
    image.colX,
    article.col1,
    article.col2,
    article.colX
FROM image
JOIN article ON article.id = image.articleid;

Upvotes: 11

sudhansu63
sudhansu63

Reputation: 6180

You can use @Doctus 's answer, but when joining the table you need to take care of proper joining

  1. Inner Join (the default Join) : when the coresponding records present in both the table in participation
  2. Outer Join (Can be Left, RIGHT, or FULL) : when One or Both table dont have some matching records

.

In your case, some user might not have images.

--//This will also get the articles without any images
SELECT article.* , image.*
FROM article 
LEFT OUTER JOIN image
ON article.articleid  = image.articleid 
WHERE userid=100;

Upvotes: 1

munsifali
munsifali

Reputation: 1732

You can read more about select statements, joins, etc. here: MySQL Reference - Select Syntax

SELECT *
FROM image
JOIN article ON article.id = image.articleid;

Upvotes: 2

justrohu
justrohu

Reputation: 589

You can do it like this 

SELECT * FROM images WHERE articleid IN (SELECT id FROM article WHERE userid=100; );

Hope this will help you.

Upvotes: 0

PSR
PSR

Reputation: 40318

SELECT * FROM image WHERE articleid  
 in(SELECT articleid   FROM article WHERE userid=100)

Upvotes: 1

Related Questions