0

I have 2 tables,

Table1: 
id, int1, int2, int3
john,1,2,4
tim,2,3,4
pete,1,3,4

Table2:
integer,blob
1,wins
2,backtickle
3,text
4,whatever

The query I want to use is given the id I want to get the blob data from table2 associated with each of the integer columns in table1.

What is the specific query I can use here?

Sample result I am looking for would be something like:

search John returns "wins","backtickle","whatever"

search pete returns "wins","text","whatever"

I think it has something to do with foreign keys, but not sure...beginner level please! With 1 table it would be SELECT * FROM table1 WHERE id="........" but not sure with the setup i have now given above.

David19801
  • 11,214
  • 25
  • 84
  • 127

1 Answers1

1

The structure of your database does not look optimal. You're limiting yourself to 3 items per person, and you're using columns instead of rows in order to list them. What you actually have in your data is a many-to-many relationship between Table1 and Table2. What I'd recommend is using three tables:

Persons: 
name, personid
john,1
tim,2
pete,3

PersonBlobs:
personid, blobid
1,1
1,2
1,4
2,2
2,3
2,4
3,1
3,3
3,4

Blobs:
blobid,blob
1,wins
2,backtickle
3,text
4,whatever

PersonBlobs would give you the many-to-many link between Persons and Blobs.

And then the query becomes:

select Blobs.blob
from Blobs inner join PersonBlobs on Blobs.blobid = PersonBlobs.blobid
           inner join Persons on PersonBlobs.personid = Persons.personid
where Persons.name = 'John'
Ilya Kogan
  • 21,995
  • 15
  • 85
  • 141
  • I like the cut of your jib, but wouldn't this be much much slower for huge tables? say 100,000 people, with 100 things each? – David19801 Dec 25 '10 at 10:34
  • Especially if you have 100 things for each person, you must not use columns for them. There's a major difference between a row and a column: when you add more data to your database, you add more ROWS. Columns represents TYPES of data, not the data itself. Columns are something you decide on once you create the database, you very rarely add more columns to a table. Databases are optimized for stuff like what I suggested, you can be sure that a table with many millions of rows is not something that a database can't cope with. – Ilya Kogan Dec 25 '10 at 10:39
  • But then why would more than 2 columns exist at all? There must be some use for them...right? – David19801 Dec 25 '10 at 10:44
  • Of course, you can make as many columns as you like, but adding more columns changes the STRUCTURE of the table, not its DATA. For example, if a person had a first name, a last name, an address and a phone number, then your Persons table would have 5 columns (one for the ID and four for the other data). – Ilya Kogan Dec 25 '10 at 10:51
  • Also note that using "SELECT *" is not recommended because it makes the results of your query dependent on future changes in the table structure. It's always better to specifically list the columns you're selecting. – Ilya Kogan Dec 25 '10 at 10:58
  • 1 million persons, 500K categories, 125 million person/categories 0.02 seconds http://stackoverflow.com/questions/3534597/rewriting-mysql-select-to-reduce-time-and-writing-tmp-to-disk/3535735#3535735 – Jon Black Dec 25 '10 at 11:47