Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
whoshotdk
Starting Member
2 Posts |
 Posted - 2010-07-09 : 13:04:09
|
| Hi thereIm creating a 'profile' based website; i.e people can get their own profile page and store their details on it. Profiles can be placed in a number of 'areas'.In addition, their profiles can be 'featured' on one or more area pages. However, they can be featured in two different ways - either as a plain old 'featured' profile, or as a 'touring' profile.So, I have four tables. A 'profiles' table which contains all the profiles. An 'areas' table which contains all the areas. Also two 'lookup' tables: 'featuredAreaLookup' and 'touringAreaLookup'.The lookup tables just have two columns in them: 'profileID' and 'areaID'.profileID references the 'id' PK in the 'profiles' table.areaID references the 'id' PK in the 'areas' table.If a profile's ID appears in one of those lookup tables; then it is 'featured'.Im using the following query to get all the 'featured' profiles (using area ID 2 as an example):SELECT * FROM profiles, featuredAreaLookup WHERE areaID = 2 AND profileID = id UNION SELECT * FROM profiles, touringAreaLookup WHERE areaID = 2 AND profileID = idThis works fine; I'm using PHP to loop through the results and output a list of all the 'featured' profiles.However, for each record I would like to determine if it came from the 'featuredAreaLookup' table or the 'touringAreaLookup' table.I assume I could either:A. Do two queries instead of the one UNION queryB. For each record looped, do another query (or two) to see which lookup table it appears in.However, I think these would be inefficient?I was thinking perhaps I could add to the above query a temporary' column that would basically say either 'featured' or 'touring' depending on the lookup table it appears in.I just dont have a clue how to do it!Any suggestions most welcome.Thanks,Dave |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-09 : 13:18:49
|
in each query you place a hard coded value so you can see from which select the data is coming.SELECT 'featured' as tabname,* FROM profiles, featuredAreaLookup WHERE areaID = 2 AND profileID = id UNION SELECT 'touring' as tabname,* FROM profiles, touringAreaLookup WHERE areaID = 2 AND profileID = id
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
whoshotdk
Starting Member
2 Posts |
Posted - 2010-07-09 : 13:50:49
|
| Ingenious! Many thanks!A quick note for posterity: the query didn't work straight away; just needed to re-jig the order of the SELECT arguments to place the '*' first:SELECT *, 'featured' AS tabname FROM profiles, featuredAreaLookup WHERE areaID = 2 AND profileID = id UNION SELECT *, 'touring' AS tabname FROM profiles, touringAreaLookup WHERE areaID = 2 AND profileID = id ORDER BY RAND() LIMIT 0, 2Thanks again,Dave |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-09 : 13:53:40
|
welcome  but think about it: this isn't a forum for MySQL so sometimes you can't get the right solution here...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
UNION Query; Determine which table record is from