CODEWITHSUNDEEP
phpsql
Jake Funchion
Jake Funchion

Reputation: 81

Need help grabbing all the data from two tables in SQL

I have a form set up where the user can enter in the username or lastname of a person. I then take either the username or lastname depending on what they entered and query the database and displaying all the userinformation and whether they have access to certain areas. There are two tables I need to query and get all the information for the user entered. I'm assuming you use a JOIN but I'm not quite sure how to do it. Right now I have the query to grab all the data from the users table I just need help adding in the second table. The two tables look like this

Users { uid username password firstname lastname email }

userAccess { uid assessment development learningmodule project }

The SQL I have so far is

    SELECT *
FROM Users
WHERE username = '$username' OR lastname = '$lastname'

Upvotes: 0

Views: 133

Answers (2)

Gustavo Rubio
Gustavo Rubio

Reputation: 10797

You don´t mention which database you are using, although I'm guessing MySql.

What you need to do is a simple inner join:

SELECT u.uid 
    u.username, 
    u.password, 
    u.firstname, 
    u.lastname, 
    u.email, 
    ua.assessment, 
    ua.development, 
    ua.learningmodule, 
    ua.project
FROM Users u
INNER JOIN UserAccess ua
    ON u.uid = ua.uid
WHERE u.firstname LIKE '%$username%' OR u.lastname LIKE '%$lastname%';

Do note that I'm not selecting the uid again in the UserAccess table as it is redundant since you already selected it from the Users table and since you are joining by that key you don't need it again.

Also note that instead of using the equal operator (=) I use the LIKE operator since there are more chances that you want to filter information based on partial info such as keywords and not full words.

I'd recommend you to have a look at a SQL tutorial for beginners. This one seems to be good enough.

I'd also recommend you to read the guidelines on how to post questions here or else you won't get much attention from anybody, plus they will downvote you :)

Upvotes: 0

Chris Rasco
Chris Rasco

Reputation: 2731

You are right you'll need to use a join. This type of join is called an Inner Join. You can achieve it like this:

SELECT * from Users, userAccess WHERE Users.uid = userAccess.uid AND Users.uid = #;

I'm guessing you'll have an id column that is the Primary Key for your userAccess table as well. The above query will provide the data from both tables in 1 record for every condition where the above statement is true.

If you are using MySQL you can read more about joins here:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 1

Related Questions