CODEWITHSUNDEEP
mysql
EdzJohnson
EdzJohnson

Reputation: 1165

Include two different tables in MySQL query

Firstly I'm sorry. This is probably a rather basic question but I'm very new to this and couldn't find the answer on Google. I've the following MySQL query:

SELECT * FROM profile WHERE status = 'completed' ORDER BY RAND() LIMIT 6

The member accounts in my database have two tables. Profile & Members. Profile includes details such as their job title, company, etc. and then Members stores their MD5 password, full name and cookie token. Now, both have a status column. The profile one has three options: not, needphoto and completed. The members status column has another three: inactive, active and terminated. I need a command that will select

from profile where status = 'completed' and members where status = 'active'

How can I achieve that? So the query will select six random members who've got active and completed profiles? Thank you in advance!

Upvotes: 0

Views: 203

Answers (4)

Luceos
Luceos

Reputation: 6720

SELECT p.* FROM `profile` AS p, `members` AS m WHERE p.`ID` = m.`ID` AND p.`status` = "completed" AND m.`status` = "active"

You can use two tables in one query like above. You will probably need to add the column ID to join them together. It's advised to make these primary.

Please note the p.* is necessary otherwise SQL will give an error that two fields exists with the same name in the tables (status and ID)

Also try to use backticks for tables and columns, especially when using names that might be used by MySQL; e.g. status and ID.

Upvotes: 0

Veer Shrivastav
Veer Shrivastav

Reputation: 5496

First of all you will need to make a column in both the tables sharing same values for common entry. Foreign Key and Primary Key.

say both the tables has one id column.

So here is your SQL:

  select * from profile as p and member as m where p.id=m.id and p.status='completed' and m.status='active'

Upvotes: 1

Farkie
Farkie

Reputation: 3337

you should look at Joins.

Try something like:

SELECT * FROM profile p
 LEFT JOIN members m ON m.profile_id = p.id
WHERE p.status = "completed" and m.status = 'active'

You may need to change the LEFT JOIN line, it depends what the link is between the 2 tables. It might just be m.id = p.id

Upvotes: 0

John Conde
John Conde

Reputation: 219804

Without knowing what the names of the columns are that links the two tables I can't give an exact query but this should show you what the basic concept is.

SELECT 
    * 
FROM 
    profile,
    members
WHERE 
    profile.membersid = members.id
AND profile.status = 'completed' 
AND members.status = 'active'
ORDER BY 
    RAND() 
LIMIT 6

Upvotes: 0

Related Questions