join

error in query with JOIN USING

Consider this simple query: SELECT * FROM table1 JOIN table2 USING(pid) WHERE pid='2' ; I get this error: SELECT command denied to user 'root'@'localhost' for column 'pid' in table 'table1' When I replace USING with ON (and this right syntax...) the error disappears. What is the problem? ...

Is a join appropriate for accomplishing such functionality in LINQ to SQL?

I'm writing an ASP.NET MVC site where I'm using LINQ to SQL to access my SQL Server database. In my database, I have the following tables: Posts: PostID - int, PK, identity Text - nvarchar(MAX) PublishDate - datetime etc. PostTags: PostTagID - int, PK, identity PostID - FK to PK to Posts table TagID - FK to PK to Tags table Tags...

MySQL: Multiple Photos and Videos for News with Joins

I want multiple photos and multiple videos, the main problem is that I can't get them inline if I don't use joins. So for example, I get 2 photos a video and again a photo. I have a parent news table and 2 secondary table news_photos and news_videos and I want to get in one query the photos and videos for the news. Is this somehow pos...

sql tuning - with multiple joins

I recently read somewhere that one of ways of tuning sql query is - If it has too many joins then do one join with fewer tables and cache the results in a temporary table. Then do the rest of the query joining on that table. My question - How it will improve the performance, as you are joining same numbers of tables. (Only not together)...

MySQL Join and get all relations even if 0

I got two tables. One that holds all availeble trophys and one that holds the relation between user and trophy. trophy -------------------------------------- | trophy_id | name | -------------------------------------- | 1 | kill 100 people | | 2 | kill 200 people | | 3 | fly 5000 f...

MySQL Query Join and Count Query

Hi, I'm trying to pull values from a database for a web app where a moderator can add companies to a list of specified industries. This request needs to pull each industry's name along with a count of attached active companies, as an overview to the moderator. These are my tables: companies ____________________________________ | id ...

SQL Searching entries not yet grouped

I have two tables: +------- IPs ------+ +---- Groups ---+ | Id (Int) | | Id (Int) | | IP (String) | | Name (String) | | Comment (String) | | IPs (String) | +------------------+ +---------------+ The IPs-column of Groups is a comma-separated list of the IPS that this group consists of (The Id of the IP in the IP...

LINQ and joining on selects

I have the following SQL: select o.tekst as Enhet, coalesce(f.Antall,0) as AntallF, coalesce(f.snitt,0) as SnittF, coalesce(b.antall,0) as AntallB from tblhandlingsplan hp inner join tblorg o on hp.eierorgid = o.orgid left outer join (select f.handlingsplanid, count(t.tiltakid) as Antall, coa...

Storing user action data in MySQL: one table or many?

I am building a website wherein the users can perform a variety of actions and they get a variable number of "points" or "badges" for performing specific actions. Certain data must be stored no matter which type of action the user performs, such as the user ID, the action type, a timestamp, the current point total, and any badge awarded....

In MySQL, how to JOIN two very large tables which both have columns in the WHERE condition?

I'm trying to determine the best general approach for querying against joined two tables that have a lot of data, where each table has a column in the where clause. Imagine a simple schema w/ two tables: posts id (int) blog_id (int) published_date (datetime) title (varchar) body (text) posts_tags post_id (int) tag_id (int) Wi...

Mysql Join and Order

Hello, I have 3 tables that have same columns,but different data ( deposits,withdrawals,transfers ) CREATE TABLE IF NOT EXISTS withdrawals ( id int(11) NOT NULL auto_increment, user varchar(12) default NULL, amount double(12,2) default NULL, date timestamp NULL default CURRENT_TIMESTAMP, time varchar(50) defau...

Combining two select queries

A the moment I am using two queries one is called initially, and the second is called during a loop through the results of the first. I want to combine both queries, but have been unable to so far. The tables the queries are pulling from are: +--------------+ +--------------+ +--------------------------+ | table_1 | | t...

Joining Tables,- Normalizing Fields

I have two tables. One table is named "Posts", the other is "Threads". Each of these has 3 columns named (id, author, content). The Posts table has a specific (thread) column. The thread column has to correspond to the id in the Threads table. What I'm trying to do is make one query that will select the thread, and all of its posts,...

Left Outer join and an additional where clause

Hi, I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause on a field from the right-hand table, but.... I still want a row from the left-hand table to be returned for each re...

Joining other Lists into SharePoint DataView - troubleshooting xsl

I have very similar working code. I've tried it inside an existing sharepoint generated view and a new custom page. none of the columns or lists have been renamed or have spaces. I've trippled checked the data, column names and this code. It just produces nothing. any good way to troubleshoot xsl? <xsl:value-of select="/dsQueryResponse...

Stuck with an SQL join query

I'm making a Q&A site, similar to this site and Yahoo answers. I have 3 tables - smf_members, qa_questions and qa_answers. In this query, I want to select some fields from qa_questions, a few fields from smf_members and the number of records in ga_answers for the question_id. This is so I can have some basic info on the question, some b...

Hibernate Criteria, Joins, Or

I'm trying to accomplish ad-hoc queries using joined tables, using an Restrictions.or and Restrictions.ilike My entities look like: @Entity @Table(name="CASE_REVIEW") public class CaseReview { @Id @Column(name = "REVIEW_ID", unique = true, nullable = false, length = 19) private Long reviewId; @ManyToOne(fetch = FetchT...

nHibernate Linq join support; The method Join is not implemented

I am using the latest nHibernate.Linq 2.1.2. But if I use join in my linq query I will get run time error "The method Join is not implemented". Could someone tell me if join is supported by nHibernate.Linq and if it is supported what is the cause of this error? ...

SQL Joins: Future of the SQL ANSI Standard (where vs join)?

We are developing ETL jobs and our consultant have been using "old style" SQL when joining tables select a.attr1, b.attr1 from table1 a, table2 b where a.attr2 = b.attr2 instead of using inner join clausule select a.attr1, b.attr1 from table1 as a inner join table2 as b on a.attr2 = b.attr2 My question is that in the long run, i...

For each row from one table only one row from another table (mysql)

Hi, I use MySQL 5.1. I have two tables T1(hash, value) and T2(hash, value) and I connect them using hash. They contain: ---- T1 ---- 1 A 2 B 3 C 4 D ---- T2 ---- 1 E 1 F 3 G 4 H My purpose is to get all rows from T1 which has connection with any row from T2. I tried to do so with: SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.hash = T2.h...