I'm having problems understanding the MYSQL join syntax. I can't seem to manipulate it for my purpose. I have had to use work arounds for months which have resulted in extra queries just for retrieving one extra column. Scenario: I have two tables.. Table.Stories - containing stories and the user ID of which whom added it. id, story,...
Excuse the poor title, i can not think of the correct term. I have a database structure that represents objects, objects have types and properties. Only certain properties are available to certain types. i.e. Types - House, Car Properties - Colour, Speed, Address Objects of type car can have both colour and speed properties, b...
Ok.. So I'm trying to improve my SQL skills and have a question. Here is a screen shot of the schema. (http://img509.imageshack.us/img509/97/screenhunter02nov121946.gif) Alright so I'm selecting a bunch of Report Bundles and other rows from a table you can see. I've got these two tables joining together correctly and displaying what s...
I have this query which works perfectly: SELECT * FROM Customer WHERE SacCode IN ( SELECT SacCode FROM SacCode WHERE ResellerCorporateID = 392 ORDER BY SacCode ) AND CustomerID IN ( SELECT CxID FROM CustAppointments WHERE AppRoomID IN ( SELECT AppRoomID FROM ClinicRooms WHERE ClinI...
I have a tree like structure of Categories, the leaf Nodes in the tree have Products and the products have Cods I need to select all the top level Categories (parent=null) that have leafs (Cods) that match some critaria... SELECT Category.Id AS Id0_, Category.Name AS Name0_, Category.COrder AS COrder0_, Category.Des...
I was thinking about the syntax of inner joins in Oracle's SQL implementation and here is something that seems a bit inconsistent: Let's say you have two relations loan(loan_number, branch_name, amount) and borrower(customer_name, loan_number). loan_number is the attribute common to both tables. Now, Oracle gives you two ways to express...
Hi folks, I am trying to export some data from two tables bridged by a third table that stores the one (file) to many (keywords) relationship. Tables are like so: files id, url, title keywords id, word bridge file, word What I want is to produce an export that has one row per file like this: files.id, files.url, files.title, keyw...
Hello All, I am having a table orders orders ( id int unsigned not null, fcr_date TIMESTAMP, completion_date TIMESTAMP, factory_no varchar(255), vendor_no varchar(255)) Please ignore the data type typos if any. I want to write a sql query that helps me fetch the data per vendor factory. The data to fetch includes th...
I have tables with listings, categories and one that maps them to each other. So a listing can then be placed in multiple categories. Something like the following: listings table id title etc categories table id category_name etc map table listing_id category_id When I need to get all of the informat...
I have a stored procedure with a number of parameters. I would like to write my query so that it joins with certain tables but only if a particular parameter has a value. Take the following example: I have a Person table. There is also an Address table which holds Person Addresses and a Groups table that holds Person Groups. Both are...
I have tried solving this problem by posting other related questions here that focused on parts of the query. However I might as well post the entire thing and see if anyone can help. I have the following tables with the following fields: tblPerson - PersonID, PersonName tblGroup - GroupID, Name tblGroupMembership - PersonID, GroupID ...
Continuing some themes in this question, I would like to know if I could get a performance of O(log n) on the size of the table from somes sqlite queries. The first would get the mth element of a table ordered by weight: select id, weight from items order by weight limit 1 offset m The second would do the opposite, get mth position o...
I've researched related questions on the site but failed to find a solution. What I have is a user activity table in MySQL. It lists all kind of events of a user within a photo community site. Depending on the event that took place, I need to query certain data from other users. I'll explain it in a more practical way by using two examp...
Hello I currently have 2 tables that are used for a select query with a simple join. The first table houses around 6-9 million rows, and this gets used as the join. The primary table is anywhere from 1mil to 300mil rows. However, I notice when I join above 10mil rows on the primary table the select query goes from instant to very slow (...
I have the following view which contains this data ActivityRecId RegionRecId IsExcluded 1 null 1 2 null 1 3 1 1 3 2 1 4 1 1 5 null 0 What I would like to do is join the region table to the view above to get the fo...
I've look around and I haven't find anything in the Web pointing me to the right direction, so I'll try the knowledgeable stackoverflow people :) I have 4 tables in Microsoft Access 2007 (Warehouse, Cars, TVs, Toys). __(many) Cars / Warehouse 1---(many) TVs \__(many) Toys The Warehouse table has a 1...
Hi all, I have 2 tables. Table 1 is 'articles' and Table 2 is 'article_categories'. When a user creates an article, it is stored into 'articles'. The user, while creating the article can select various categories under which this article can appear. Currently, an article can be selected to belong to anywhere from 10-25 categories(may be ...
I am trying to create a news page for a website I am working on. I decided that I want to use correct MySQL queries (meaning COUNT(id) and joins instead of more than one query or num_rows.) I'm using a PDO wrapper, that should function fine, and this still fails when run directly through the MySQL CLI application. Basically, I have 3 ta...
Hi, I have two tables: players and cards2 In each cards2 row, there is at least one player id (pid, pid2, pid3, pid4). I'm trying to come up with a select statement to get all fname's and lname's if there is more than one player id (pid's that are not 0). There is always a pid, but not always a pid2, pid3, etc. Does this make sense? He...
If I had the following query: select some cols from tbl_a INNER JOIN tbl_b ON tbl_a.orderNumber = tbl_b.orderNumber where tlb_b.status = 'XX' Assuming both tables have clustered indexes on order number only, would it be better from a performance perspective to extend the clustered index on table b to include the status column r...