Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using:
***********
SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC
***********
I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
View Replies !
Selecting From One Table Based On Another?
I have a table for products and another table for the type of product it is like this: I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" ?
View Replies !
Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table? for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0. i don't want merged results. i just need to list all therapist based on the where criteria from a different table. these two tables have the therapist_id in common.
View Replies !
Help CHECKING FORM DATA AGAINST MYSQL TABLE DATA
PHP // See if relevant fields have been set. $submitted = IsSet($_REQUEST['Submit']) ? TRUE : FALSE; $user = IsSet($_REQUEST['username']) ? cleanValue($_REQUEST['username']) : '' $pswd = IsSet($_REQUEST['password']) ? cleanValue($_REQUEST['password']) : '' if($submitted) { // Form submitted yet? if (empty($user) || empty($pswd)) { // Are Form fields empty? // Display error message when fields are not filled in. $message = 'Make sure you enter both a username and password.' } else { /* THIS IS MY HUMBLE EFFORT BELOW AND IT DONT WORK HELP */ //CONVERT PASSWORD FROM FORM INTO MD5 $pswd = md5($pswd); //CONNECT TO DATABASE $connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server."); $db = mysql_select_db("$database_name", $connection) or die("Couldn't select database."); //SELCT TABLE AND DATA $sql = @mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pswd' AND activated=Ƈ' "); if (!$sql) { exit('<br /><br /><h3>Error retrieving Art members from database!<br />'. 'Error: ' . mysql_error() . '</h3><br /><hr /><br />'); } //MAKE VARIABLES FOR CHECKING FORM DATA AGAINST IS THIS RIGHT!! while ($login_check = mysql_fetch_array($sql)) { $user = htmlspecialchars($login_check['username']); $pswd = $login_check['password']; //NOT SURE FROM HERE // ORIGINAL SCRIPT BELOW // Authenticate username and password (i.e. match against a database). // Start a session only after username and password have been verified. THIS IS THE EXAMPLE LOGIN CHECK THAT CAME WITH THE CLASS if ((!strcmp($user, 'empl')) && // Does username match 'empl'? (!strcmp($pswd,'demo'))) { // Does password match 'demo'? $sess_param['new_sid'] = TRUE; // Create new session ID to be safe. $sess_param['security_level'] = 100; // Security level for Employees area $sess_param['gc_maxlifetime'] = 300; // Inactivity timeout of 300 seconds (5 minutes) $sess = new DB_eSession(&$sess_param); // session_start() done. $sess->setSessVar('authenticated', TRUE); // Set that authentication occurred. // Save the username for displaying and encrypt $sess->setSessVar('username', $user, ENCRYPT_VALUE, EXTRA_FIELD); $URI = $sess->setSessURI('employees.php'); // Optional - pass session ID in URI. Not as safe. header("Location: $URI"); // Go to members area. exit; } REGARDS Bill007
View Replies !
Data Insertion/Update Form Different Tables Into On Table
I have 3 tables say Employees, Benefits, Employee_Benefit_Mapping. These tables are something like this: Employee: ======== Employee_Id Name Dept_Id . . Benefits: ========= Benefit_Id Benefit Name . . Employee_Benefit_Mapping: ========================= Mapping_Id Employee_Id Benefit_Id Its a many to many relation. Hence the mapping table. Now the situation and question is: ==================================== Every employee "In a certain Dept" is eligible for some benefits by default. How can add these records with one (or minimum) insert statement(s) rather than going employee by employee? Insert into Employee_Benefit_Mapping ((Select Employee_Id from Employee where Dept_Id = xx), (Select Benefits_Id from Benefits where Benefit_Id in(xx, yy))) Will this work to insert all the data into the mapping table?
View Replies !
Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city. What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'. So is it possible to make those 2 query in one so I do not need to do a php loop: SELECT city, COUNT(city) FROM profile GROUP BY city; then UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image. The file with the new data is a .txt file, a sample of which looks like this: v_products_modelv_products_imageEOREOR 5361453614.jpgEOREOR 5361553615.jpgEOREOR 5372453724.jpgEOREOR The beginning part of the table looks like this: +----------------------------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------+---------------+------+-----+---------------------+----------------+ | products_id | int(11) | NO | PRI | NULL auto_increment | | products_type | int(11) | NO | | 1 | | | products_quantity | float | NO | | 0 | | | products_model | varchar(32) | YES | MUL | NULL | | | products_image | varchar(64) | YES | | NULL | | | products_price | decimal(15,4) | NO | | 0.0000 | | | products_virtual | tinyint(1) | NO | | 0 | | From what I've read over the past few days...I think I need to: (a) delete the EOREOR column from the text file; (b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.
View Replies !
Selecting Data From More Than One Table (not Join)
I have a database with a bunch of tables, including two tables that store messages. These two tables have different names, but are identical in structure. In my app, a user can run a report that selects messages from both of these tables. This is currently achieved by running two selects and printing out the details. However I would now like to sort and limit (i.e. show messages 1-25 and have a link to show the next 25 etc). I can do this in the code by dumping the combined results in an array and then sorting/limiting the output, but this will cause problems if the number of messages gets too big and the array size becomes too large for the memory in the server. Also it doesn't help any with optimization and won't speed up the query time as I won't be able to use the LIMIT clause in the SQL! So what I want to know is whether it is possible in SQL to select data from more than one table, given that the two (or more) tables to be joined will be identical in structure? If so, can I perform the normal ORDER BY and LIMIT functions?
View Replies !
Selecting Data From Single Table... Subqueries
I have a table with a list of books, their genres (type), and price. I have attached it as an image. I would like to select all books whose price is higher than all books of type HOR. Basically, I think that this means: find the book of type HOR that is the most expensive. Then, select all other books whose price is greater than that. In this example, the most expensive HOR book is 19.60. Therefore, I need to select all books with price > 19.60 and not of type HOR. I tried using a subquery like this: SELECT * FROM BOOK WHERE PRICE > (SELECT MAX(PRICE) FROM BOOK WHERE TYPE='HOR' GROUP BY TYPE) However, I get an error that says: Quote: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX( PRICE ) WHERE TYPE = 'HOR' GROUP BY TYPE ) LIMIT I looked up that error in the mysql doc but it didn't have much info in there: Quote: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated.
View Replies !
How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do: 1- I have Two tables: polls_created and votes 2- Table polls_created is like: poll_id owner poll_subject 3- Table votes has the votes issued for a given poll, like this: vote_id poll_id vote vote_date So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables. How do I then generate this result: MySQL Code: SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes "sorted by polls that have gotten most number of Votes" Of course "sorted by polls that have gotten most number of Votes" is not real MySQL
View Replies !
Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows: Customer table contains: 1) customer_email_address 2) customer_newsletter (value 0 or 1) Visitor table contains: 1) email The visitor table contains email addresses from customers that have signed up through another system. I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.
View Replies !
Sql Based Contact Form
how would I create a contact form that would store the results in a sql databse, be able to see the results in a text file, and also would have a link to delete that result from the database in the results text file?
View Replies !
Preventing Duplicate Rows On Form Entry
I have written an contact database for my company intranet. I want to prevent users from submitting new contacts into the database if the contact already exists. I have written an unsuccessful php script that tries to query the database prior to inserting the fields from the entry form. ("if first and last name columns match, do not insert"). This seems like a common database practice, but I can't find out how to do this. My code below:
View Replies !
Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
View Replies !
Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table This is a SQL that I am trying to use UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000, SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000, SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000 WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1; I got error "ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET SOLAR_SY' at line 1" I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?
View Replies !
Creating New Table Based On Old Table
I have a MySQL database with a user table will lots of DUPLICATE email. I want to delete the rows completely having the duplicate email. I found a way out is to use some thing like this: Code: insert into newtable (email) select distinct email from oldtable But this way, only the email column is populated in the new table. I want all the other columns as well.
View Replies !
A Table Or A Form Field
If I have a db where people enter in their basic info...one of those things being the country they live in....Is it better to make a seperate table in the databases with the country names OR...just put the country names as options in the html form dropdown list?
View Replies !
Create Table With Form
I have an html form that I want to create a new table when submitted. Is there a way to use input fields in the form ($username, $email) and create a table with it: "CREATE TABLE ('username', 'email')( various values )";
View Replies !
SQL To Get Lastest Record Based On Date Of Entry
Here's my table: Activity - Id (PK) - ProfileId (FK) - Date Each record represents a profile's activity in the CMS we're building (e.g. user updated profile, user uploaded photo, etc.) I'm only interested Id, ProfileId, and Date of the latest activity. How do I get that?
View Replies !
Creating An Order Form - New Table For Each One?
I'm trying to create a simple database using mySQL which will be used to create various web based order forms, these forms will be used multiple times. The first table is for the item and is stuctured: ------------- |Item | |-------------| |Description | |Part No. | |Article No. | |No. Required | ------------- The order options form is structured: ------------- |Order Options| |-------------| |System Name | |PPNo | |Item | |Item | |Item...etc | ------------- Each order options form can have any number of items within it.
View Replies !
Join Records Form Same Table
I have results from a mySQL database that I am exporting to a CSV. These records are sales form an eCommerce store (ZenCart). What happens is that attributes for each part of an order make a new row for each attribute. Like this: .....
View Replies !
Selecting A Table From JOINED TABLE?
I have these existing tables let say table a, table b and table c. (all connected, meaning they are joined using foreign key) note: that these table structure is not my work. (this is the work of another programmer) now i just want to select table a. I'm currently selecting table a only. but there is no result and no error.
View Replies !
Selecting One Table From Joined Table
I want to select a table where is currently joined (has foreign key). or do i need to join select both the tables first? before i can get the value of a field inside a table joined? i just want to select one table (where is currently has foreign key).
View Replies !
Unique Entry In A Table
I am having a problem with trying to work out how I could select the lowest unique number in a field called MONEY. I am trying to find the record with the least money, but no other record in that table can have the same amount of money. Here is the table: CREATE TABLE lubidEntries ( partnerid smallint(5) unsigned NOT NULL default '0', lubidCamp smallint(5) unsigned NOT NULL default '0', keyword varchar(40) NOT NULL default '', MONEY varchar(40) NOT NULL default '', status enum('current','old', 'lubid') default NULL, time datetime NOT NULL default '0000-00-00 00:00:00', numberid int(10) unsigned NOT NULL default '0', KEY lubidentries (partnerid,status,numberid) ) TYPE=MyISAM;
View Replies !
Getting A Random Entry From A Table
Assume I have a table with 100 entries I would like to run a query that will return a random entry from all those that match some condition One way would be to run the query, getting ALL the entries that match the condition and then pick one of the randomly Is there a way to tell MySQL to do it for me, so I won't have to pick the random entry myself? This will reduce the size of result set from n to 1.
View Replies !
Check Table For An Entry?
I'm currently working on some rudamentry administration, namely an IP ban list. I want to make it so that if the user trys to ban an IP that is already in the list, the entry will be removed instead. what would be the best way to go about this?
View Replies !
Deleting An Entry From A Table?
When the user logs out, it doesn't display an error message, but when I check the table Currentusers, they are still on it. $username = $_SESSION['username']; mysql_query("DELETE FROM Currentusers WHERE username=$username"); echo $username; echo " is now logged out."; I must be defining the WHERE username=$username"); part incorrectly because when I take out username=$username"); and replace it with username='Jane'); it removes Jane from the table. There isn't a problem with the $username variable because it displays the value correctly. What am I doing wrong?
View Replies !
Pulling Out The 2nd Entry From A Table
i have an application already installed. It has an table to handle attachments to articles. The structure is; attachment_id |question_id| file_path Is it possible to pull out the 2nd entry in a table for a particular question (using its question_id)? eg something like? Select * From attachments WHERE question_id = $question_id Limit ROW 2 Ive search the mysql online manual but im getting really lost!
View Replies !
New Table Vs. Table Entry
I have a question pertaining to efficiency of creating a new table vs. creating entries in an existing table. My situation is as follows: I am writing a program that works with user accounts and other information pertaining to websites. Currently, the way I have it designed is that there is a user table in which all the user data (e-mail, password, etc...) is written to in a format similar to: #User Name#---#e-mail#---#etc...# where ## is a separate field. I am, however, starting to doubt this design and am thinking that it might be faster to simply create a new table for each user and populate the table with the appropriate fields. I currently use the same design for the website information (which includes its name, messages others have posted to the site, and current people looking at the site) but have an inkling it would be faster, cleaner, and more efficient to use separate tables for each website opposed to one table with all the information about all the websites in it. Is it easier, in terms of system load and access, to have individual tables instead of accessing one table hundreds of time for each different site/user?
View Replies !
Delete Duplicate Entry From A Table
I have a question about delete command: I have to delete duplicate entry from a table and I think I have to use the following statement: delete from ip_siti_gbw where data_evento in ( select data_evento from ip_siti_gbw group by data_evento having count (data_evento ) > 1 ) When I execute I get an error about count function: FUNCTION statistiche.count does not exist Can I use count function into a subquery such this?
View Replies !
Duplicate Key Entry On InnoDB Table
I have a table that contains a primary key, designated as VARCHAR (1). I cannot enter the same letter of the alphabet, using lower and upper case, to create 2 entries. For example, 'A', and 'a', both cannot be specified as primary keys.
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
Selecting Last Entry
All I need some help i have been trying to write this program for a friend that owns a internet cafe. The DB he uses is in sql he wants people to be able to see their time on his website i almost have it all written except for one problem when it selects there time it brings up all the changes to their account as in if he bought a hour yesterday it would so it i want it to show the current or last entry in the columns heres the code SELECT userid, username, usertime, datetime FROM users a, log b where username = 'Their UserName' order by datetime; Is there any command so it shows only the last entry?
View Replies !
MySQL SELECT Table From $_POST Entry?
Is it possible to use the results of $_POST['selectTable'] from a dynamically populated drop down menu to specify the table in a MySQL SELECT query? I want to get the user of my webpage to select which database table to use from a menu before I make a connection to the database table and retrieve the information I need. I'm assuming this is best done over two pages or using an IF ELSE routine?
View Replies !
How To Select Default Value If The Given Entry Doesn't Exist In Table?
user | data ---------------------- root | data1 mike | data2 default | data3 ---------------------- I need to select some data from the data column for a user, which is very simple: > select data from table where user='mike' Problem: when a user doesn't not exist in the table, the select query has to return data for "default" user (also in the table) and I don't know how to do it. So, it should be something like this: > select data from table where <if john exists user='john'> <else user='default'> in the table above, user 'john' doesn't exist and this query is supposed to return 'data3' value. I've read that "if/then", "if exists" statements are used in procedures only,
View Replies !
Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.
View Replies !
Deleting From A Table Based On Record Id
I need to execute the following query: delete from history where id=(select min(id) from history where user = username); this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause" How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.
View Replies !
Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this: SELECT A.id AS Id, A.nome AS Nome FROM main_table A JOIN joined_table JT ON JT.id = A.id_cat ORDER BY ("first whoever have a row in" another_table AT) another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first... I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)
View Replies !
Mysqlimport: Error: Duplicate Entry '2' For Key 1, When Using Table: Analysis
I am installing some databases on the mysql databases of my mac osx 10.2.8 but when I do "mysqlimport -u X -p W (directory) *.txt.table" I am getting this answer: mysqlimport: Error: Duplicate entry '2' for key 1, when using table: analysis (analysis: one of the txt.table files). This problem happen just with one sery of files that I downloaded; any other files of the same style are having this problem. Is this clear enough to have a solution to my problem?
View Replies !
List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme: TABLE Prerequisites PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column) UpgradeID (FK to Upgrades table as well) TABLE BuildingUpgrades BuildingID (FK to Buildings table) UpgradeID (FK to Upgrades table; states what Upgrade Building already has) So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U. What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way. The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me: SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID) FROM BuildingUpgrades BUPG JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID GROUP BY BUPG.BuildingID, BUPG.UpgradeID HAVING COUNT(PRE.PrerequisiteID) = ( SELECT COUNT(PrerequisiteID) FROM Prerequisites WHERE UpgradeID = PRE.UpgradeID GROUP BY UpgradeID ) What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.
View Replies !
Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.). That seems simple enough. But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user? Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him. I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to. I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.
View Replies !
|
TRACKER
