Left Join Using 2 Columns

[beyzad]

Hi there.

 

I have a query like the following:

 

SELECT *
FROM `products`
LEFT JOIN `partner_products`
USING ( `vendor_id` , `product_id` )
WHERE `partner_products`.`vendor_id` = '23060001'
AND `partner_products`.`partner_id` = '1'

 

As i read in many pages, the following query must export every rows in `products` table even if there is no matching row in `partner_products` table. but this only results rows with matching conditions. any suggestion please?

 

Thanks.

[Barand]

If a table is left-joined the conditions on that table must be in the join and not in the WHERE clause

 

SELECT * FROM `products` p
LEFT JOIN `partner_products` pp
   ON p.vendor_id = pp.vendor_id
   AND p.product_id = pp.product_id
   AND pp.`vendor_id` = '23060001'
   AND pp.`partner_id` = '1'

[beyzad]

Hi.

 

Thanks sir.

 

I tried your suggestion. also this one:

 

SELECT * FROM `products` p
LEFT JOIN `partner_products` pp
    ON p.vendor_id = pp.vendor_id
    AND p.product_id = pp.product_id
    AND p.`vendor_id` = '23060001'
    AND pp.`partner_id` = '1'

 

But in both, All rows, even rows without `vendor_id` = '23060001' are returned.

Thanks.

[Barand]

Hi there.

 

I have a query like the following:

 

SELECT *
FROM `products`
LEFT JOIN `partner_products`
USING ( `vendor_id` , `product_id` )
WHERE `partner_products`.`vendor_id` = '23060001'
AND `partner_products`.`partner_id` = '1'

 

As i read in many pages, the following query must export every rows in `products` table even if there is no matching row in `partner_products` table. but this only results rows with matching conditions. any suggestion please?

 

Thanks.

Hi.

 

Thanks sir.

 

I tried your suggestion. also this one:

 

SELECT * FROM `products` p
LEFT JOIN `partner_products` pp
 ON p.vendor_id = pp.vendor_id
 AND p.product_id = pp.product_id
 AND p.`vendor_id` = '23060001'
 AND pp.`partner_id` = '1'

 

But in both, All rows, even rows without `vendor_id` = '23060001' are returned.

Thanks.

 

Do you want all, or not?

 

My query should get all products but matching data from partner_products only where the criteria are true.

[beyzad]

Hi sir.

 

Ofc i want all rows, but with conditions. The condition is `vendor_id` = '23060001'

 

Thanks.

[Barand]

As i read in many pages, the following query must export every rows in `products` table even if there is no matching row in `partner_products` table. but this only results rows with matching conditions. any suggestion please?

 

Thanks.

 

In your original post your problem was it only gave rows with matching condition.

 

Now you are saying you want rows with matching conditions.

 

I suggest you put some effort into writing an unambiguous specification of what you want the query to do before anyone wastes any more time.

[beyzad]

Hi.

 

I didn't mean to waste your time sir.

 

But as i said in topic title, I need to join 2 tables using 2 columns. I have also some condition for my left table.

 

table1:

 

vendor_id | product_id | .....

-----------------------------------

1 | 1 | .....

1 | 2 | .....

1 | 3 | .....

2 | 1 | .....

2 | 2 | .....

2 | 3 | .....

 

table2:

 

vendor_id | product_id | partner_id | .....

-----------------------------------

1 | 1 | 1

1 | 3 | 1

 

 

Now i want to export all rows with vendor_id=1 from table1, also export matching rows with same vendor_id and product_id from table2

 

I need something like this:

 

vendor_id | product_id | partner_id | .....

-----------------------------------

1 | 1 | 1

1 | 2 | NULL

1 | 3 | 1

 

 

I wish i was clear this time.

 

Thanks.

Edited November 27, 2012 by beyzad

[Barand]

In that case you need a WHERE condition on the product table

SELECT * FROM `products` p
LEFT JOIN `partner_products` pp
ON p.vendor_id = pp.vendor_id
AND p.product_id = pp.product_id
WHERE p.`vendor_id` = '23060001'

 

Still not sure what you want when partnr_id is not '1'

Edited November 27, 2012 by Barand

[beyzad]

Hi.

 

You wrote the same query i used in my 1st post in this topic.

 

my result on this query is this:

 

vendor_id | product_id | partner_id | .....

-----------------------------------

1 | 1 | 1

1 | 3 | 1

 

May be i am experiencing some version bug or something? Is this neede to post my whole DB so you can see the confusing results?

 

Thanks.

[Barand]

As I said, I am still waiting to know how the partner rows should be treated - with and without the '1' in partner_id.

 

You haven't specified that bit yet.

[beyzad]

Hi again.

 

Here is my structure:

 

CREATE TABLE IF NOT EXISTS `partners` (
 `partner_id` int(11) NOT NULL AUTO_INCREMENT,
 `vendor_id` varchar( COLLATE utf8_unicode_ci NOT NULL,
 `partner_user` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `partner_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `partner_name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
 `partner_site` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 `partner_phone` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `partner_cell` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `partner_email` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 `partner_bank` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 `partner_account` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
 `partner_credit_card` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `partner_active` enum('yes','no') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',
 `partner_valid` enum('yes','no') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',
 PRIMARY KEY (`partner_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `partner_products`
--
CREATE TABLE IF NOT EXISTS `partner_products` (
 `partner_id` int(11) NOT NULL,
 `vendor_id` varchar( COLLATE utf8_unicode_ci NOT NULL,
 `product_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `partner_product_percent` int(11) NOT NULL,
 UNIQUE KEY `partner_id` (`partner_id`,`vendor_id`,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
 `vendor_id` varchar( COLLATE utf8_unicode_ci NOT NULL,
 `product_category_id` int(11) NOT NULL,
 `product_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `product_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `product_weight` int(11) NOT NULL,
 `product_price` int(11) NOT NULL,
 `product_active` enum('yes','no') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',
 KEY `vendor_id` (`vendor_id`,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

I have a table named products that keeps product information of all vendors.

I also have a partner table that keeps partner information for each vendor.

There is a third party table named partner_products that keeps assigned products from products to partners.

 

Vendors will choose which product can be assigned to which partner. That means there may be only 10 out of 100 products available for a partner.

 

Now i want to make a query that list all products of a vendor. also for those that been assigned for a specified partner, the other fields from partner_products needed.

 

Thanks.

[Barand]

SELECT * FROM `products` p
LEFT JOIN `partner_products` pp
ON p.vendor_id = pp.vendor_id
AND p.product_id = pp.product_id
AND pp.`partner_id` = '1'
WHERE p.`vendor_id` = '23060001'

[beyzad]

YOU ARE HERO.

 

 

Thanks a lot.