I have two tables in a DataSet in .NET. I want to join them on an ID column. Then I want a DataGridView to display two columns from the first table and one column from the second table. If it makes it easier, the relation between the two tables is one-to-one. Can it be done? ...
I have the following (shortened query): SELECT `Statistics`.`StatisticID`, COUNT(DISTINCT `Flags`.`FlagType`) AS `FlagCount` FROM `Statistics` LEFT JOIN `Flags` ON `Statistics`.`StatisticID` = `Flags`.`StatisticID` WHERE `FlagCount` = 0 GROUP BY `Statistics`.`StatisticID` ORDER BY `SubmittedTime` DESC LIMIT 0, 10 Now, neither...
I need to modify the following MySQL statement to include information from a third table... but I suck at joins. select ss.*, se.name as engine, ss.last_run_at + interval ss.refresh_frequency day as next_run_at, se.logo_name from searches ss join search_engines se on ss.engine_id = se.id where ss.user_id='.$user_id.' group by ss...
I have a function that exports a table to CSV and in the query I set which fields will export. Here is the query: SELECT lname, fname, email, address1, address2, city, state, zip, venue_id, dtelephone, etelephone, tshirt FROM volunteers_2009 The field venue_id is the the id of the venue which is referred to in another table (venues)...
I have a table of the form CREATE TABLE data { pk INT PRIMARY KEY AUTO_INCREMENT, dt BLOB }; It has about 160,000 rows and about 2GB of data in the blob column (avg. 14kb per blob). Another table has foreign keys into this table. Something like 3000 of the blobs are identical. So what I want is a query that will give me a re m...
I'm wondering if this is possible in SQL. Say you have two tables A and B, and you do a select on table A and join on table B: SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id); If table A has columns 'a_id', 'name', and 'some_id', and table B has 'b_id', 'name', and 'some_id', the query will return columns 'a_id', 'name',...
I have the following five tables: ISP Product Connection AddOn AddOn/Product (pivot table for many-to-many relationship). Each Product is linked to an ISP, each Connection is listed to a Product. Each product can have a number of add-ons, through the use of the pivot table (which simply has 2 fields, one for the product ID and one fo...
Hi, I'm trying to write a simple query involving two tables. The "person" table has a unique person_id and a name, and the "friends" table has a person_id and a friend_id which is a FK to a person_id in the person table. person: int person_id varchar[45] name friends: int person_id int friend_id I want to select the name of all...
Hi, I have two tables with the following columns: table1: id, agent_name, ticket_id, category, date_logged table2: id, agent_name, department, admin_status What I'm trying to achieve is to Select all rows from table1 where an agents department is equal to that of table2. I've tried a few different join statements but I'm either ...
I'm selecting 1 field from 1 table and storing it into a temp table. Sometimes that table ends up with 0 rows. I want to add that field onto another table that has 20+ fields Regular union won't work for me because of the field # mismatch. Outer wont work for me because there is nothing to compare. NVL doesn't work on the first temp t...
I am trying to select a record out of the table1 by joining table2 and using table2 columns in the WHERE statement. Col1 and Col2 in table1 can be stored in col1 of table2. I need to join col1 of table2 with either the value of col1 or col2 in table1 here is the sql statement I created so (pseudo): SELECT t1.Col1, t1.Col2, t...
Consider the following table: mysql> select * from phone_numbers; +-------------+------+-----------+ | number | type | person_id | +-------------+------+-----------+ | 17182225465 | home | 1 | | 19172225465 | cell | 1 | | 12129876543 | home | 2 | | 13049876543 | cell | 2 | | 15064223454 | home | ...
How do I inner join multiple columns from the same tables via Linq? For example: I already have this... join c in db.table2 on table2.ID equals table1.ID I need to add this... join d in db.table2 on table2.Country equals table1.Country ...
Hi, I've got MS Access database with linked tables, whever each table is linked to a table in the same SQL Server database. I have a query inside Access that joins 2 tables (in particular i'm updating a table based on another using a join). The question is does Access "download" all the table data before doing a join? Or is smart and j...
Which design do you think runs faster on PostgreSQL? A. Making a 15 column table of varchars and the like, but putting all TEXT columns in a separate table with an fkey link back to this table. And let's imagine you want to search for the record with ID of "4" but then pull all the rows back, including the stuff from the TEXT columns in...
I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works: select * from A where not exists (select * from B where A.pk=B.pk); however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B) Is there a better way to run this? Perhaps as a left join? ...
Ok, so we have a bunch of tables that are named like this: training_data_001 training_data_002 training_data_003 training_data_004 training_data_005 And then to find those we look at a field in another table, let's just call it master.training_type. Anyway, I was wondering if anyone knew of a way to do a weird table name based join w...
After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different. What is the difference and what should go in each? If I understand the theory correctly, the query optimizer should be able to use both interchangeably. ...
Coming from another question of mine where I learnt not to EVER use db queries within loops I consequently have to learn how to fetch all the data in a convenient way before I loop through it. Let's say I have two tables 'scales' and 'items'. Each item in items belongs to one scale in scales and is linked with a foreign key (scaleID). I...
hi, i'm having an issue with creating a query in oracle which doesnt seem to want to join on missing values the table i have is this: table myTable(refnum, contid, type) values are: 1, 10, 90000 2, 20, 90000 3, 30, 90000 4, 20, 10000 5, 30, 10000 6, 10, 20000 7, 20, 20000 8, 30, 20000 a break down of the fields i'm after is this: ...