0

I am building a small database app for friends where table 1 is contacts and table 2 is users. I can find email on both (One as the loggued in user and the other as the owner of the contact)

SELECT *
  FROM contacts
 WHERE contacts.username = users.email

I try to show all contacts fields where username is equal to already loggued in users (email)

Thanks you very much!

Jacob
  • 14,463
  • 65
  • 207
  • 320
MAC
  • 13
  • 1
  • Please tell us what is stopping you from showing all contacts fields where username is equal to already loggued in users (email) – DeanOC Jun 28 '18 at 04:30
  • 1
    Possible duplicate of [SELECT Data from multiple tables?](https://stackoverflow.com/questions/5055770/select-data-from-multiple-tables) – Dinesh Ghule Jun 28 '18 at 04:38
  • So...what's the question??? – Eric Jun 29 '18 at 22:59

3 Answers3

0

Try the following. Also, I would suggest you learn about joins in SQL.

SELECT *
  FROM contacts
INNER JOIN
  users on contacts.username = users.email
Ankur Patel
  • 1,413
  • 8
  • 14
0

It sounds like you're trying to JOIN two tables together. Ideally, you don't want to use the email as the primary key on a table (the smaller the data, the faster your JOIN will be); a better option would be to add an auto-incrementing Id (integer) to both the Contacts and Users tables, set as the primary key (unique identifier). Joining on integers is much faster, as integers are 4 bytes per row, vs string which (in MySQL) is 1 per character length (latin1 encoding) + 1 byte.

Anyway, back to the original question. I believe the query you're looking for (MySQL syntax) is:

SELECT c.Id, c.Col1, u.Col2, ...
FROM contacts AS c
INNER JOIN users AS u ON u.email = c.username

Additionally, I would avoid the use of *, as it slows down the query a bit. Instead, try to specify the exact columns you need.

ChoNuff
  • 814
  • 6
  • 12
  • Hi Chosun, I have read a lot about the subject since. Thanks for all of those tips! About the MySQL, it's still showing all the table (Not showing only those rows that matches u.email = c.username) I've checked syntax, database exact name etc... nothing to do :( I'll keep searching on the subject – MAC Jun 29 '18 at 16:45
  • Hi @MAC, it's going to show all the columns because I'm using *. If you want to return only certain columns, do the following: SELECT c.Id, c.Name, u.Name... – ChoNuff Jun 29 '18 at 22:48
  • I updated my response to reflect that. Also, if you find this answer to answer your question, please mark it as the accepted answer. Thanks! – ChoNuff Jun 29 '18 at 22:55
0

Use Inner Join:

SELECT *
  FROM contacts as c
INNER JOIN
  users as u on u.email = c.username
Dinesh Ghule
  • 3,423
  • 4
  • 19
  • 39