Problem with query


#1

Hi all … i’m italian and i hope you excuse me for my bad english!
Ok … I have a table where for all the user i save all the data (name,surname,address etc…) (We call this system “Vertical distribution of data”)
I must search some data from this table, and now the problem …
id bigint(20) NOT NULL default ‘0’, identify a unique user
idrifnome bigint(20) NOT NULL default ‘0’, identify a input text form
valore varchar(255) NOT NULL default ‘’, this is the value of the input text form
PRIMARY KEY (id,idrifnome) ok …
P.S. : For all unique user i have multiple record …

I must search the name surname and nationality of users … how can i do?
For example:
Database :
id 1
idrifnome 2
valore Marco
id 1
idrifnome 3
valore Rossi
id1
idrifnome 4
valore Italy

I must search in the table all the person who have name Marco and nationality Italy

I hope u can understand what i have write. Thanks


#2

Why do you have your database set up that way? Is there a specific reason?

A better design (IMHO):
CREATE TABLE user (
ID INT(20)…
FNAME VARCHAR…
SURNAME VARCHAR…
NATIONALITY VARCHAR …
PRIMARY KEY (ID));

SELECT * FROM users WHERE SURNAME = “Marco” AND NATIONALITY = “Italy”;

For your database design you could try adding “AND ID = ID” to the where clause of the above SQL.