mysql select two tables problem

[krissy86]

---

Hi guys, i know most of you naka agi na ani, im still new and basic pa ako nahibaw.an sa mysql then diri ko na ungot kay nag ka lisod na ako query

how do you join two tables?

this is my problem.

-I have a 'user' table and 'notifications' table
-in the 'user' table naa koy user_id, email
-in the 'notifications' table naay user_id, status

I want to get the email sa user table where ang notifications.status = 1

need help

[krissy86]

btw, i found my own way but also want to know if naa pay mas nindot na solution

SELECT DISTINCT users.email, notifications.user_id FROM users, notifications WHERE notifications.user_id = users.user_id AND notifications.status = 1

[kelv]

try this:

SELECT u.email, n.user_id 
 FROM users u JOIN notifications n 
 ON u.user_id = n.user_id
 WHERE n.status = 1

[krissy86]

thanks sir, it worked the same as my query, pero is there any difference in using JOIN and just plainly FROM two tables

btw, dili na diay needed ang DISTINCT sa ako query

[kelv]

naay difference ang JOIN and select FROM 2 tables sir kung mogamit ka ug other types of JOIN. like LEFT JOIN, RIGHT JOIN ug OUTER JOIN. pero kanang JOIN/INNER JOIN pareho ra mn cguro.

edit:
pareha ra diay according to wiki. ang nkalahi ra ky ang tawag. explicit ug implicit join notation. heheh

[silent-kill]

gamit ka distinct kong gusto ka unique ang result, gamit ka join kong naa relationship ang 2 or more tables.

[RenDel]

SELECT u.email, u.user_id
FROM users u
WHERE EXISTS(
SELECT n.user_id
FROM notifications n
WHERE n.user_id = u.user_id AND n.status = 1)

[krissy86]

hi guys, i've came across another problem. How do i select all fields from one table and only 1 or 2 fields from another table?

[MarlboroMan]

select t1.*,t2.col1,t2.col2 from table1 t1,table2 t2
//condition sample
where t1.id=t2.id and t2.id=something

[jeramel21]

hi guys, i've came across another problem. How do i select all fields from one table and only 1 or 2 fields from another table?

-I have a 'user' table and 'notifications' table
-in the 'user' table naa koy user_id, email
-in the 'notifications' table naay user_id, status

SELECT a.*, b.user_id, b.status FROM user a, notifications b WHERE a.user_id=b.user_id AND b.status=1