CODEWITHSUNDEEP
mysqlsqldatabasejoin
travis
travis

Reputation: 159

MySQL Join help from two tables

I'm sure there are a bunch of examples on S.O. but I don't even know what to search for since I'm very limited when it comes to working with databases, so I thought I'd post here and hope to get some help.

I have two tables, Users and Profiles. There can be multiple profiles for one email, and there can multiple profiles for in the Users table.

I need to write a query that selects all of the profileIDs from the Profile table where the email matches ("[email protected]" for example. This should return a bunch of profiles (1, 23, 444, 9203, 034) But I only want to return the profileIDs (and username) that are in the Users table. (for example only profileID 444 is in the User's table.

Profile Table Example:

profileID | primaryEmail | name | otherStuff |

User Table:

profileID | username | password | otherUserStuff|

This is probably extremely simple, but it would take me quite a bit of time to research, and I'm on a time crunch.

Something like(?):

    SELECT t1.profileID, t2.username 
    FROM Profiles as t1 INNER JOIN Users as t2 ON t1.profileID = t2.profileID 
    WHERE t1.primaryEmail="[email protected]"

I'm a frontend dev that picked up a task that has a bunch more backend work than I thought.. and I'm already a couple days past the due date..

Upvotes: 0

Views: 84

Answers (3)

bramaningds
bramaningds

Reputation: 41

SELECT ProfileID, Username
  FROM Profile
  JOIN Users USING (ProfileID)
 WHERE primaryEmail = "[email protected]"

It will show data that exists in both table which has same profileID

Upvotes: 0

Muhammad Rizwan Kaim Khani
Muhammad Rizwan Kaim Khani

Reputation: 2080

You may write this query.

SELECT Profile.ProfileID, User.username from Profile INNER JOIN User ON Profile.ProfileID = User.ProfileID WHERE Profile.primaryEmail = "[email protected]"

Try it.

Thanks.

Upvotes: 1

Walter H.
Walter H.

Reputation: 51

The sql documentation from mysql's documentation site should help you form the query you're looking for, but for what a working join example would look something like this:

SELECT column1 FROM table1 tableAlias
INNER JOIN table2 table2Alias ON tableAlias.column = table2Alias.column
WHERE tableAlias.column = this AND table2Alias.column = that

NOTE: There's more than one type of join you can use, so take a moment to read through the documentation, and determine which one is best suited for solving your problem.

Upvotes: 1

Related Questions