join

Problem with table JOIN in MySQL and aggregation

I have three tables. This query will write down the right answer (x-lines for btv.id_user with appropriate btv.cas and race.id_zavod SELECT `btv.id_user`, `btv.id_zavod`,`btv.cas` FROM `btv` JOIN `btu` ON `btv.id_user` = `btu.id_user` JOIN `race` ON 'btv.id_zavod' = `race.id_zavod` WHERE `race.type` = '8' AND `btv.id_user` = '607' Res...

How do you JOIN two MySQL tables where the data is NOT in the other table?

For example, I have a pet table and a lost pets table. I want to get all of the pets that are NOT lost with 1 join and no sub-selects. Is that possible? A typical join only returns results that are in both tables. ...

Items from multiple categories best practices

Hi, I'm making list of items in categories, problem is that item can be in multiple categories. What is your best practice to store items in categories and how list all items within category and its child categories? I am using Zend Framework and MySQL to slove this issue. Thanks for your replies. Sorry for my English :) ...

What's the difference between "using" and "on" in table joins?

Is this ... T1 join T2 using(ID) where T2.VALUE=42 ... the same as ... T1 join T2 on(T1.ID=T2.ID) where T2.VALUE=42 ... for all types of joins? That's my understanding of using(ID), its just a short hand for "on(T1.ID=T2.ID)". Is this true? Now for another question Is the above the same as ... T1 join T2 on(T1.ID=T2.ID and T2...

How do you do many to many table outer joins?

I have 3 tables, foo, foo2bar, and bar. foo2bar is a many to many map between foo and bar. Here are the contents. select * from foo +------+ | fid | +------+ | 1 | | 2 | | 3 | | 4 | +------+ select * from foo2bar +------+------+ | fid | bid | +------+------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 3...

How to do joins in LINQ on multiple fields in single join

I need to do a LINQ2DataSet query that does a join on more than one field (as var result = from x in entity join y in entity2 on x.field1 = y.field1 and x.field2 = y.field2 I have yet found a suitable solution (I can add the extra constraints to a where clause, but this is far from a suitable solution, or use this ...

Join in linked server or join in host server?

Here's the situation: we have an Oracle database we need to connect to to pull some data. Since getting access to said Oracle database is a real pain (mainly a bureaucratic obstacle more than anything else), we're just planning on linking it to our SQL Server and using the link to access data as we need it. For one of our applicati...

PHP join help with two tables

Hi. I am just learning php as I go along, and I'm completely lost here. I've never really used join before, and I think I need to here, but I don't know. I'm not expecting anyone to do it for me but if you could just point me in the right direction it would be amazing, I've tried reading up on joins but there are like 20 different met...

SQL Select based on Link Field

I feel like an idiot asking this... Table 1: users id serial person integer username char(32) Table 2:persons id serial name char(16) Can I run a query that returns the name field in persons by providing the username in users? users 1 | 1 | larry123 persons 1 | larry 2 | curly SQL? select name from persons where users.person=p...

Does LINQ use DataRelations to optimize joins?

I can't find the answer to this anywhere, and before I start pawing through generated code with Reflector I thought it'd be worth asking: Suppose I have the following LINQ query run against DataTables in a DataSet: var list = from pr in parentTable.AsEnumerable() join cr in childTable.AsEnumerable() on cr.Field<int>("ParentID") ...

[SQL] What's the best way to get related data from their ID's in a single query?

I have a table where each row has a few fields that have ID's that relate to some other data from some other tables. Let's say it's called people, and each person has the ID of a city, state and country. So there will be three more tables, cities, states and countries where each has an ID and a name. When I'm selecting a person, what'...

Is it possible to concatenate strings from multiple rows and tables into one result column?

I am trying to write a MySQL query that retrieves one record from table "projects" that has a one-to-many relationship with table "tags". My application uses 4 tables to do this: Projects - the projects table Entities - entity table; references several application resources Tags - tags table Tag_entity - links tags to entities Is it p...

Is this join hint dangerous?

A coworker asked me to look at indexing on some tables because his query was running very long. Over an hour. select count(1) from databaseA.dbo.table1 inner join databaseA.dbo.table2 on (table1.key = table2.key) inner join databaseB.dbo.table3 on (table1.key = table3.key) Note the different databases. This was being run from Databa...

.NET and Oracle: Joining a table to an empty set of another table

Hi all, I am trying to join tableA with some data to an empty set of another tableB. The main purpose is to get all the columns of tableB. I do not need any data of tableB. I have constucted the following SQL: SELECT uar.*, s.screen_id, s.screen_name FROM crs_screen s LEFT JOIN crs_user_access_right uar ON s.rid IS ...

MySQL - Selecting data from multiple tables all with same structure but different data

Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records. A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more...

SQL Join Differences

What's difference between inner join and outer join (left join,right join), and which has the best performance of them? Thanks! ...

Can I join to a table in ORACLE (10g) using a CASE clause in the ON statement (or even where clause as it's an inner join)

I'm trying to make the following code smaller. Is this possible? select a.* from table1 a WHERE a."cola1" = 'valuea1' UNION ALL select a.* from tablea1 a inner join tablea2 b on a."cola2" = b."colb2" WHERE a."cola1" = 'valuea2' and b."colb3" = 'valueb3' In effect I'm looking for records from table1 for value1 or value2, but for rec...

SQL JOIN help

SELECT * FROM friends WHERE (user1 = $userid OR user2 = $userid) AND accepted = 1 how would a JOIN look like if i want to get lets say user2's information from table "users"? table friends: id int(11) NOT NULL auto_increment, user1 int(11) NOT NULL, user2 int(11) NOT NULL, date datetime NOT NULL, accept tinyint(1) NOT NULL, ...

SQL Join maybe?

$query = mysql_query("SELECT * FROM news WHERE id = '{$_GET['id']}'"); $news = mysql_fetch_assoc($query); $sql84 = mysql_query("SELECT username FROM users WHERE id = '".$news['user_id']."'") or exit(mysql_error()); $author = mysql_fetch_array($sql84); is there i better way of doing this? a join maybe? how that look ...

SQL Sub-Query vs Join Confusion

I have a database which is in Access (you can get it link text). If I run SELECT DISTINCT Spl.Spl_No, Spl.Spl_Name FROM Spl INNER JOIN Del ON Spl.Spl_No = Del.Spl_No WHERE Del.Item_Name <> 'Compass' It provides the names of the suppliers that have never delivered a compass. However you can supposedly do this with a sub-query. So f...