join

SQLite query optimization (subquery and join) help

I have a table for a Statistical project. Structure is like this: CREATE TABLE NewStatHistory ( StatHistoryID uniqueidentifier PRIMARY KEY NOT NULL, DateEntered dateTime NOT NULL, DateApplies dateTime NOT NULL, WhoEnteredID uniqueIdentifier NOT NULL, PostingID uniqueIdentifier NULL, EnteredValue decimal(19,5) NO...

mysql - subqueries and joins

Hey, I'm not quite sure if this is the right approach, this is my situation: I'm currently trying to select 15 galleries and then left join it with the user table through the id but I also want to select one random picture from each gallery however from what I know you can't limit the left join (picture) to only pick up one random pict...

Help with Join

I need help with a join I think. Can't figure it out. I have these tables: Posts `id` int(10) unsigned NOT NULL, `title` varchar(140) DEFAULT NULL, `text` text COLLATE utf8_unicode_ci Posts tags `post_id` int(10) unsigned NOT NULL, `tag_id` int(10) unsigned NOT NULL Tags `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varc...

How can I join multiple regex matches into one array element using Perl?

Following is the script for matching regex and storing value in array: sub b1 { # print $_; my @file = @_; my @value; my $find = qr/(\s+)([0-9]+)\s([A-Z])\s[0-1].[0-9]+\s->\s([A-Z])\s/; foreach my $file(@file){ push (@value, $file=~ /$find/) ; print "\n"; } ...

SQL - joining on multiple columns in the same row

Our school's ERP has a nasty database structure to it and since it is not normalized correctly, I have an issue with joining on the same table multiple times. The DegreeHistory table has these columns in one row entry for a person: |Major1|Major2|Major3|Minor1|Minor2|Minor3| -------|------|------|------|------|------| CMPT ...

MySQL: JOIN queries vs multiple queries

Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query) I'm asking because JOINing them would complicate A LOT the design of my application If they are faster, can anyone approximate very roughly by how much? If it's 1.5x I don't care, but if...

Combine Multiple child rows into one row MYSQL

Thanks in advance, I just can't seem to get it! I have two tables Ordered_Item ID | Item_Name 1 | Pizza 2 | Stromboli Ordered_Options Ordered_Item_ID | Option_Number | Value 1 43 Pepperoni 1 44 Extra Cheese 2 44 Extra Cheese What I am looking to o...

Left and right joining in a query

A friend asked me for help on building a query that would show how many pieces of each model were sold on each day of the month, showing zeros when no pieces were sold for a particular model on a particular day, even if no items of any model are sold on that day. I came up with the query below, but it isn't working as expected. I'm only ...

SQL query hangs on join

Hey folks, I've written an SQL query that produces a report of some stats for each Year-Week-Mine-Product. It works exactly as desired except for one thing - trn.wid-date isn't the correct date to be using. I should be using td.datetime-act-comp-dump. When I replace trn.wid-date with td.datetime-act-comp-dump, it doesn't give me any...

Hierarchical Data in MySQL

I've got a sort of tree like thing going on in my MySQL database. I have a database that has categories, and each category has a subcat. I'm keeping all the categories in one table, so the columns are like this: *categories table* id | name | parent_id 1 | Toys | 0 2 | Dolls | 1 3 | Bikes | 1 Each item in my database is assigned...

Keyword search, multiple tables, PHP and Mysql, Which Join to use?

Hi I have 3 tables event, location, event_location. Event can have multiple locations. Location table has lattitude, longitude, hasGeocode fields. so for keywords "hello world" a simple query to Event table becomes Select * from event where keywords like '%hello%' OR keywords like '%world%' but if if the user has entered their loc...

How do you left join in Linq if there is more than one field in the join?

I asked a question earlier about why left joins in Linq can't use defined relationships; to date I haven't got a satisfactory response. Now, on a parallel track, I've accepted that I need to use the join keyword as if there were no relationship defined between my objects, and I'm trying to work out how to express my query in Linq. Trou...

SQL - Difference between these Joins?

I should probably know this by now, but what, if any is the difference between the two statements below? The nested join: SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID ON t2.table2_ID = t1.table1_ID The more traditional join: SELECT t1.* FROM table1 t1...

Union with Count OR Join with Sum - MySQL

Hi, I want to combine three tables - date, lead and click - in a query. The tables looks like this: date: |date| lead: id|time|commission click: id|time|commission The table date is just storing dates and is used when getting dates with no click or lead. So if we have the following data in the tables: date: 2009-06-01 200...

Which of two ways of coding an Inner join is faster?

I much prefer to code in t-sql using what is in effect an in-line join, rather than have a long list of the joins at the end of the stored procedure or view. For example, I code: SELECT PKey , Billable, (SELECT LastName FROM Contact.dbo.Contacts WHERE (Pkey = Contacts_PKey)), (SELECT Description FROM Common.dbo.LMa...

oracle: efficient way to configure columns in an output report

I'm designing an HTML report that effectively extracts columns from a single table The number of columns in this table is quite large, and I would like some way to configure the application to say which columns to display. Note: This is not a per-user setting. Lets say I have the main table: MAIN_TABLE id first_name last_name weight h...

What is the purpose (or use case) for an outer join in SQL?

Is an outer join only used for analysis by the developer? I'm having trouble finding a use case for why you would want to include data in two or more tables that is unrelated or does not "match" your select criteria. ...

LINQ To SQL - How to Join using function result

I have a query that needs to retrieve a string value based on a function and it needs to join to another table using that string. Background Info: I have one table that contains my company's data and one column in particular contains company id's (integers). I have another table that we import data into which contains our vendor data an...

How can I turn these LINQ joins into LEFT OUTER joins?

var auditAgencyRecords = (from ag in db.Agencies join ara in db.AuditRuleAccounts on ag.Agency_Id equals ara.AgencyID join arr in db.AuditRuleResults on ara.AuditRuleAccountID equals arr.AuditRuleAccountID join are in db.Audi...

select x posts, regardless of number of inner joined category rows

A table with blog posts that are in one or more categories. Now I want to select 10 posts and having the categories for each of the posts. That means inner joining each post on the post-to-category table etc. Problem: limit 10 gives only 10 rows (eg one post with 10 categories). I want to have 10 different posts with all categories for ...