DISTINCT, JOINS on composite tables

Hi,

I have a products page that has an initial setting (selected by the client). Depending on the initial setting I need to grab DISTINCT products.

I grab the makeid and modelid from the initial settings table.

I have 2 composite tables because products can be associated to more than one make or model.

So in table 'product_make' I have 2 columns makeid and pid (product id) and in table 'product_model' I have modelid and pid (product id).

I need to grab distinct product(s) based on the initial setting makeid and modelid.

TABLES:
TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
);

TABLE `product_initial` (
  `modelid` int(11) NOT NULL,
  `makeid` int(11) NOT NULL
) ;

TABLE `product_make` (
  `pid` int(11) NOT NULL,
  `makeid` int(11) NOT NULL
);

TABLE `product_model` (
  `pid` int(11) NOT NULL,
  `modelid` int(11) NOT NULL
);

Some assistance on the QUERY would be much appreciated.

Thanks
Sythe-oneAsked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's start with this:
SELECT p.*
  FROM `product_initial` i
  JOIN `product_make` ma
    ON ma.makeid = i.makeid
  JOIN `product_model` mo
    ON mo.modelid = i.modelid
  JOIN products p
    ON p.id = ma.pid
   AND p.id = mo.pid
GROUP BY p.id

Open in new window

0
ozgurorhanCommented:
I guess angelll's query is basicly right if you dont count grouping. (If I didnt get it wrong)
SELECT p.id, i.makeid, i.modelid, p.name, p.description
  FROM `product_initial` i
  JOIN `product_make` ma
    ON ma.makeid = i.makeid
  JOIN `product_model` mo
    ON mo.modelid = i.modelid
  JOIN products p
    ON p.id = ma.pid
   AND p.id = mo.pid
GROUP BY p.id, i.makeid, i.modelid

Open in new window

0
Sythe-oneAuthor Commented:
Hi Guys

thanks for the quick response.

I get the following from both queries:
MySQL returned an empty result set (i.e. zero rows)

But there is data there... and I should get something back?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>But there is data there... and I should get something back?
data in the tables does not mean automatically that each query should return data.

now, the query will ONLY return data if there is at least 1 product which has an entry for makeid and modelid as such couple exists in the product_initial table...

you will have to double check the data, take 1 example where you think there must be data returned.
eventually, post the relevant data samples?
0
Sythe-oneAuthor Commented:
TABLE `product_initial`
values:
makeid = 7
modelid = 6

This only ever has 1 row of data (one makeid and one modelid)

I actually grab this prior to trying find the products associated to either the makeid or modelid.

So I was thinking that I needed a WHERE clause in my QUERY and it can show products associated to the model or make (doesn't have to be both)...

Does that make better sense?
0
Sythe-oneAuthor Commented:
Oh and I want to add that on this product page, after it is loaded, the user can select from drop downs to view products by make and/or model...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>doesn't have to be both...
that explains it :

SELECT p.*
  FROM `product_initial` i
  JOIN `product_make` ma
    ON ma.makeid = i.makeid
  JOIN `product_model` mo
    ON mo.modelid = i.modelid
  JOIN products p
    ON p.id = ma.pid
    OR p.id = mo.pid
GROUP BY p.id

Open in new window

0
Sythe-oneAuthor Commented:
I already have the initial values for makeid and modelid from the product_initial table, when I load the page...
makeid = 7
modelid = 6

When the client selects a different make and / or model (from drop downs), it should show related product(s). I will pass the values in a querystring like $_GET['makeid'] and / or $_GET['modelid'];

I want to grab the products related to those values, so I am not sure why I am querying the product_initial table without saying, somewhere, WHERE makeid = '$_GET['makeid'] OR modelid = '$_GET['modelid']''.

I am even more confused now...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't think you need that table, if you get the parameters:
SELECT p.*
  FROM products p
WHERE EXISTS( SELECT NULL 
                FROM  `product_make` ma
               WHERE ma.makeid = {$_GET['makeid']} 
                 AND ma.pid = p.id
            )
  OR EXISTS ( SELECT NULL
                FROM `product_model` mo
               WHERE mo.modelid = {$_GET['modelid']}
                 AND p.id = mo.pid
             )

Open in new window

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
Sythe-oneAuthor Commented:
angelIII:
I am very happy to say that I have tested that query extensively and it gives back the results as I expected!

You are truly amazing, I doubt I would've gotten this right without your help!

Thank you
0
Sythe-oneAuthor Commented:
Well done. Truly a genius and I see you are at the top of both leader boards!
0
Sythe-oneAuthor Commented:
I need to ask another question.

I modified the code and simply grabbed products related to a model.

After getting the unique products for a Make, I simply wanted to extract the associated Models for that product.

I am not able to do this without nesting queries...

$getModels = "SELECT * FROM product_model WHERE pid = '$prodid' ";
$gotModels = mysql_query($getModels) or die ("Error (get model ids): $getModels. " .mysql_error());
if (mysql_num_rows($gotModels) > 0) {
    while($rowmo = mysql_fetch_assoc($gotModels)){
      moid = $rowmo['moid'];
                                          
      $getmodelname = "SELECT * FROM model WHERE id = '$moid' AND makeid = '$curMakeId'";
      $gotmodelname = mysql_query($getmodelname) or die ("Error (get model name): $getmodelname. " .mysql_error());
            if (mysql_num_rows($gotmodelname) > 0) {
                  while($rowmn = mysql_fetch_assoc($gotmodelname)){
                        $modelnames = $rowmn['name'];
                        $products .= " ".$modelnames;
                  }
            }
      }
}

I don't want to that, any suggestions?
0
Sythe-oneAuthor Commented:
products related to MAKE and NOT MODEL... oops
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify: you want the products associated to the same make as a selected product, but not to some selected models?
0
Sythe-oneAuthor Commented:
Sorry angellll

I am getting the products related to a makeid only now:
SELECT p.id AS pid FROM products p WHERE EXISTS (SELECT NULL FROM product_make ma WHERE ma.mid = '$curMakeId' AND ma.pid = p.id)

Then I get the actual products:
SELECT * FROM products WHERE id = '$allpids'

But now one product can have many makes / models associated to it. I want to grab the Model that is associated to that Product but within the current Make (selected earlier by '$curMakeId')

I wonder if this is clear?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I wonder if this is clear?
sorry, no. sample data will clarify, eventually.
0
Sythe-oneAuthor Commented:
LOL, ok

here are the tables again (with the MAKE and MODEL tables added):
TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
);

