how to combine multiple select query into one?

Question

i have three tables with structure

CREATE TABLE IF NOT EXISTS `refrence` (
  `products_ref_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `categories_id` int(11) NOT NULL,
  `Product_property` varchar(255) NOT NULL,
  `Product_reference` varchar(50) NOT NULL,
  PRIMARY KEY (`products_ref_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;


CREATE TABLE IF NOT EXISTS `product_details` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `categories_id` int(11) NOT NULL,
  `FieldA` varchar(30) NOT NULL,
  `FieldB` varchar(50) NOT NULL,
  `FieldC` varchar(255) NOT NULL,
  `FieldD` varchar(255) NOT NULL,
  `FieldE` varchar(255) NOT NULL,
  `FieldF` varchar(255) NOT NULL,
  `Field_desc` text NOT NULL,
  PRIMARY KEY (`products_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;

Here i am extracting Product_reference value from refrence table with respect to Product_property & categories_id as

SELECT Product_reference FROM refrence where categories_id=3 AND Product_property ='xxx'

Now this Product_reference is the field name in product_details table suppose result is abc. So i want to extract distinct abc from product_details table as

SELECT Distinct abc FROM product_details where categories_id=3

Now i want to combine both the query to one, What is the way to write these queries in to single one.

I tried this

SELECT Distinct (
SELECT Product_reference FROM refrence where categories_id=3 AND Product_property ='xxx'
) as aa FROM product_details where categories_id=3

But its not working, may be i am missing some syntax.

"Bit its not working" - Can you be more specific? What's the error? — Mark Byers
– Mark Byers, Commented Apr 28, 2012 at 13:36
categories_id is it same for all table....I mean are you using it as foreign key? — Rohit Choudhary
– Rohit Choudhary, Commented Apr 28, 2012 at 13:43
From looking at your query, it's not very clear why you're searching refrence for records where categories_id=3 yet product_details for records where categories_id=1 - and your explanation of what your query is intended to do is very hard to understand. Perhaps you can clarify? — eggyal
– eggyal, Commented Apr 28, 2012 at 13:44

GDP · Accepted Answer · 2012-04-28 14:22:52Z

1

Without knowing what isn't working, it would appear that you're trying to accomplish this:

SELECT r.product_reference
FROM refrence AS r 
LEFT JOIN categories AS c ON (c.categories_id = r.categories_id AND r.categories_id=3 AND     r.product_property='zzz')
WHERE c.categories_id=1

As pointed out by Eggyal, this will always return nothing because of the impossible condition of category_id=1 AND category_id=3, so either: They both have to be 1 or both have to be 3 OR They're completely different "categorys" that are unrelated, or just happen to have the same names.

edited Apr 28, 2012 at 14:22

answered Apr 28, 2012 at 13:49

GDP

8,1636 gold badges48 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

That is ALWAYS going to return an empty recordset due to conflicting filters on categories_id, on which the tables have been joined.

GDP Over a year ago

Oops...darned good point..cut/paste too quickly from my editor without trying enough data.

Collectives™ on Stack Overflow

how to combine multiple select query into one?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related