Solved

DISTINCT, JOINS on composite tables

Posted on 2008-06-22
23
512 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:Sythe-one
  • 12
  • 10
23 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840243
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
 

Expert Comment

by:ozgurorhan
ID: 21840320
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
 

Author Comment

by:Sythe-one
ID: 21841106
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21841118
>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
 

Author Comment

by:Sythe-one
ID: 21841287
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
 

Author Comment

by:Sythe-one
ID: 21841305
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21841323
>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
 

Author Comment

by:Sythe-one
ID: 21841729
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21841745
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
 

Author Comment

by:Sythe-one
ID: 21841892
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
 

Author Closing Comment

by:Sythe-one
ID: 31469513
Well done. Truly a genius and I see you are at the top of both leader boards!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Sythe-one
ID: 21846834
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
 

Author Comment

by:Sythe-one
ID: 21846933
products related to MAKE and NOT MODEL... oops
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21847269
can you clarify: you want the products associated to the same make as a selected product, but not to some selected models?
0
 

Author Comment

by:Sythe-one
ID: 21847936
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21847958
>I wonder if this is clear?
sorry, no. sample data will clarify, eventually.
0
 

Author Comment

by:Sythe-one
ID: 21848189
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21849966
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21849987
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
 

Author Comment

by:Sythe-one
ID: 21853201
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21853270
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
 

Author Comment

by:Sythe-one
ID: 21853306
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21853371
>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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now