?
Solved

DISTINCT, JOINS on composite tables

Posted on 2008-06-22
23
Medium Priority
?
523 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to dynamically set the form action using jQuery.
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…
Suggested Courses

719 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