Please help with this MySQL query

Hello,

I have the need to make a query to a MySQL database (2 tables in the database). To be easier to understand what I want the query to do, I'll explain the structure of the tables.

Table1 contains a list of properties that a product can have and they are in the following form:

IDprop | property
1 | property1
2 | property2
3 | property3
4 | property4
5 | property5

.... and so on

Table2 contains products and details for each product. One of the fields in this table (table2) contains the product properties, which are stored in the following form " 1 ; 2 ; 4 ; 5 ; 7 ;" (the numbers are the IDprop of each property contained by this product)

And now here's my question:
How can I select the product properties in such a way that only those properties that are contained by at least one of the products would be shown? I need to select only these because after this I would make it (property name) a link and the opening page would show all the products that have this property.

Thank you for any suggestions you might have.

 

Do an inner join.

Select Table1.property From Table1 Inner Join Table2 On Table1.propID = Table2.propID

 

druidelder,

I thought about a JOIN but I'm not sure how this would work, because the product can contain (and in most cases it does contain) more than one property, so propID in Table2 (products table) contains something like " 1 ; 2 ; 4 ; 5 ; 7 ;" (propID are separated by ";")

My ideia was to select all the "propID"s from Table2(products table) and put them all in one array, after this to use the function "array_unique()" and delete all the "propID"s that occur more than once in the array, and final point to select the propID names (those which IDs I have in the array) from Table1 (properties table)

What do you think about this?

 

Sorry, I see what you mean now. That might work. If you change the delimiter to a , then you could use the IN clause.

Select Distinct Table1.property From Table1 Where Inner Join Table2 On Table1.propID In Table2.propID

Your other option is to have a third table.

tblProduct
productID int (pk)
productName char(30)

tblProperty
propertyID int (pk)
propertyName char(30)

tblProductProperty
prodPropID int (pk)
productID int (fk)
propertyID int (fk)

 

Thank you, it's not a problem for me changing the delimiter if you think this would help.

Thanks. I'll try the solution you provided.

 

I have changed the delimiters to , but this still doesn't work

 

I wrote that badly.

It would be something more like

Select Distinct Table1.property From Table1 Inner Join Table2 On Table1.propID In(Table2.propID)

Or possibly

Select Distinct Table1.property From Table1 Where Table1.propID In(select Table2.propID from Table2)

 

HI, I found this working, but they only give the first value from Table2.propID

If the field Table2.propID contains "2,4,5,6,8" it will only return 2

 

Right, that gets you a list of properties that have a matching product. That is what you wanted on the first page. The next page where you click through to see the list of products will need a different query.

Are you running the queries from the page or are you using stored procedures?

The SQL will differ depending on the answer.

 

Also, if you used the where in() method you may want to add some data to the table to make sure that 1 is not considered to be in 10.

 

If the field Table2.propID contains "2,4,5,6,8" it will only return 2 but I need it to return all the values "2,4,5,6,8" and any other that other products might contain

 

I thought about this that's why my properties IDs start at 10 and finish 84 (there are 75 properties in total)

 

Yes, that gets me a list of properties that have a matching product. But I need a list of ALL properties that have a matching product. The query above only returns a few of them

I know how to make the script for other pages - thanks for your concern - but I still need to find a solution for this.

 

Hi,
If I got you right, this might work...

Select all properties from table1.

Start a loop
//Get the "IDprop" of each row individually, stored in "$id" for example, then:

$sql=mysql_query("SELECT * FROM `table2` WHERE column_for_idprop LIKE '%$id :%' LIMIT 1");
$num= mysql_numrows($sql);
if($num>0)
{
//make a link here.
}

// end the loop.

If you meant something else, sorry...
Bye

 

My problem was that I tested those in tables where the matching field did not have a list, just single number. So I made some tables to match yours and came up with this:

SELECT DISTINCT aaProperty.propertyName
FROM aaProperty
INNER JOIN aaProduct ON aaProduct.properties LIKE '%' + CAST(aaProperty.propertyID AS varchar) + '%'

aaProperty -> the properties table
propertyName -> name of property from aaProperty
propertyID -> id field for aaProperty
aaProduct -> the products table
properties -> comma delimited list of properties a product has

 

You're saying you have tested this and it worked? It doesn't work for me so I'll guess I'll have to go for the solution with an additional table

 

Yes, it worked for me. What version of MySQL are you using? They have made updates to JOINs in almost every release. What works in newer releases doesn't always work in newer versions, and what works in older versions doesn't always work the same in newer versions. I thought what I wrote would work back to 4.0, but maybe not.

Also, in the tables I created the propertyID is an int. If you are using a varchar, there would be no need for the CAST().

 

I'm using MySQL5 but have decided that I will use an additional table.
Thanks a lot for your help.