find products not listed 1

[tyutghf]

I have two tables, a list products and their product id's, and a list of comments for the products. They go like this

product list
id | productid | product name

product review
id | productid | comment

I need to find which products don't have a comment, so need to look at each product in the list and see if there is a comment associated.

How do I output a list of product id's and names that have not had a review?

 

[feherke]

Hi

[b]select[/b]
l.productid, l.productname

[b]from[/b] productlist l
[b]left[/b] [b]join[/b] (
  [b]select[/b] distinct
  productid

  [b]from[/b] productreview
) r [b]using[/b] (productid)

[b]where[/b] r.productid [b]is[/b] [b]null[/b]

[small][maroon]Warning[/maroon] The above code was not tested[/small]

Feherke.
feherke.github.io

 

[tyutghf]

Hi Feherke

Thanks for the reply, that's not quite doing what I need as it seems to be outputting products that haven;t had a comment.

My explanation wasn't very good, here is what I do to get the seperate bits

all products

SELECT `productnumber`,`productname` FROM `products`

ALL comments

SELECT `prod_code`,`product_name`
FROM `comments`
WHERE `archived` = 1

I need a list of the products that don't appear in the comments table, i.e. those products that dont have an associated comment, the link between the two tables is productnumber and prod_code.

Many thanks

 

[jpadie]

as it seems to be outputting products that haven;t had a comment.

I need a list of the products that don't appear in the comments table, i.e. those products that dont have an associated comment

confused. by your own post feher's code is doing what you want.

 

[tyutghf]

Sorry my inability to follow instructions there :-
It is working great, but I'd like to display some columns from the comments table, but obviously I'm not calling these at any point.

In essence I need to

select
l.productid, l.productname, r.reviewdate, r.reviewarchive

from productlist l
left join (
  select distinct
  productid

  from productreview
) r using (productid)

where r.productid is null

I have tried reversing the code so that I select all from the comments table and distinct the productid from the list table but this outputs reviews that don;t have a product (these will be old products that are no longer available) which is not what I want to achieve.

I also tried adding an inner join to the query to grab the extra fields but this then returns an empty result set.

Is it possible to achieve the above?

Thanks

 

[jpadie]

may i ask why you are not doing this as two queries? an outer loop for the products and an inner loop for the comments on the product.

 

[tyutghf]

Hi jpadie, I'm happy to do it whatever way will work well. I imagined one query would be faster than two

 

[jpadie]

i would use two queries as i'm not sure you need things like the product name etc for every comment. so iterate the products table and then in the inner loop iterate the comments table.

 

[jpadie]

sorry. i confused myself.

you are trying to list products that don't have comments.

there is no point in outputting anything from the comments table as, by definition, there is nothing.

so i'm still confused as to what you actually want.

try providing some real world examples of your table data and your desired output from the query (queries)

 

[tyutghf]

Each product needs to have a comment placed on it every year, the product table contains just information about that product, the review table contains the review details and the date the review was conducted. So I need to filter the results by review date and add something like

where r.reviewdate between '2013-01-01' and 2013-12-31'

to get a list of products that have not had a review carried out on them in the last 12 months. I tried reversing the query to begin on the review table then left join on the product table but this broght back reviews in the system that didn't have a product (product no longer exists) which wasn't right.

Is what i want to achieve possible?

The info will be output to a webpage using php if that helps.

Thanks

 

[jpadie]

so you want a list of products that have no corresponding entry in the reviews table in a particular time period

ok. here goes.

i created two tables

CREATE TABLE `products` (
 `productid` int(10) NOT NULL AUTO_INCREMENT,
 `productname` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`productid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `reviews` (
 `reviewid` int(10) NOT NULL AUTO_INCREMENT,
 `productid` int(10) DEFAULT NULL,
 `reviewdate` date DEFAULT NULL,
 `reviewtext` mediumtext,
 PRIMARY KEY (`reviewid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

and put some minor dummy data in

INSERT INTO `products` (`productid`, `productname`) VALUES
(1, 'product 1'),
(2, 'product 2'),
(3, 'product 3');

INSERT INTO `reviews` (`reviewid`, `productid`, `reviewdate`, `reviewtext`) VALUES
(1, 1, '2013-01-01', 'first review'),
(2, 3, '2013-02-01', 'second review'),
(3, 1, '2012-01-01', 'third review');

then slightly adapting feher's query above I tried this

SELECT p . * , r.c
FROM products p
LEFT OUTER JOIN (
SELECT productid, COUNT( reviewid ) AS c
FROM reviews
WHERE YEAR( reviewdate ) =2013
GROUP BY productid
)r ON r.productid = p.productid
WHERE r.c IS NULL || r.c =0
GROUP BY p.productid

if I have understood your question properly, on the data set provided above this should yield only one row which should reflect product 2 which has had no reviews in 2013.

and the result is

productid	productname	c
2	        product 2	NULL

were I to change the date to 2012 both product 2 and product 3 would be returned as neither has reviews in 2012.

if you want a subset of year rather than all year then

BETWEEN CAST('2013-01-02' AS DATE) AND CAST('2013-12-05' AS DATE)

would be the way I approached it. unless we were looking for whole months in which case I'd probably use

YEAR(reviewdate) = 2013 AND MONTH(reviewdate) IN (1,3,5,6) //jan, mar, may, jun

 

[tyutghf]

That is a great help thank you jpadie, embelishing on selecting date ranges is also very interesting, I have never queried dates like that.

Thanks