Sign in to follow this  

[web] Querying SQL Table Based on Returned Value

This topic is 3314 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Is there an easy way to query a table in MySQL if it's dependent on a return value of the previous SQL statement without having to do two separate queries? For example, in my first query if I get the type of item as a music item then query the music table for that item id, that would be two separate queries. Not to mention if they choose 10 items, that's actually 11 separate queries (one query for all the item types) then 10 queries into the individual tables for the individual items unless someone can think of a better way to do this. Basically each item of music is in the music table, each book item is in the book table as the information about each item is different. Thanks.

Share this post


Link to post
Share on other sites
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:


SELECT Items.ItemName, Items.Price
FROM Items
INNER JOIN ItemTypes
ON ItemTypes.ItemTypeID = Items.ItemTypeID
WHERE ItemTypes.ItemType = 'Music'


or in the case of multiple item types this:


SELECT Items.ItemName, Items.Price
FROM Items
INNER JOIN ItemTypes
ON ItemTypes.ItemTypeID = Items.ItemTypeID
WHERE ItemTypes.ItemType IN ('Music', 'Book', 'Video')


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:

SELECT ItemTypes.ItemTypeID, MusicItems.ItemID, MusicItems.Title AS ItemName, MusicItems.Price
FROM MusicItems
FULL JOIN ItemTypes
WHERE ItemTypes.ItemType = 'Music'
UNION ALL
SELECT BookItems.ItemID, BookItems.Title, BookItems.Price
FROM BookItems
FULL JOIN ItemTypes
WHERE ItemTypes.ItemType = 'Book'
UNION ALL
SELECT LawnChairItems.ItemID, LawnChairItems.ModelName, LawnChairItems.Price
FROM LawnChairItems
FULL JOIN ItemTypes
WHERE ItemTypes.ItemType = 'LawnChair'
.
.
.


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

SELECT ItemsView.ItemName, ItemsView.Price
FROM ItemsView
INNER JOIN ItemTypes
ON ItemTypes.ItemTypeID = ItemsView.ItemTypeID
WHERE ItemTypes.ItemType IN ('Music', 'Book', 'Video')



Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

This topic is 3314 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this