Mammoth Amounts Of Self Joins
I have a table that contains keywords (Varchars) each one mapped to a
product. so the database schema is just an id column, product column and
keyword column.
I want to select the products that contain x,y,z keywords. Now if this query
involves many keywords I end up with a massive amount of self joins on the
keywords table, is there a better way to do this than self joins? What would
work perhaps is multiple unions where I could discard non duplicated rows
but you can't seem to do that. Any ideas... ?
View Complete Forum Thread with Replies
Related Forum Messages:
Adding Amounts Of Time
I am looking for a way, as the title suggests, to add a length of time to a time-type field.I needed to type something, and this is what I logically typed (of course, incorrect): SET playtime=playtime + '$length' Let's say playtime = 00:10:30, and $length = 00:04:30. What do I need to change the above statement to, so that the new value of playtime = 00:15:00 ?
View Replies !
Import Large Amounts
My client has a large database in MS Access. I need to put a section of this (approximately 900 rows) into MySQL to be displayed on a website. The thing is, every month I will be resupplied with updated data, which will need to replace the data that is currently in the SQL database. I am operating on a Mac and they've given it to me as an EXCEL spreadsheet, which I am trying to import into MYSQL using phpMyADmin with no luck. I've tried exporting from EXCEL to various formats, such as CSV, but I can't seem to get it to import into the MySQL database Does anyone know what I can do? I need it to be relatively simple so that every month I can simply reimport the new data. Is there some trick to importing the data from EXCEL, or should I ask the client to supply the data from MS Access in a different format.
View Replies !
Getting The Sum Of Amounts For Each User - How To Join?
I have a simple table for transactions that will store information like a bank account. To find the balance for a given user I want to sum all the amounts and display the result How can I do this for more than one user at a time? CREATE TABLE `transactions` ( `id` int(5) NOT NULL auto_increment, `user_id` int(10) NOT NULL default Ɔ', `created` datetime default NULL, `modified` datetime default NULL, `description` text character set latin1 collate latin1_general_cs NOT NULL, `amount` decimal(10,0) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I'm hoping to get output like: user_id | balance(sum of amounts) ------------------------------------------ 1 | 70 2 | -70 3 | 10 4 | 5
View Replies !
Handling Large Amounts Of Text
I am desiging a web store to hold and manage up to 150,000 to 200,000 items and have run into a problem which I believe can be fixed but I am not sure about the way to go about it.The problem is that every product has a rich text description that contains a lot of extra HTML code that goes into the database with the product description. I would estimate every product has an average of 10k of description and code. Now instead of putting all of this into the database would it be possible to write this to a .txt file and keep it in a products_desc folder? Would this keep the database at a respectible size and optmize everything?Does anyone have any good examples or tutorials on going about this with PHP?
View Replies !
Inserting Large Amounts Of Data Into Mysql
I have a whole bunch of numbers and state, county, city, etc. data that i'm trying to load into a database for a demographics website. The data is all in excel currently so I could easily copy and paste it to a text file or another medium if necessary but I'm wondering what the quickest and most efficient way to get it all entered into mysql is.
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier. Since I am new to starting mysql and modifying it, more information the better.
View Replies !
Many Joins
I've this table structure: t2(jb, B) | tj12(b, jb) | t1(A, b, c) | tj13(c, jc) | t3(jc, C) How can I do an elegant "SELECT A, B, C FROM t1" ? When I try JOIN tj12 USING(b) JOIN t2 USING(jb) JOIN tj13 USING(c) JOIN t3 USING(jc) MySQL join tj13 with t2. Must I use "JOIN ... ON ..." to force MySQL or thre's an other way?
View Replies !
Three Inner Joins
Is it possible to have 3 inner joins in one query? As i have tried to write a query but get the following error: SELECT Error: Unknown column 'I.COLUMNB' in 'on clause'
View Replies !
Using Joins
I'm having trouble figuring out which join i should be using to attain the results I am looking for. here's a bit of background information. I have 3 tables Table 1 - hp_cats - cat_id - cat_name Table 2 - hp_projects - project_id - various other fields that are irrelevant Table 3 - hp_proj_cat - pc_id - cat_id - project_id - active Basically, I have a table of projects, and a table of categories. each project has an entry in hp_proj_cat for each category. if the category is active for that project, active = 1 in hp_proj_cat. I'm looking for a way to retrieve all projects that have categories say '85, 200, 13, 6' and are active (ie, set to 1). I know this will involve a join, but am a tad bit confused. I greatly appreciate any help anyone can provide.
View Replies !
Self Joins
however, when I do this, I am only able to display the two parts that meet the ON statement, and not have the right side null when there is nothing that exists on a left join. that is how it should be. for example select this1.something, this2.something from sametable as this1 left join sametable as this2 on this1.column = this2.column where this1.form = 'red' AND this2.form = 'blue' I have used this, and it works. basically, I am joining the red columns with the blue columns and displaying the data on one area because the red columns are different rows and so are the blue columns. anyway, is there a way to make it so that if there is no blue column that exists, the red column will display its contents.
View Replies !
Sql / Joins
I have 4 tables: tblProducts (it has all the product names), tblInventory (it has all the products actually in inventory), tblMainIngredient (it has all the main ingredients ids associated with the products' ids), and tblProductIngredient (it has all the ingredients' ids). I am trying to retrieve all the products and their main ingredient but also those that don't have one. I need to be able to display the product name (in tblproducts) as well as the name of its main ingredient (tblproductingredient). I wrote the following SQL but it doesn't seem to work as it returns only the products that have an ingredient associated with them: SELECT * FROM (tblProduct INNER JOIN tblInventory ON tblProduct.ProductID = tblInventory.ProductID) INNER JOIN (tblMainIngredient INNER JOIN tblProductIngredient ON tblMainIngredient.IngID = tblProductIngredient.IngID) ON tblProduct.ProductID = tblProductIngredient.ProductID WHERE tblProduct.ProductID = " & productID ORDER BY Name, Price, Comments
View Replies !
Out Of Joins
I have this query: SELECT c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster, t.question, f.parent_forum_id FROM '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].') LEFT JOIN '.$db->prefix.'topics AS t ON f.last_post=t.last_post AND f.last_post_id=t.last_post_id WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND (f.parent_forum_id IS NULL OR f.parent_forum_id=0) ORDER BY c.disp_position, c.id, f.disp_position and i want to add the field displayname from table: '.$db->prefix.'users where username = f.last_poster I have used all of my joins i think (left, right, inner) and i need to add that in, can someone add this in.
View Replies !
Alternative To Self-joins?
I have a table that has values of variables for certain entities. The columns of interest are targetID, variableID, and valueID. A row (1, 5, 9) means that target number 1 has a value of 9 for variable 5. Being denormalized, target number one will have many possible rows in this table, one for each variable for which it has a value. My problem occurs when I want to find out what targets match a certain set of variable values. For instance, I want to find out what targets have a value of 9 for variable 5 and a value of 25 for variable 10. I'm thinking that this can be a simple self-join: SELECT mya.targetID from mytable as mya LEFT JOIN mytable as myb ON mya.targetID=myb.targetID WHERE (mya.variableID=5 AND mya.valueID=9) AND (myb.variableID=10 AND myb.valueID=25) Does this make sense so far? The problem is that this doesn't scale. When I have more than 31 variables and I need to evaluate them all, MySQL breaks: I can't do more than 31 joins. My design calls for perhaps 80-100 variables, so even 64-bit architecture with a limit of 64 joins won't get me there. I need another data structure that won't get me stuck on too many joins. Any suggestions? If I have to scrap this approach in favor of another,
View Replies !
Converting Joins
I have to run the following statement in MySQL. (The script is generated from oracle). ************* SELECT t1.c1, t2.c2, t3.c3 from t1, t2, t3 where t1.flag = 1 and (t1.cx = t2.cx(+) and t2.cy(+) = 1) and ( (t2.cz(+) = t3.cz and not exists (select * from t3) ) or (t1.c3 = 2) ) ; **************** This looks a bit complex for me as I am not familiar with JOINs
View Replies !
JOINS And/or INTERSECTS
I'm trying to create a query to find missing values in a table. Example table: <PRE> COL1 COL2 ---- ---- A 1 A 2 A 3 A 4 B 1 B 3 C 2 C 4 I want my output to be: COL1 COL2 ---- ---- B 2 B 4 C 1 C 3 </PRE> I thought I could do this with some outer joins but I can't seem to get even close.
View Replies !
Complex Joins
Here is the problem. Table_1 (gl_Train_KeyIdeas) +----------------------+----------------+-------------------+---------------+ | KeyIdea_ID | Unit_ID | Group_ID | Title | +----------------------+----------------+-------------------+---------------+ | 1 | 1 | 27 | yada 1 | | | | | | | 2 | 1 | 27 | yada 2 | | | | | | | 3 | 1 | 27 | yada 3 | | | | | | | 4 | 1 | 27 | yada 4 | | | | | | +----------------------+----------------+-------------------+----------------+ Table_2 (gl_Train_Progress) +----------------------+----------------+----------------------+ | ID | User_ID | KeyIdea_ID | +----------------------+----------------+----------------------+ | 12 | 5 | 3 | | | | | | 11 | 5 | 2 | | | | | | 10 | 5 | 1 | | | | | +----------------------+----------------+-----------------------+ The following sql returns field KeyIdea_ID = 4 which is the only KeyIdea not in both tables. SELECT gl_Train_KeyIdeas.KeyIdea_ID FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND ((gl_Train_KeyIdeas.Unit_ID)=1)); What I seem to be having trouble with is specifying the User_ID in table 2. I need to specify the current user for example: an sql with a User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.
View Replies !
2 Left Joins
I tried the query below and it seems to double count the results. Code: select a.prospect_id, count(b.referral_id), count(c.fepslead_id) from prospect a left join referral b on a.prospect_id=b.prospect_id left join fepslead c on a.prospect_id=c.prospect_id Is it possible to do left joins twice on the same table and that i am doing it wrong or is it impossible and that i may have to redesign the way my tables are linked.
View Replies !
Delete In Self Joins
delete from External where myid in(select myid from External where priority='1' and myid<>'0') and priority='2'; I get the following error: You can't specify target table 'External' for update in FROM clause
View Replies !
Joins Query
I have a page whereby I list all events and flyers for each event. Because each event can have more than one flyer it's obviously a many-to-many relationship, so I have introduced a new table called event_flyers which has the following fields; event_id, flyer_id. How *should* the query look? SELECT * FROM calendar, flyers LEFT JOIN calendar_flyers ON calendar_flyers.flyer_id = flyers.id WHERE calendar_flyers.calendar_id = calendar_id
View Replies !
Left Vs Inner Joins
I'm sure this has probably been asked before and, don't worry, I have searched the forums and found some answers. BUT does anyone know any links to exampls of the two joins? I've got explanations, sure. But I feel I'd better understand what left joins do (over inner ones) if I could see an example of the output you'd get on a simple DB.Unless anyone has the time or inclination to write one?
View Replies !
On Clause In JOINs With LIKE % %
I want to join two tables with the following condition: (table1.field1 = table2.field1) OR (table1.field1 LIKE table2.field2) For instance, if there is symptom in table2.field2 tom should join with that row ? Is this possible ? What I noticed was that LIKE works in exact matches withouth % % signs...But it does not work even in perfect matches when I say table1.field1 LIKE '%table2.field2%' Real join is as follows: SELECT * FROM a78764 INNER JOIN genetable ON (a78764.tagheap = genetable.genesymbol OR genetable.synonyms LIKE '%a78764.tagheap%')
View Replies !
Php Mysql Joins
I am trying to grab all topics from TOPIC which have a certain parent id (WHERE TOPIC.pID = 3 or whatever) but also match each returned row to the USER table to get the users name for the last post in that topic (match TOPIC.lastUID with USER.ID) problem is its only returning one row .Code: "SELECT TOPIC.ID, TOPIC.pID, TOPIC.lastUID, TOPIC.TITLE FROM TOPIC INNER JOIN USER ON TOPIC.lastUID = USER.ID WHERE CP.pID = '$FORUM_id'";
View Replies !
SQL Question Regarding Joins
What I'm trying to create is an index of all of my items, including options/variants for each item. I am having trouble figuring out the SQL to 1) Grab & display xyz_products.product 2) Grab & display the corresponding xyz_product_options_lng.option_name Here are the required tables & their fields: xyz_products: product, productid xyz_variants: variantid, productid xyz_variant_items: variantid, optionid xyz_product_options_lng: option_name, optionid As you can see, I need all four tables in order to match up OPTION_NAME to the corresponding PRODUCT.
View Replies !
Using Left Joins
ive searched for mysql tutorials and cant really seems what im after. basically i have two tables like below and i want to return all the player ids, firstname, lastname. from the player table that DONT appear in the top ten table so the ones that do appear in top ten i dont want returned ? so from below im trying to just get players 1 and 3 (bob and ken) to print out Player --------------- id | firstname | lastname --------------- 1 | bob | smith 2 | jim | bill 3 | ken | ten //------------- Top Ten ------------ id | playerid | position ------------ 1 | 2 | 1
View Replies !
Timeouts When Using JOINs
I'm working on a database that stores the results of answers to questions for testing / training purposes. I'm having a problem with the inner loop consistently timing out at 30 seconds. I've adjusted the timeout values in-script and in configuration but that doesn't seem to have had any effect at all despite restarting the server. Error logs indicate a pipe error which fits with the script not completing and hence the timeout. I've run the "OPTIMIZE <table>" command and added indexes in the transaction tables of the next table up to help optimise but this doesn't seem to help either. (ie. questionID for the QuestionAnswer table). Tables such as Question are using their primary key as an index (i.e. QuestionID for Question table) Code:
View Replies !
Joins To Same Table
I have a table that contains two codes, both of which are expanded by the same table. In an SQL SELECT I want to be able to expand both of these codes to their meanings. Is this possible in MySQL. I am sure I read something about Table Alias at some point, but can not find the reference now.
View Replies !
Avoid Self-joins
I have a table that has values of variables for certain entities. The columns of interest are targetID, variableID, and valueID. A row (1, 5, 9) means that target number 1 has a value of 9 for variable 5. Being denormalized, target number one will have many possible rows in this table, one for each variable for which it has a value. My problem occurs when I want to find out what targets match a certain set of variable values. For instance, I want to find out what targets have a value of 9 for variable 5 and a value of 25 for variable 10. I'm thinking that this can be a simple self-join: SELECT mya.targetID from mytable as mya LEFT JOIN mytable as myb ON mya.targetID=myb.targetID WHERE (mya.variableID=5 AND mya.valueID=9) AND (myb.variableID=10 AND myb.valueID=25) Does this make sense so far? The problem is that this doesn't scale. When I have more than 31 variables and I need to evaluate them all, MySQL breaks: I can't do more than 31 joins. My design calls for perhaps 80-100 variables, so even 64-bit architecture with a limit of 64 joins won't get me there. This is NOT an architecture or platform issue - I need a design and a data structure that will scale to lots of variables. I need another data structure that won't get me stuck on too many joins.
View Replies !
Multiple Joins
My database has three tables with the following fields: --tblIngredients-- IngredientID Ingredient IngredientInfo --tblRecipeIngredients-- RecipeIngredientID RecipeID IngredientID Quantity --tblRecipe-- RecipeID RecipeName Directions tblRecipeIngredients is a join table to link each recipe from tbRecipe with it's respective ingredient(s) from tblIngredients. Now, I'm trying to write a query that will return all recipe names (RecipeName) that don't have each of the Ingredients specified. In other words, I'm going to have a form that allows the user to select their on-hand ingredients and I want the query to eliminate all the recipes that include ingredients that the user does not have on hand and return the rest. I hope I'm making sense here... Anyhow, this is what I have so far, but it doesn't work the way I would like it to:
View Replies !
Multiple Self Joins
I am a newcomer to MySQL, from Access. My first project is porting a single-user, no-security Access DB to MySQL, largely due to Access size limitations, but I was also hoping for a sizeable improvement in speed. The data is a set of over 2,000 text files spanning around 130MB, with considerable growth expected over the next few years. The app uses VBA to read in all the text and index the location of every word. A sample from one file is shown at the end. What is important is the dot-and-two-letter descriptor preceding each line, and the number of the block in which it lives. (A block is the range from one '._HS nnn' line to the next '._HS nnn' line.) ......
View Replies !
SQL For Outer Joins
I can´t figure out how to create the following SQL select a.folio, a.tipo, a.date from solic a, order b where a.id = b.id_sdc and b.tipo!=399 and b.tipo!=398 so far so good besides, I want the rows from table a with no match in table b select a.folio, a.tipo, a.date from solic a, order b where (a.id = b.id_sdc and b.tipo!=399 and b.tipo!=398) or a.id not in (select id_sdc from b) this select does not work with the version I have. I tried using the left join but couldn't get the right rows.
View Replies !
Joins And Nulls
I have three tables containing information about companies. The first lists companies, each with an id. The second defines what information (attributes) can be held about each company and format it takes (eg address, postcode, owner, etc). The third table lists the specific values of each attribute for each company. Each attribute value for each company has its own line. Now, I am trying to select data from this group of tables. The problem is that not every company has complete information. Some, for example, don't have a postcode. But where they do, I'd like to display that. Which brings me to this SQL: Code:
View Replies !
Joins Confusion
From a table of purchase orders ("purchaseorders"), I would like to identify those orders which were NOT placed on hold. Whether or not a purchase order was placed on hold is kept in a separate "holds" table. The two are linked by a "POindex" field. The following SQL generates the results below (only order 10248 was put on hold): SELECT holds.huser, purchaseorders.poindex FROM purchaseorders LEFT JOIN holds ON (PurchaseOrders.POIndex = Holds.POIndex) where Purchaseorders.poindex > 10240 and Purchaseorders.poindex < 10250 order by poindex asc holds.huser purchaseorders.poindex [Null] 10241 [Null] 10242 [Null] 10243 [Null] 10244 [Null] 10245 [Null] 10247 184 10248 [Null] 10249 But what I really want is a listing of all orders not on hold - in other words: holds.huser purchaseorders.poindex [Null] 10241 [Null] 10242 [Null] 10243 [Null] 10244 [Null] 10245 [Null] 10247 [Null] 10249 I've tried the left join condition where purchaseorders.poindex != holds.poindex, but that does not work. I'm obviously missing something simple but can't figure it out.
View Replies !
Understanding JOINs
Table structures: - AUCTIONS: id | other fields... - AUCTIONS_BIDS: id | auction | amount | other fields... When viewing the AUCTIONS, users should be able to filter out records based on whether or not the current high bid for an auction is less than or equal to a value they supply. I want to select all information from the AUCTIONS table with this query, it doesn't need to return any data from the AUCTIONS_BIDS table. But it does need to be able to reference the AUCTIONS_BIDS table and eliminate auction records in the fashion I described. As far as I understand JOINs and similar concepts, I am under the impression that this can be achieved with a single query.
View Replies !
Joins And Limit Used Together
I have a query, which reads all information from tables. select se.section_name,pr.*,ph.code from prices pr left join pharmacy ph on pr.pharmacy_id=ph.id left join sections se on pr.section_id=se.id where medication_id=7 order by se.order_id asc, cast(`dosage` as SIGNED) asc, cast(`total_price` as SIGNED) asc result can be viewed here: http://www.foreign-drugstores-online...oduct.php?id=7 I need to read only top 5 (total price) for each `dosage` somekind of: group by `dosage` order by total_price desc limit 5 per group
View Replies !
Conditional Joins
This is similar to a previous thread of mine: http://www.sitepoint.com/forums/showthread.php?t=375169 I have two fields in my user table, 'lastentryid', and 'lastprivateentryid'. What I need to do is join the entry table using that entryid. Unfortunately, the results are skewed if their last entry is private, because lastentryid won't contain it. I am running into this problem in a few places, and I'd really like to solve it without using subqueries. I might even accept better organization on the db level. Here is my query as it is now SELECT user.userid, user.options, user.displaygroupid, user.usergroupid, user.username, ugroup.opentag as opentaga, ugroup.closetag as closetaga, dgroup.opentag as opentagb, dgroup.closetag as closetagb, entry.title, entry.dateline as entrydateline FROM vb_user as user LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid) LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid) LEFT JOIN vb_blog_entry as entry on (entry.entryid = user.lastentryid) WHERE entry.dateline > $cutoff ORDER BY user.username ASC Here is what I want... but this doesn't work. SELECT user.userid, user.options, user.displaygroupid, user.usergroupid, user.username, ugroup.opentag as opentaga, ugroup.closetag as closetaga, dgroup.opentag as opentagb, dgroup.closetag as closetagb, entry.title, entry.dateline as entrydateline, if ( user.lastentryid > user.lastprivateentryid, user.lastentryid, user.lastprivateentryid ) as last_entryid FROM vb_user as user LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid) LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid) LEFT JOIN vb_blog_entry as entry on (entry.entryid = last_entryid) WHERE entry.dateline > $cutoff ORDER BY user.username ASC
View Replies !
JOINs And UserNames...
i need a bit of help on the following problem. Table I Row_i. 1 2 _ Row_ii. 3 1 6 ... Row_j. 9 _ _ The numbers in each row identify users (User_A, User_B, User_C); not all rows have all three users filled in - in the example above, the first row has two users, the second three, and the last only one. Table II Row_i. 1 Fred Bloggs Row_ii. 2 John Smith Row_iii. 3 Pat Brown ... Row_j. 6 Tom Adams ... Row_k. 9 Vicky Brown ... Row_l. n Name Surname I can thus identify each user (if present) in Table I. So far, so good. Now for the problem. I need to generate a list of ALL the names used in Table I, either as User_A, User_B, or User_C, without repetitions, ordered by Surname. Given the example above, the output should be 6. Tom Adams 1. Fred Bloggs 3. Pat Brown 9. Vicky Brown 2. John Smith Simple, right?... But I guess the old brain is just not cooperating today, because I can't seem to get my query right. Any simple and elegant solutions out there? Many thaks in anticipation for your help.
View Replies !
Limiting Inner Joins
This has been bothering me and the relation isn't typical of the other expressions I've had to build. I have a table of 250 records that I'm returning ie. SELECT B.ID, B.Title, B.Owner FROM blogs AS B WHERE B.Status = 0 I need to return not only the blog name but the latest post they've made on their account. So what I did was used: SELECT B.ID, B.Title, B.Owner FROM blogs AS B INNER JOIN archives AS A ON B.ID = A.BlogID WHERE B.Status = 0 But when I do this it returns each record in archive with the relevent blog information. What I would like is for it to determine the latest ID insert on the blog. So if he has posts IDs such as 1010, 1050, 2063 and 2075 then it will return the blog information and the results of record 2075 in the archives table. I've tried a couple ways so far but each fails, I think what I want to do is somewhat limit the return on the inner join...sort of like add a WHERE clause unto the clause. I would have done this with a stored proc. or a view but I'm using MySQL 4 and I want to keep the querying function in my code for now. Not sure if that makes sense to the experts, if my problem isn't clear let me know I'll explain it further.
View Replies !
Case And Joins
Is it possible to use a case statement to determine what to join? I am trying to get information from different tables. All of the tables have a field that is referenced in different tables, but they are somewhat named the same. I am trying to use a case statement to determine what the join should look like. Here's a basic example Quote: SELECT A.PERMIT_NUMBER, B.DESCRIPTION FROM TABLE A LEFT OUTER JOIN CASE A.PN_TYPE WHEN 'B' THEN LEFT OUTER JOIN C ON A.ID = C.A_ID WHEN 'D' THEN LEFT OUTER JOIN D ON A.ID = D.A_ID END WHERE A.PN_ID=11111 When I do this, I get an error (incorrect syntax near the keyword case)
View Replies !
Twin Joins
Code: LEFT OUTER JOIN emails AS mails_alias ON mails_alias.from =people.email1 LEFT OUTER JOIN emails AS mails_alias2 ON mails_alias2.from =people.email2 People's structure Code: id name email1 email2 emails's structure Code: id subject body from
View Replies !
Joins On Two Columns In Db?
If i have a table RESULTS: GameID, team1ID, team2ID, team1score, team2score and another NEWTEAMIDS: teamID, newTeamID How would I go about creating a new table (NEWRESULTS) of the form: GameID, newTeam1ID, newTeam2ID, team1score, team2score I would normally atmysqlt something of the form: SELECT ... INTO ... INNER JOIN... but don't know how to change both team1ID and team2ID according to NEWTEAMIDS table.
View Replies !
Outer Joins?
In my application this SQL: SELECT Tags.postId, Tags.term as term FROM Tags LEFT OUTER JOIN Posts ON Tags.postId=Posts.id WHERE Tags.userId=30 AND Tags.term LIKE "label/%" Produces 97 rows... but this: SELECT * FROM (SELECT Tags.postId, Tags.term as term FROM Tags LEFT OUTER JOIN Posts ON Tags.postId=Posts.id WHERE Tags.userId=30 AND Tags.term LIKE "label/%") as allTags LEFT OUTER JOIN Tags as newTags ON (newTags.term=allTags.term) Produces 1935 rows. I guess I don't understand the Left Outer Join well enough as I thought that it just appends the joining table to the table without adding new rows like some of the other JOIN types. So in the second example I've created a dynamic table aliased as allTags and I then wish to join the original Tags table to my dynamic table and then filter it. If someone can let me know how to achieve this without adding the extra rows that'd be great! BTW how does one add multiple OUTER JOINS that all are all based on the data from the first table? ie I don't want Table 3 to be using Table 2 data to make the join I just want it to be joining on Table 1 as well. Am confusing myself now....
View Replies !
|
TRACKER
