CODEWITHSUNDEEP
mysql
tee
tee

Reputation: 155

sql on mysql about join

the code below provide a result too much Infact i want to list the customer that never buy somethink How can i fix the code below

SELECT
webboard.listweb.id,
webboard.listweb.iditempro,
webboard.listweb.url,
webboard.listweb.useradddate,
webboard.listweb.expiredate,
webboard.prorecord.urlpostonweb
webboard.prorecord.urlpostonweb
FROM
webboard.listweb ,
webboard.prorecord

Where listweb.id Not In 
             (select webboard.prorecord.idlist From webboard.prorecord )

Upvotes: 1

Views: 58

Answers (1)

Tony
Tony

Reputation: 10327

Using the syntax

FROM
webboard.listweb ,
webboard.prorecord

will perform a cartesian, or cross, join on the tables involved. So for every row in the table listweb all the rows in prorecord are displayed.

You need to use an INNER JOIN to only select the rows in listweb that have related rows in the prorecord table. What are the fields which identify the rows (your Primary Keys) and what is the name of the foreign key field in the prorecord table?

EDIT: Just re-read the question and comments and I see you want the rows in listweb which do not have an entry in prorecord

Your SELECT will then look like:

SELECT
  webboard.listweb.id,
  webboard.listweb.iditempro,
  webboard.listweb.url,
  webboard.listweb.useradddate,
  webboard.listweb.expiredate,
  webboard.prorecord.urlpostonweb
  -- webboard.prorecord.urlpostonweb -- You have this field twice
FROM webboard.listweb LEFT JOIN webboard.prorecord
    ON webboard.listweb.id = webboard.prorecord.idlist -- I'm guessing at the foreign key here
WHERE webboard.prorecord.idlist IS NULL

Upvotes: 2

Related Questions