select colum

[ddtpmyra]

Hi,

Is there a way where you can select columname that is equal to row value?
for instance i have tablename PET

--------------------------
 | CAT | DOG | BIRD |
--------------------------
| dog | dog | dog |
----------------------
| cat | cat | dog |
---------------------
| bird | dog | dog |
---------------------
 

now i want to select the column name that is equal to row let say
selct * from pet where
column = cat
row = cat


is there a way you can do that?

[tharden3]

Correct me if I'm wrong, but I believe it is:
SELECT *
FROM Pet
WHERE (CAT="cat");

[ddtpmyra]

I think I didn't explain very well, what I wanted to do a result that tells me the column name that has cat value.

[Atli]

You don't select column names based on the row data.
You select row data based on column names.

If you need to do the opposite, odds are your table design is somewhat off.

Could we see the CREATE TABLE command you used to create it?
We might be able to suggest a better method.

[ddtpmyra]

Actually, I have a website that the user assigned the approver names and everytime they check box and hit the save button it assigned 'Y' on its equivalent variable where the name of the persons is the column, below is how I created the table

DROP TABLE IF EXISTS `test`.`approval`;
CREATE TABLE `test`.`approval` (
`ID` int(10) unsigned NOT NULL auto_increment,
`JamesLeory` char(1) NOT NULL default '',
`MaricarEdwards` char(1) NOT NULL default '',
`LizaJones` char(1) NOT NULL default '',
PRIMARY KEY (`FileID`)
) TYPE=MyISAM;

Now I wanted to select the column names (person names) that has 'Y' value can I do that?

[tharden3]

The names should not be set up as the fields, but rather data in a column. 'Y' can then be assigned in a matching column. Not trying to make it difficult on you, but I think you need to rethink the structure here.

[Atli]

Yea, tharden is right. You should never use actual data (like a approver's name) as the name of a table or a field.

In your case, a many-to-many (N:M) relationship would probably be the best solution.

That is; you should have one table to store a list of approvers. A second to keep a list of whatever you need them to approve of. And a third to link the two together.

Consider this:

 Table: Approver
+------------+--------------+
| ApproverID | ApproverName |
+------------+--------------+
| 1          | John         |
| 2          | Jane         |
| 3          | Mike         |
+------------+--------------+
 Table: Items
+---------+-----------+
| ItemID  | ItemName  |
+---------+-----------+
| 1       | FirstItem |
| 2       | ItemTwo   |
+---------+-----------+
 Table: ItemApproval
+---------+------------+------------+
| ItemID  | ApproverID | Approved   |
+---------+------------+------------+
| 1       | 1          | Y          |
| 1       | 2          | N          |
| 2       | 1          | N          |
| 2       | 2          | Y          |
| 2       | 3          | Y          |
+---------+------------+------------+

This is a standard N:M relationship, with an added "Approved" value for each link in the "ItemApproval" table.

And to prevent each Approver from approving each item more than once, you would use the ItemID and the ApproverID in the link table as a joint Primary-Key (or a unique-key, if you prefer that).

[ddtpmyra]

Hi Atli,

These getting clearer now, but what if the user will select who will be the approver for the items, where that will be stored? should it be on the Table:Items and insert the approver ID?

thanks for your support,
DM

[Atli]

If each item should only be able to be approved by a single, preselected approver, then you wouldn't need the N:M relationship.

For that scenario, a one-to-many (1:N) relationship would do fine.
The ID of the selected approver could simply be added to the Item table, as well as the value indicating whether the item has been approved.

Like:

 Table: Approver
+------------+--------------+
| ApproverID | ApproverName |
+------------+--------------+
| 1          | John         |
| 2          | Jane         |
| 3          | Mike         |
+------------+--------------+
 Table: Item
+---------+-----------+---------------+------------+
| ItemID  | ItemName  | ApproverID_FK | Approved   |
+---------+-----------+---------------+------------+
| 1       | FirstItem | 1             | TRUE       |
| 2       | ItemTwo   | 2             | FALSE      |
+---------+-----------+---------------+------------+

[ddtpmyra]

Thank You Atli! This make more clearly how to contruct my tables