INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

find products not listed

find products not listed

(OP)
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?

RE: find products not listed

Hi

CODE --> MySQL

select
l.productid, l.productname

from productlist l
left join (
  select distinct
  productid

  from productreview
) r using (productid)

where r.productid is null 
Warning The above code was not tested

Feherke.
feherke.github.io

RE: find products not listed

(OP)
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

CODE

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

ALL comments

CODE

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

RE: find products not listed

Quote:


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

Quote:


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.

RE: find products not listed

(OP)
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

CODE

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

RE: find products not listed

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.

RE: find products not listed

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

RE: find products not listed

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.

RE: find products not listed

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)

RE: find products not listed

(OP)
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

CODE

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

RE: find products not listed

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

CODE

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

CODE

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

CODE

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

CODE

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

CODE

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

CODE

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

RE: find products not listed

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close