Ad:

MySQL Discussion Thread
Unsolved
Views: 578

Mar 30th, 2009

I Need MySql Statement for this

Expand Post »

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.

Similar Threads

How to select the Last row from a table..using Mysql in MySQL
MySQL LIKE statement in MySQL
send email on mysql db update in PHP
Problem Using a Function Parameter in a MySQL Statement in Python
Converting MYSQL statement to MSSQL in MS SQL

it2051229

Reputation Points: 7

Solved Threads: 1

Junior Poster in Training

Offline

82 posts
since May 2007

Permalink

Mar 30th, 2009

Re: I Need MySql Statement for this

MySQL Syntax (Toggle Plain Text)
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)
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)

Stylish

Reputation Points: 44

Solved Threads: 19

Junior Poster

Offline

148 posts
since May 2007

Permalink

Mar 30th, 2009

Re: I Need MySql Statement for this

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.

it2051229

Reputation Points: 7

Solved Threads: 1

Junior Poster in Training

Offline

82 posts
since May 2007

Permalink

Mar 31st, 2009

Re: I Need MySql Statement for this

I gave you the basics. Good luck.

Stylish

Reputation Points: 44

Solved Threads: 19

Junior Poster

Offline

148 posts
since May 2007

Permalink

Mar 31st, 2009

Re: I Need MySql Statement for this

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.

it2051229

Reputation Points: 7

Solved Threads: 1

Junior Poster in Training

Offline

82 posts
since May 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Verifying Record before submitting to db

Next Thread in MySQL Forum Timeline: insertion

DaniWeb Message

Cancel Changes

User Name
Password