SELECT from another table multiple times

[RavenStar]

Hi all.

 

I'm working on a project in PHP that requires users to fill out a profile about themselves, though I've got a mind blank when it comes to making this query as short as possible.

 

Basically I have 2 tables (accounts & dropbox). The users details are stored in the accounts table and the answers to some of the questions are just a value that matches up with a row found in the dropbox table.

 

I need to return valueA of a cell in dropbox where valueB equals that of the value in accounts AND where valueC = '4'. (valueC defines the question we're looking up, so it will be different for each time we run through this query, it would be 4,5,6,7)

 

This needs to be done for several different columns in the accounts table, is this possible to do with just a single query?

I could go about doing this with several individual queries but that would be messy and it would haunt me.

 

It's perhaps a little hard to explain without visualising it, so here's a link.. http://3es.com/RavenStar/table_structure.html

 

Any help would be greatly appreciated,

thanks in advance.

[RavenStar]

..No one has any ideas or input on this problem?

 

As mentioned, I could just run individual queries but that's messy, I'm looking for a single query, if possible?

[The Little Guy]

what columns do you want to match together?

[RavenStar]

Basically I'm trying to combine all these queries into one, somehow.

SELECT * FROM dropbox WHERE type = '4' AND accounts.smoke = dropbox.value

SELECT * FROM dropbox WHERE type = '5' AND accounts.drink = dropbox.value

SELECT * FROM dropbox WHERE type = '6' AND accounts.drugs = dropbox.value

SELECT * FROM dropbox WHERE type = '7' AND accounts.country = dropbox.value

 

I tried to provide a list of my table in the link provided in my first post.

 

[The Little Guy]

Maybe you would like something like this:

 

mysql_query("(SELECT * FROM dropbox d left join accounts a ON (a.smoke = d.value) where type = 4)
UNION
(SELECT * FROM dropbox d left join accounts a ON (a.drink = d.value) where type = 5)
UNION
(SELECT * FROM dropbox d left join accounts a ON (a.drugs = d.value) where type = 6)
UNION
(SELECT * FROM dropbox d left join accounts a ON (a.country = d.value) where type = 7)");

 

I might be able to come up with a better way later...

[RavenStar]

That looks like something that might do what I need.

 

Thanks a lot for that, I'll give it a shot.

Much appreciated.