Solved

DISTINCT, JOINS on composite tables

Posted on 2008-06-22
23
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
23 Comments
 
LVL 143

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 143

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 143

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 143

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
 

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 143

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 143

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 143

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 143

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 143

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 143

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article discusses four methods for overlaying images in a container on a web page
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

752 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