join

How to prevent a full table scan when doing a simple JOIN?

I have two tables, TableA and TableB: CREATE TABLE `TableA` ( `shared_id` int(10) unsigned NOT NULL default '0', `foo` int(10) unsigned NOT NULL, PRIMARY KEY (`shared_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `TableB` ( `shared_id` int(10) unsigned NOT NULL auto_increment, `bar` int(10) unsigned NOT NULL, K...

Rails :include vs. :joins

This is more of a "why do things work this way" question rather than a "I don't know how to do this" question... So the gospel on pulling associated records that you know you're going to use is to use :include because you'll get a join and avoid a whole bunch of extra queries: Post.all(:include => :comments) However when you look at ...

Producing XML from a multi-table join in SQL Server

Given a database schema with a parent table and two or more child tables. For example: Is it possible to create a query, using the for xml statement, that outputs the following XML: <?xml version="1.0"?> <person> <name>Joe Bloggs</name> <age>25</age> <address> <streetAddress>123 Test Street</streetAddress> ...

mySQL inserting multiple records with a select

Hi, I have a "dictionary table" called car_status that has an 'id' column and a 'status' column. car_status id status 1 broken 2 fixed 3 working 4 fast 5 slow I have a table called cars with the columns: id, type, status_id cars id type status_id 1 jeep 1 2 ford 3 3 acura 4 ...

MySQL Join syntax for one to many relationship

I have a situation where I have one table of titles (t1) and another table with multiple links that reference these titles (t2) in a one to many relationship. What I want is the full list of titles returned with a flag that indicates if there is a specific link associated with it. Left Join and Group By: SELECT t1.id , t1.titl...

Advanced SQL SELECT

Hi, I have basic tables one and two. Let`s call them tbl1 and tbl2. tbl1 --------- 1 - apple | 2 - orange | 3 - banana | 4 - pineapple tbl2 --------- 1 - table | 2 - chair | 3 - sofa | Then there is tbl3 that has foreign keys that link it to both tables above. Table 3 form has two select fields: one that queries tbl1 and another tha...

Mysql join with where clause

I have two tables I want to join. I want all of the categories in the categories table and also all of the categories subscribed to by a user in the category_subscriptions table. essentially this is my query so far: SELECT * FROM catagories LEFT JOIN user_category_subscriptions ON user_category_subscriptions.category_id = catago...

MySQL not using index with JOIN, WHERE and ORDER

We have two tables resembling a simple tag-record structure as follows (in reality it's much more complex but this is the essance of the problem): tag (A.a) | recordId (A.b) 1 | 1 2 | 1 2 | 2 3 | 2 .... and recordId (B.b) | recordData (B.c) 1 | 123 2 | 666 3 | 124...

LEFT JOIN using OR

I was wondering how I would return the result of the left most condition in a OR clause used in a LEFT JOIN if both evaluate to be true. The solutions I've come upon thus far both involve using CASE statement in the SELECT, this does mean I'd abandon the OR clause. The other solution involved using a CASE statement in an ORDER BY. Is ...

How to join tables together on columns with different datatypes?

A Microsoft Access implementation is throwing a type mismatch error while trying to execute a macro that opens up some queries. Most of the tables are linked to a SQL Server and I need to join two of the tables together that have different datatypes. Table A: REFERENCE TEXT Table B: REFNO NUMBER I would ordinarily want to correct the...

how do you search Right/Left joins?

I am having a problem searching multiple tables ,i have 2 tables tblcourse -courseid -name -status tblenroll -courseid(holds courseid from tblcourse) -studentid lets say a student has 1990 as student num and he registered to 2 courses in tblenrol I want to get the name of the courses that 1990 has and the ones he aint subscribe...

SQL Server : Join

Hi All, First two column is from one set of query statements and the remaining from other set. I want to display the values in a single row. Can anybody help me A B C D NULL NULL 0 22 0 699 NULL NULL SELECT statement: SELECT P.A, P.B, T2.C, T2.D FROM Table1 AS P JOIN ( SELECT MAX(ID) ID, COU...

Which SQL do you write?

When joining two tables, what are the difference between the two blocks below and which is the better approach? Pattern A: SELECT ... FROM A INNER JOIN B ON A.PK = B.FK WHERE 1=1 AND A.Name = "Foo" AND B.Title = "Bar" Pattern B: SELECT ... FROM A INNER JOIN B ON A.PK = B.FK AND B.Title = "...

How to handle multiple one->many relationships?

I have a database with several tables, 5 of which are dedicated to specific publication types. Each of these 5 have a one->many relationship with a status table and a people table. All of these tables are tied together using a unique "pubid". I have a view which includes the pubid (for all 5 types), along with their associated keyword...

Store procedure select all fields from One table using join

Hello I am very frustrated from linq to sql when dealing with many to many relationship with the skip extension. It doesn't allow me to use joinned queries. Not sure it is the case for SQL server 2005 but I am currently using SQL Server 2000. Now I consider to write a store procedure to fetch a table that is matched by two tables e.g...

mySQL MATCH across multiple tables

Hi, I have a set of 4 tables that I want to search across, each has a full text index, and here is my query; SELECT categories.name AS category, categories.id AS category_id, host_types.name AS host_type, host_types.id AS host_type_id, hosts.name AS host, hosts.id AS host_id, products.name as name, products.id AS ...

SQL JOIN, GROUP BY on three tables to get totals

I've inherited the following DB design. Tables are: customers customerid customernumber invoices invoiceid amount invoicepayments invoicepaymentid invoiceid paymentid payments paymentid customerid amount My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any ...

MySQL - Find rows matching all rows from joined table

Hi, Table 1: Tracks Table 2: Wordlist Table 3: N:M Track has Words (trackwords) Find all tracks which have all the words. currently the query looks like: SELECT DISTINCT t.id FROM track as t Left Join trackwords as tw ON t.id=tw.trackid Left Join wordlist as wl on wl.id=tw.wordid WHERE wl.trackusecount>0 group by t.id HAVING SUM(...

How should I join these tables?

These are the tables: threads: id, date, title, text comments: id, thread_id, date, comment How would I do to list the last commented thread on top? This is currently how it looks: $threads = mysql_query("SELECT id, title FROM threads ORDER BY date ASC"); while ($thread = mysql_fetch_assoc($threads)) { echo $thread['title']; }...

Best approach to construct complex MySQL joins and groups?

I find that when trying to construct complex MySQL joins and groups between many tables I usually run into strife and have to spend a lot of 'trial and error' time to get the result I want. I was wondering how other people approach the problems. Do you isolate the smaller blocks of data at the end of the branches and get these working f...