Member Avatar for it2051229

I have three tables in the mysql database. Let's say:

Table: Human -> Fields: human_id (primary key), name
Table: Attribute -> Fields: attribute_id (primary key), name
Table: Human_Attribute -> Fields: human_id (foreign key to human table), attribute_id (foreign key to attribute table)

Cardinality: Human Table to Human_Attribute Table (1 to Many)
Cardinality: Attribute Table to Human_Attribute Table (1 to Many)

My Objective is to search a Human given the attributes. So i created an interface where the user selects the attributes of the human and then given those attributes, I have to find humans that has those exact selected attributes.

Can anyone help me with the SQL statement for this? I know that basic queries won't work for this.

  • 2 Contributors
  • 4 Replies
  • 169 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by it2051229
Member Avatar for Stylish 
SELECT h.name
FROM Human h, Attribute a, Human_Attribute ha
WHERE h.human_id = ha.human_id and a.attribute_id = ha.attribute_id and a.attribute_id in (list)
Member Avatar for it2051229

hmm.. I have multiple attributes... what you have shown is for a single attribute only..

Here's what I want to happen, let's say I have a human named john with an attribute of head = 1, arms = 2, and legs = 2. John is stored in the human table and the head, arms, and legs are stored on the attributes table (note: head, arms, and legs are not table fields but values of the field called "attribute_name").

Now on the interface, I provided three textfields where to put the value of head, arms, and legs. So let's just say that the user entered 1 for head, 2 for arms, and 2 for legs... Now the queried result of course is John Because john has those specific attribute.

Member Avatar for Stylish

I gave you the basics. Good luck.

Member Avatar for it2051229

I already know the basics... what I need cannot be done by a basic sql query... that's why I have to go here and ask for help if it is possible or any other way that an advanced query can do that such as sub queries and stuff. But the problem is that my knowledge in SQL statement is not as far as a professional database administrator can do. I tried going to oracle but seems like it's not for free.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.