MySQL query with condition from 2 tables-CodePudding

I'm new to programing and mysql and stuff I think this is simple but not for me So, I have those two table Tickets and Ticket_user and I need to get all the fieds from the Table Ticket where user_id =15 and ticket_id >8

Table 1 : Ticket

id             name          ...... other fields
7              Tickte1            
8              Tickte2  
9              ticket3
10             ticket4
11             Tickte5

Table 2 : Ticket_users

   id   ticket_id     User_id
   1     7             15           
   2     8             16
   3     9             15
   4     10            15
   5     11            8

Result

  ticket_id     name          ...... other fields
  9             Tickte3             
  10             ticket4

How can I achieve this ? appreciate any help.

CodePudding user response：

Both queries will work.

The second is faster if you have to handle a lot of data First uses IN clause

SELECT 
    id, name
FROM
    Ticket
WHERE
    id IN (SELECT 
            ticket_id
        FROM
            Ticket_users
        WHERE
            User_id = 15 AND ticket_id > 7);

second uses INNER JOIN

SELECT 
    id, name
FROM
    Ticket t
        INNER JOIN
    (SELECT 
        ticket_id
    FROM
        Ticket_users
    WHERE
        User_id = 15 AND ticket_id > 7) ut ON t.id = ut.ticket_id;

CodePudding user response：

You need to look into JOIN CLAUSE.

What your looking for is something like this

SELECT t.* 
FROM Ticket t LEFT JOIN Ticket_users tu ON t.id = tu.ticket_id 
WHERE tu.user_id = 15 AND t.id > 8