CODEWITHSUNDEEP
mysqlsqldatabase
Nicolas Racine
Nicolas Racine

Reputation: 1061

Mysql Select with select result in an other table

I need some help, I don't know correct term of what im trying to do so im not able to find any example of what im trying to acheive.

Im hopping someone on here could just point me how how i could do this simple query and il be able to create one on my own.

SELECT * FROM
 `product_views` pv,`product_sales` ps
WHERE
 pv.`SessionId` = ps.`SessionId` {result of} ps.`ProductId` = 1

The syntax im trying to figure out is basacly how to get result of a query in a where clause.

Thanks!

Edit. I realize that the fact that I don't know correct terms of what im trying to achieve is confusing so il show how in this point of time I would get the same result but in two query

SELECT ps.`SessionId` FROM `product_sales` ps WHERE ps.`ProductId` = 1;

Then with that the result of that query I would have let say sessionId 20 ( to keep it simple ) And then i would have to re-query with

SELECT * FROM `product_views` pv WHERE pv.SessionId = {ValueOfLastQuery};

Upvotes: 1

Views: 82

Answers (2)

Donald
Donald

Reputation: 1200

A join would help you achieve what you're looking for. Here's a great introduction.

Basically, you'd want to do something like:

SELECT * FROM
product_views
LEFT JOIN product_sales
ON product_views.SessionId = product_sales.SessionId
WHERE product_sales.ProductId = '1'

Edit:

I think you could also use a subquery, which would look something like:

SELECT * FROM
product_views
WHERE SessionId = (SELECT SessionId from product_sales WHERE ProductId = '1')

Upvotes: 1

Sigfried
Sigfried

Reputation: 3110

How about a subquery:

 SELECT * FROM `product_views` pv 
 WHERE pv.SessionId IN 
    (SELECT ps.`SessionId` FROM `product_sales` ps WHERE ps.`ProductId` = 1)

Upvotes: 1

Related Questions