Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DISTINCT, JOINS on composite tables

Posted on 2008-06-22
23
Medium Priority
?
525 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

876 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