Click here to Skip to main content
15,074,394 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables products2 and model.
SQL
products2
prodID | prod

model
modID | prodID | prodName

I want to link both tables and display the models for each product. Example:
SQL
product 	Model
Prod1		mod1, mod2, mod4
Prod2		mod3

Currently my code repeats the model name for every tab and only shows product available in model table.

Please help me with this. Much thanks

What I have tried:

PHP
<?php
				if($stmt = $connection->query("SELECT * FROM products2 INNER JOIN model ON products2.prodID=model.prodID")){		
			?>        
				<table>
				<thead>
				  <tr class="monthTable">
					<th >product</th>
					<th >models</th>
				  </tr>
				</thead>
				<tbody >
				  <tr>
				  <?php
				  while ($row = $stmt->fetch_assoc()) {
					$prod=$row['prod'];
					$prodName=$row['prodName'];
				 ?>						
					<th class="productTable"><?php echo $prod; ?></th>
					<td><?php echo $prodName; ?></td>
					
				  </tr>
				<?php
					}
					}
				else
				{
				echo $connection->error;
				}
				?>
				</tbody>
				</table>
Posted
Updated 23-Mar-21 22:48pm
Comments
Gerry Schmitz 17-Mar-21 17:58pm
   
You pivot the data (sql), not the presentation.
seal123 17-Mar-21 20:15pm
   
Thank you for the information. May I ask what is the right SQL query to display as such. I'm still new to this. Your help would be much appreciated.
Andre Oosthuizen 18-Mar-21 10:35am
   
Use the DISTINCT operator
seal123 18-Mar-21 21:35pm
   
I tried using DISTINCT but it doesnt show as what I wanted. Any help of whats the right query pls. much thanks

1 solution

As has already been hinted, you can use PIVOT in the SQL to get the data the way you want it - an example of how to do that can be found at Simple Way To Use Pivot In SQL Query[^]

Here is a worked example for your scenario - all in SQL. Firstly some sample data
SQL
declare @products2 table (prodID int identity (1,1), prod varchar(50));
declare @model table (modID int identity(1,1), prodID int, prodName varchar(50));
insert into @products2 (prod) values ('Product1'),('Product2'),('Product3');
insert into @model (prodID, prodName) values (1,'P1Mod1'),(1,'P1Mod2'),(2,'P2Mod1'),(3,'P3Mod1'),(3,'P3Mod2'),(3,'P3Mod3');
(HINT: always provide sample data and the expected results from that data to get the best chance of an answer to your question)

The first thing we need is some way of identifying which "column" each of the models needs to be in. Not all products have the same amount of models. I'm going to use the ROW_NUMBER() function just to give them an arbitrary name. I'm also going to tidy up the columns that I'm retrieving from the tables to avoid duplication e.g.
SQL
SELECT a.prodID, a.prod, b.modID, b.prodName, ROW_NUMBER() OVER (PARTITION BY a.prodID ORDER BY a.prodID, b.modID) AS rn
FROM @products2 a 
INNER JOIN @model b ON a.prodID=b.prodID
(HINT: Instead of using SELECT * .. in your queries, always list the columns you require. It avoids duplication and protects your code from database schema changes in the future)

The results from that query are
prodID	prod	modID	prodName	rn
1		Product1	1	P1Mod1		1
1		Product1	2	P1Mod2		2
2		Product2	3	P2Mod1		1
3		Product3	4	P3Mod1		1
3		Product3	5	P3Mod2		2
3		Product3	6	P3Mod3		3
In my example the maximum number of models per product is 3, but that might vary. I don't want to get into the complexity of dynamic SQL here so I'm going to assume in the rest of my code that there could be up to 5 models per product. Just putting this out here now so you are aware of the possible pitfalls.

If you follow that article through you will notice that you need to have an aggregate function in your pivot. Unfortunately you can't use things like concatenate or sum on varchars (the model name) so I've used MAX() here..
SQL
select prod, [1],[2],[3],[4],[5]
from
(
	SELECT a.prodID, a.prod, b.modID, b.prodName, 
    ROW_NUMBER() OVER (PARTITION BY a.prodID ORDER BY a.prodID, b.modID) AS rn
	FROM @products2 a 
	INNER JOIN @model b ON a.prodID=b.prodID
) src
PIVOT
(
	max(prodName) for rn in ([1],[2],[3],[4],[5])
) pvt
That gives me the results
prod		1		2		3		4		5
Product1	P1Mod1	NULL	NULL	NULL	NULL
Product1	NULL	P1Mod2	NULL	NULL	NULL
Product2	P2Mod1	NULL	NULL	NULL	NULL
Product3	P3Mod1	NULL	NULL	NULL	NULL
Product3	NULL	P3Mod2	NULL	NULL	NULL
Product3	NULL	NULL	P3Mod3	NULL	NULL
Almost there, but not quite!
To get rid of all those NULL values, and to get to a single row per product, I'm going to use a GROUP BY statement in my final SQL e.g.
SQL
select prod, max([1]) as [1],max([2]) as [2],max([3]) as [3], max([4]) as [4], max([5]) as [5]
from
(
	SELECT a.prodID, a.prod, b.modID, b.prodName, 
    ROW_NUMBER() OVER (PARTITION BY a.prodID ORDER BY a.prodID, b.modID) AS rn
	FROM @products2 a 
	INNER JOIN @model b ON a.prodID=b.prodID
) src
PIVOT
(
	max(prodName) for rn in ([1],[2],[3],[4],[5])
) pvt
group by prod
Which gives the results
prod		1		2		3		4		5
Product1	P1Mod1	P1Mod2	NULL	NULL	NULL
Product2	P2Mod1	NULL	NULL	NULL	NULL
Product3	P3Mod1	P3Mod2	P3Mod3	NULL	NULL
You can tidy up the NULL values in this output in your presentation layer. If you want the results as a comma separated list then look at using the CONCAT function[^]
   
Comments
seal123 25-Mar-21 1:21am
   
Thank you very much for your time and detailed answer! Wish you the best

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900