query

MySql query help : joints with sums and counts

Hello community, I have this database structure: TBL_A | TBL_B | TBL_C | TBL_D | TBL_E -------+---------+---------+---------+---------- id | id_tbla | id_tbla | id_tbla | id name | id_user | id_user | id_user | name_tbla ... | is_bool | | weight | id_user Here is what I'm trying to achieve : SELECT a.id...

Combining two MySQL Queries (One to select contents of another)

How would I combine the following two quieries to produce a combination of the two? SELECT * FROM post_attributes WHERE name IN ('title', 'body'); SELECT id, url, created_at, name, value FROM posts, post_attributes WHERE posts.id = post_attributes.post_id ORDER BY id; The idea would be to extract the post number, it's url...

mysql split multiquery string with php

I want to make a database restore function that will parse a dump made with Php My Admin and will do the queries. I have already a mysql class that does the query, but it only knows to do a single query. So i am looking of a way to split the file content into single queries. I tried to play with preg_split ... but didn't managed to get...

Does anyone use Right Outer Joins?

I use INNER JOIN and LEFT OUTER JOINs all the time. However, I never seem to need RIGHT OUTER JOINs, ever. I've seen plenty of nasty auto-generated SQL that uses right joins, but to me, that code is impossible to get my head around. I always need to rewrite it using inner and left joins to make heads or tails of it. Does anyone a...

Informix SQL count() comparisons

I'm trying to build a SQL query that will count both the total number of rows for each id, and the number of 'FN%' and 'W%' grades grouped by id. If those numbers are equal, then the student only has either all 'FN%' or all 'W%' or a combination of both. I need a list of all the id's who only have stats of 'FN%' or 'W%' example id # 6...

Mysql order by rating - but capture all

Hi, I have this PHP/MYSQL code which returns records from a table ordered by their ratings, from highest rated to lowest rated: <table width="95%"> <tr> <?php if (isset($_GET['p'])) { $current_page = $_GET['p']; } else { $current_page = 1; } $cur_category = $_GET['category']; $jokes_per_page = 40; $offset = ($current...

SQL: how to join multiple tables?

I have the following tables (and example values): user: user_id (1, 2, 3) username (john33, reddiamond...) password (pass1, pass2...) session: session_id (4,5, 6) user_id (1, 2, 3) activity activity_id (1, 2) name (running, walking...) user_activity user_activity_id (1, 2, 3, 4, 5) session_id (4, 5) activity_id (1, 2) All columns wi...

Average, Count, etc of entire column of table in SQL Query or as Function in VBA to Display result in Form?

I need to calculate Count, Average and a couple other things of a column as a result of a Query and then put the result in a text box in a form. I was thinking about either implementing it in the Query and putting the results in a column or something like that, OR use a VBA function to calculate it; however I don't know how to calcula...

best way to get good with sql queries

As a programmer I would like to get a strong hold on writing queries. In my college years I've read a few sql books and rest I've just learned working as a programmer for last couple of years. But as those queries were work related...they weren't that 'hard' or complex. what would you guys suggest ? Is there a good advanced sql book...

Mysql Query not working

I have three tables that are structured like this: http://i41.tinypic.com/2bt9aq.png What I am trying to do is retrieve the joke id, title, and average rating of all jokes in a certain category and order them alphabetically. I have this query: $result = mysql_query(" SELECT jokedata.id AS joke_id, jokedata.joketitle AS joke_title, SUM(...

XPath 1 query and attributes name

First question: is there any way to get the name of a node's attributes? <node attribute1="value1" attribute2="value2" /> Second question: is there a way to get attributes and values as value pairs? The situation is the following: <node attribute1="10" attribute2="0" /> I want to get all attributes where value>0 and this way: "attr...

Sort a List<T> using query expressions - LINQ C#

I have a problem using Linq to order a structure like this : public class Person { public int ID { get; set; } public List<PersonAttribute> Attributes { get; set; } } public class PersonAttribute { public int ID { get; set; } public string Name { get; set; } public string Value { get; set; } } A person might go li...

SQL query for generating given data

I have a table called student with two columns: JoinDate DATE EmployeeName STRING The contents of the table are as follows: EmployeeName | JoinDate ----------------+----------------- jaison | 1-jan-2008 robin | 2-feb-2008 binoy | 3-mar-2008 rahul | 4-feb-2008 I am looking to prepare the fol...

Ordering Wordpress posts by most recent comment

I'm wanting to order Wordpress posts by the most recent comment. To the best of my knowledge this isn't possible using the WP_Query object, and would require a custom $wpdb query, which I can easily write. However, I then don't know how to setup the loop to run off this object. Can anyone help? ...

SQL Query Question (Possible Join / Subquery?)

Hi, I have two tables (Management and Employee). The management table tracks the different management teams that have managed company X in past few years. Each management team is given an ID (i.e: managementnr), and each team has a CEO (namely ceoname). The employee table tracks employees working for company X (basically just their ...

Querying similar but disjoint datasets in a single SQL query

In SQL (specifically MySQL, but the question is generic enough), what is the most efficient way to query time-series data when I have multiple tables across disjoint time ranges? For example, if my tables are as follows: router1_20090330( unixtime integer unsigned, iface1_in integer unsigned, iface1_...

Computing hashes for strings in SQL Server database - worth the effort?

Say I have a million-row table [mytable] in my SQL Server 2005 database, which has columns: ID SomeField AnotherField Url A very common query in this system is: select * from [mytable] where url = 'http://www.somesite.com/some/really/long/url' Which will give me better performance: a) Add an index to the Url column. or b) Add an...

Need tips for optimizing SQL Query using a JOIN

The Query I'm writing runs fine when looking at the past few days, once I go over a week it crawls (~20min). I am joining 3 tables together. I was wondering what things I should look for to make this run faster. I don't really know what other information is needed for the post. EDIT: More info: db is Sybase 10. Query: SELECT a.id, a.da...

Sql Query with unique column value

My goal is to get a query written. I have three tables, A, B and C. The tables are written such that A.bID = B.bID, and B.cID = C.cID. This basically allows me to write a query where I link a record from a to b, and link the b record to a record from c. So far so good, simple query. What my problem is... one of the columns included in t...

MySQL: get maximum an minimum values in ONE query

hi folks, i'm searching for a way to get from two coloumns (lets say: posx and posy) the global (whole table) maximum and minimum with just one query. Database is MySQL thanks ...