MySQL: select all records from a table with an id, see schema

Question

I've no clue on how to solve this in sql:

TABLE ADS:

ad_id
ad_title
ad_type_id

.... TABLE ADS_DATA:

ad_data_id
ad_id
ad_data_name
ad_data_value

Ok, lets try to make it easy: table ADS stores common data for an object of a type ad_type_id. Table ADS_DATA stores extra data for the objects depending on its ad_type_id.

ADS_DATA.ad_data_name stores the name of the data (in a normal table would be the column name), ADS_DATA.ad_data_value stores the value for this data. Depending on ad_type_id, ADS_DATA table is filled with specific ad_data_name values. e.g.:

ADS
ad_id = 1,ad_type_id = 2
ad_id = 2,ad_type_id = 3
ADS_DATA
ad_data_id = 1,ad_id = 1,ad_data_name = 'rooms',ad_data_value = 2
ad_data_id = 2,ad_id = 1,ad_data_name = 'size',ad_data_value = 20
ad_data_id = 3,ad_id = 2,ad_data_name = 'fingers',ad_data_value = 15
ad_data_id = 4,ad_id = 2,ad_data_name = 'toes',ad_data_value = 5

OK, how do i get all rows from ADS_DATA with and specific ad_id (every row despite on its name or value) and where (ad_data_name = x and ad_data_value = y) AND (ad_data_name = x1 and ad_data_value = y2) AND ...

I know it's a huuuugge mess, but thanks for reading!!

UPDATE: Here is an example: im trying to build an advertisements system, of different categories (ADS.ad_type_id). Depending on which type of advertisement you choose, the ads_data table is filled with specific data to this category, e.g: AD house category, will have in the ads_data table: rooms,size,baths,.... I can create an ads_data table with columns with the name of all possible data for each category,and fill only the ones i need, but i thought it was a waste,so decided to create a table with columns :

ad_data_id        UNIQUE ID
ad_id             REFERENCE TO ADS.ad_id
ad_data_name      for the "column name",e.g: house_rooms
ad_data_value     for the value.

INSTEAD OF A "NORMAL" RELATION:
ad_data_id        UNIQUE ID
ad_id             REFERENCE TO ADS.ad_id
house_rooms       ad data,only fill columns relative to choseen category(e.g:house_)
house_size
house_baths
car_engine
car_hp
car_year
...

I want to search through ADS table (filtering for example by advertisement price(that is a common data to all ads,so its in the ADS table,not the ADS_DATA), and then get only the rows that have,for example:(house_rooms BETWEEN X AND Y) AND (house_size BETWEEN A AND B). I need an 'AND' not an 'OR', betweeen the ADS_DATA columns. Do you think is better (and maybe the only way) to make a "NORMAL" table and only fill the columns that i need for each category?

Thanks

UPDATE: Although there are many googles out there that advice against the use of key/value pairs, i've found,despite of performance hits, a solution right here: Need a MySQL query for selecting from a table storing key value pairs

Please make it clear. It doesnt making any sense try to give an example with above table data. — simply-put, Oct 15 '12 at 16:30
Wait, are you asking one question or three? Are you trying to get all rows or rows with specific name value pairs? Also those tables aren't a huge mess. They are actually pretty clearly normalized. — invertedSpear, Oct 15 '12 at 16:31
@invertedSpear I completely agree with you. I think he is not able to convey his message clearly — simply-put, Oct 15 '12 at 16:35
Here is an example: im trying to build an advertisements system, of different categories (ADS.ad_type_id). Depending on which type of advertisement you choose, the ads_data table is filled with specific data to this categorie e.g: AD house category, will have in the ads_data table: rooms,size,baths,....I can create an ads_data table with columns with the name of all possible data for each category,and fill only the ones i need, but i thought it was a waste,so decided to create a table with columns ad_data_name for the "column name" and ad_data_value for the value. — iomismo, Oct 16 '12 at 15:38

D'Arcy Rittich · Answer 1 · 2012-10-15T16:45:25.390

0

THis assumes you won't have duplicate rows with the same ad_data_name and ad_data_value values for a given ad_id:

select a.*
from ADS_DATA a
inner join (
    select ad_id
    from ADS_DATA
    and (
        (ad_data_name = 'rooms' and ad_data_value = 2) 
        or (ad_data_name = 'size' and ad_data_value = 20)
    ) 
    group by ad_id
    having count(*) = 2
) am on a.ad_id = am.ad_id
where a.ad_id = 1

edited Oct 15 '12 at 16:45

answered Oct 15 '12 at 16:36

D'Arcy Rittich

167,292
40
290
283

score 0 · Answer 2 · answered Oct 15 '12 at 16:40

0

I think you sort-of answered your own question. A sub-select seems applicable here...

SELECT * FROM ADS_DATA WHERE ad_id IN (
    SELECT DISTINCT ad_id FROM ADS_DATA 
    WHERE 
        (ad_data_name = 'Foo' AND ad_data_value = 'Bar') 
    OR 
        (ad_data_name = 'Fizz' AND ad_data_value = 'Buzz')
)

It looks like your schema could do with normalization though. Storing key/value pairs in a relational database is kind of a waste.

answered Oct 15 '12 at 16:40

Zutty

5,357
26
31

Yeah,i've tried a bunch of posibilities but i need a strict AND between the rows of ADS_DATA e.g.: rooms =5 AND size = 45,and there is the problem. Anyway, do you think its better to create every possible column of each type of object in ads_data and only fill what i need,i mean like a normal relationial database? thanks – iomismo Oct 16 '12 at 13:07

MySQL: select all records from a table with an id, see schema

2 Answers2