SELECT FROM WHERE l.id != p.link_id

I am doing something wrong but for the life of me can't figure it out.

I want to select items that have not yet in the products table

i have tryed the following:


$table = "sometable";

$query = $db->sql("SELECT l.*
              FROM $table l, ".$table."_products p
              WHERE l.id != p.link_id
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");

And

$query = $db->sql("SELECT l.*
              FROM $table l
              LEFT JOIN ".$table."_products p
              ON l.id != p.link_id
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");

The result is including items that are allready in ".$table."_products?

Please advice.
LVL 1
ScottNL1Asked:
Who is Participating?
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
I have a much simpler solution. Just use a not in with a nested select. This may not paste right in as I'm typin on my mobile:
$query = $db->sql("SELECT l.*
              FROM $table l
              WHERE l.id not in (select p.link_id from  ".$table."_products p)
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");
0
 
Beverley PortlockCommented:
On this

query = $db->sql("SELECT l.*
              FROM $table l
              LEFT JOIN ".$table."_products p
              ON l.id != p.link_id
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");

change your WHERE clause to pick out things from the products table that are NULL (becuase they are not there) like so

query = $db->sql("SELECT l.*
              FROM $table l
              LEFT JOIN ".$table."_products p
              ON l.id != p.link_id
              WHERE p.link_is IS NULL
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");
0
 
Beverley PortlockCommented:
WHERE p.link_is IS NULL

should be

WHERE p.link_id IS NULL

Sorry...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ScottNL1Author Commented:
adding this

WHERE p.link_id IS NULL

gives me no results
0
 
ScottNL1Author Commented:
This is just so you know what i have so far (note the $db is my class of mysql functions)

$query = $db->sql("SELECT l.*
              FROM $table l
              LEFT JOIN ".$table."_products p
              ON l.id != p.link_id
              WHERE p.link_id IS NULL
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");
     echo mysql_error();
     print_r($db->select());
     die;
0
 
Lukasz ChmielewskiCommented:
Did you try
ON l.id <> p.link_id
?
0
 
ScottNL1Author Commented:
First thing i tried when it didn't work the first time

here the table structure if it could be any more help

CREATE TABLE `link_vektonl_products` (
  `link_id` int(11) NOT NULL,
  `name_db1` varchar(255) DEFAULT NULL,
  `name_db2` varchar(255) DEFAULT NULL,
  `supplier_id_db1` int(11) DEFAULT NULL,
  `supplier_id_db2` int(11) DEFAULT NULL,
  `description_db1` text,
  `description_db2` text,
  `image_db1` varchar(255) DEFAULT NULL,
  `image_db2` varchar(255) DEFAULT NULL,
  `id_db1` int(11) DEFAULT NULL,
  `id_db2` int(11) DEFAULT NULL,
  `code_db1` varchar(128) DEFAULT NULL,
  `code_db2` varchar(128) DEFAULT NULL,
  `supplier_code_db1` varchar(128) DEFAULT NULL,
  `supplier_code_db2` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`link_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `link_vektonl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `db1` int(11) DEFAULT '0',
  `db2` int(11) DEFAULT '0',
  `valid` tinyint(4) DEFAULT '0',
  `product_type` smallint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=816 DEFAULT CHARSET=latin1;
0
 
MarkusIdCommented:
Hi!

I think what you need is an outer join:


$query = $db->sql("SELECT l.*
              FROM $table l, ".$table."_products p
              WHERE l.id is null
                AND l.id(+) = p.link_id
              ORDER BY l.product_type,l.id
              LIMIT 50
              ");

Open in new window

0
 
ScottNL1Author Commented:
Not at the office, i'll try both Monday morning.
Thanks.
0
 
ScottNL1Author Commented:
Workes thank you, also tryed marks outer join but got a syntax error at (+).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.