TABLE `product_make` (
  `pid` int(11) NOT NULL,
  `makeid` int(11) NOT NULL
);

TABLE `product_model` (
  `pid` int(11) NOT NULL,
  `modelid` int(11) NOT NULL
);

TABLE `make` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL
);

TABLE `model` (
  `id` int(11) NOT NULL auto_increment,
  `makeid` int(11) NOT NULL,
  `name` varchar(200)
);

Now when I add a product, I can link the Product to a Model AND a make. All Models are linked to a Make.

I find this terribly hard to explain... Let me try.

2 products:
Cola
Lemonade
2 makes:
Plastic
Glass
The Plastic Make has 2 models:
330ml
440ml
The Glass Make has 3 models:
550ml
660ml
770ml

Now let's assume that the product Cola has the following makes and models
Plastic - 330ml
Plastic - 440ml
Glass - 550ml
Lemonade:
Plastic - 330ml
Glass - 660ml

I the product id's by selecting the Plastic MAKE from the drop down...
SELECT p.id AS pid FROM products p WHERE EXISTS (SELECT NULL FROM product_make ma WHERE ma.mid = '$curMakeId' AND ma.pid = p.id)

Then I grab the products based on the product id.
I'll get Cola and Lemonade

Then I want to display after a brief description that the Product is suitable for their related Models, which would be:
330ml and 440ml for Cola
and
330ml for Lemonade

I am getting it right but with nested queries, like so:
$getModels = "SELECT * FROM product_model WHERE pid = '$prodid' ";
$gotModels = mysql_query($getModels) or die ("Error (get model ids): $getModels. " .mysql_error());
if (mysql_num_rows($gotModels) > 0) {
    while($rowmo = mysql_fetch_assoc($gotModels)){
      moid = $rowmo['moid'];
                                         
      $getmodelname = "SELECT * FROM model WHERE id = '$moid' AND makeid = '$curMakeId'";
      $gotmodelname = mysql_query($getmodelname) or die ("Error (get model name): $getmodelname. " .mysql_error());
            if (mysql_num_rows($gotmodelname) > 0) {
                  while($rowmn = mysql_fetch_assoc($gotmodelname)){
                        $modelnames = $rowmn['name'];
                        $products .= " ".$modelnames;
                  }
            }
      }
}

Which I think can coded better, without nesting queries?

Does that make better sense?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is what clarifies all:
TABLE `model` (
  `id` int(11) NOT NULL auto_increment,
  `makeid` int(11) NOT NULL,
  `name` varchar(200)
);

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, why do you need the table `product_make`?
the table product_model refers to the model table, which has the make_id referred?
0
Sythe-oneAuthor Commented:
I built these prior to actually coding it is abit of a roundabout I must admit. I guess I don't have to reference the product_make table. But the client needs to be able to select a make and it was easier to simply add the makeid value to a composite product_make table and a model to the product_model table seperately.

That still doesn't make my nested queries go away though, if they can??
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT p.id, p.name, p.description
  , GROUP_CONCAT( m.name , ' / ') models
FROM products p
JOIN `product_model` pm
  ON pm.pid = p.id
JOIN `model` m
  ON m.id = pm.modelid
 AND m.makeid = '$curMakeId' 
GROUP BY p.id

Open in new window

0
Sythe-oneAuthor Commented:
How do you do that? It works great!

And how can I give you more points or something for your troubles and bearing with me?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>How do you do that? It works great!
experience :)

>And how can I give you more points or something for your troubles and bearing with me?
no need here.
however, next time(s), you should "ask a related" question, that would also make it for others to contribute as they could see a truly new question.

CHeers
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.