[web] Querying SQL Table Based on Returned Value

You should think about refactoring your table design by moving common fields to a single product table. The specific details about a product type (music, books, lawn chairs, etc) should remain in the separate tables. Then your query could look something like this:


or in the case of multiple item types this:


If you absolutely cannot change your table design, you could create a view that appends the common fields from all the product tables like so:

Then you query the view for the item(s) needed like so:

So I already pull out the common information. I somewhat understand what your saying but I'm not seeing where you pull the item specific information out (unless I'm missing something). So I'd have one table that's basically an item table that has the following:

Table Item:

id, name, type, price, specificid

Then I have the specific item tables:

Music Item:

specificid, number of songs, running length

Book Item:

specificid, number of pages,

Granted this is a bit more simplified than what I really have but it's pretty close equivalent for an understanding.

What I don't see is where in the inner joins it pulls out the "music item" or "book item" specific information. Wouldn't that still require two separate queries unless I did an inner join on every single unique item table? Even then, if I have the same specificid in each of those tables then I will be returning information for an item I don't want. I could put a type in each of those tables but it's redundant (why put a music type in the music table, it's redundant). In addition, if I had the same specificid in each of those item specific tables and I return items that have the same specificid then I couldn't separate between those with the inner join which would require a seperate SQL statement on each of those individual tables unless I'm missing something. I don't want to have the example where I have all the UNION's between the SQL statements to create a specific view as that view then basically makes a flat file to search through. Am I missing something or could you clarify a bit more. Thanks for the help.

If your sub-tables (music, book) have different fields, it would probably be better to query each separately based on type.

Quote:

Original post by pinkyandthebrain I'm not seeing where you pull the item specific information out (unless I'm missing something).

You don't see it because you probably wouldn't want to pull the specific information in a single query. My advice above assumed you didn't have the common fields in a single table. That was the purpose of the UNION statement above. Since you have all the common fields in a single table, ignore all the queries I suggested above. They are not applicable to your table design.

Quote:

Table Item: id, name, type, price, specificid Then I have the specific item tables: Music Item: specificid, number of songs, running length Book Item: specificid, number of pages,

This table design looks fine with the exception of the id field the items table. If the specificid field in the item table match with either the one in the music table or the book table, why do you need the field id in the item table?

Ultimately, your query depends on what you are trying to do. If you need to return information about two or more different items(this is what it sounds like you're trying to do) and the fields for each type of item differs(which is what you indicated above in the table design), you should perform a separate query for each item type.

Thanks for the input. I do want to return specific information in what I'm doing so it doesn't appear that I'll get around two separate queries. I just wanted to make sure there wasn't something that I'm not thinking about.