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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ScottNL1Author Commented:
adding this

WHERE p.link_id IS NULL

gives me no results
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
Aaron TomoskySD-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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.