tailoring result display from multitable select

[AHinMaine]

I'm trying to get results back from an sql query in the easiest way to handle in my script.

table 'categories' and table 'restcat' (restaurant categories).

I'd like to construct a query where I can look up a specific restaurant id, yet somehow related it to the categories table so that instead of a regular select/where, I get back something like:

+------------+----------+----------+
| restaurant | category | category |
+------------+----------+----------+
| 136        | Chinese  | NULL     |
| 136        | French   | NULL     |
| 136        | Italian  | Italian  |
| 136        | Pizza    | Pizza    |
| 136        | Seafood  | NULL     |
+------------+----------+----------+

Showing in this fashion that restaurant 136 is a member of the Italian and Pizza categories.

(and yes, my categories table actually uses category numbers) --
Andy

http://www.nachoz.com

 

[AHinMaine]

anyone know this one? --
Andy

http://www.nachoz.com

 

[r937]

i don't understand your two types of category

you said restaurant 136 is a member of the Italian and Pizza categories

but the sample data you gave shows restaurant 136 is in the Chinese, NULL, French , NULL, Italian, Italian, Pizza, Pizza, Seafood, and NULL categories


rudy

http://rudy.ca/

 

[nigelcohen]

Firstly, you need to set up a table called "category" (or something similar). In the category table, you need to have a unique reference field called ID (or something like this).

In the table Restaurant, you would need to set up a field called CategoryID (or something similar). In each record in the Restaurant table, insert the relevant category id.

The results can then be displayed by a query like this:

select * from Restaurant r, Category c
where r.CategoryID = c.ID

 

[AHinMaine]

The categories table is nothing more than the list of possible categories. The restcat table is just the restaurant id and the category id. I didn't want to put a category column in the main restaurant table because one restaurant can be a member of multiple categories. --
Andy

http://www.nachoz.com