CODEWITHSUNDEEP
mysqlsql
Ragith Thomas
Ragith Thomas

Reputation: 105

SQL query to display the row of data which combines two tables and does not exists in other table

Thanks in advance. I am using a mysql database.

I have three tables carts, courses and checks. I need a row of data which combines carts and courses tables and should not exist in checks table, and I am passing userid = $id from my php function and carts table status = 'Completed'. Here the userid is same for both the tables carts and checks.

I have written this SQL query 

select c.* 
from carts c 
where NOT EXISTS (select 1 
                  FROM checks 
                  WHERE checks.order_id = c.order_id 
                  AND c.exam_id = checks.exam_id) 
AND c.userid = $id 
AND c.status ='Completed'

But this is not showing the exam name, instead it's showing the exam id because i didn't join the table . so I am slightly confused about that

Courses table:

id | name    |
1  | PMP     |
2  | CAPM    |
3  | Prince2 |

Carts table:

    id |userid | username | orderid             | exam_id |status
    1  |13     | Gautham  |437b310v671N888M6720 | 1       |Completed
    2  |13     | Gautham  |437b310v671N888M6720 | 2       |Completed
    3  |13     | Gautham  |437b310v671N888M6720 | 3       |Completed
    4  |14     | Rakesh   |678g310v671N888M6720 | 3       |Completed

Checks table :

    id |userid | username | orderid             | exam_id |
    1  |13     | Gautham  |437b310v671N888M6720 | 1       |
    2  |13     | Gautham  |437b310v671N888M6720 | 2       |

I need a output which combines carts and courses table but the data should not exist in checks table and i am passing userid = $id from my php function and carts table status = 'Completed':

1  |13     | Gautham  |437b310v671N888M6720 | Prince2  |Completed
2  |14     | Rakesh   |678g310v671N888M6720 | Prince2  |Completed

Upvotes: 0

Views: 54

Answers (3)

Didarul Alam
Didarul Alam

Reputation: 79

Try it hope you will get your expected out put.

If you have any further query regarding you can fell free knock me.

SELECT carts.id,carts.userid,carts.username,carts.orderid,carts.exam_id,carts.status,courses.name FROM carts,courses where carts.exam_id = courses.id and carts.status="Completed" and carts.id not in (SELECT exam_id FROM checks WHERE 1)

enter image description here

Upvotes: 1

lndim
lndim

Reputation: 199

Try this: 

SELECT c.*, cs.*
FROM carts c
LEFT JOIN courses cs ON c.exam_id = cs.id
LEFT JOIN checks ch ON c.userid = ch.userid AND c.exam_id = ch.exam_id
WHERE c.userid = $id AND c.status ='Completed' AND ch.id IS NULL

Upvotes: 0

apomene
apomene

Reputation: 14389

SELECT * FROM COURSES JOIN CARTS
ON COURSES.ID=CARTS.ID WHERE CARTS.USERID NOT IN
(SELECT USERID FROM FROM CHECKS)

Upvotes: 0

Related Questions