join

MySQL calling in Username to show instead of ID!

I have a users table, books table and authors table. An author can have many books, while a user can also have many books. (This is how my DB is currently setup). As I'm pretty new to So far my setup is like bookview.php?book_id=23 from accessing authors page, then seeing all books for the author. The single book's details are all displa...

How can I use two or more COUNT()s in one SELECT statament?

i develop this code: SELECT COUNT(NewEmployee.EmployeeID), NewEmployee.EmployeeId,EmployeeName FROM NewEmployee INNER JOIN NewTimeAttendance ON NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID and NewTimeAttendance.TotalTime is null and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null) and (month = 1 or ...

MySQL search for user and their roles

I am re-writing the SQL which lets a user search for any other user on our site and also shows their roles. An an example, roles can be "Writer", "Editor", "Publisher". Each role links a User to a Publication. Users can take multiple roles within multiple publications. Example table setup: "users" : user_id, firstname, lastname "pub...

LINQ to SQL, select targets with max date

I ended up with this horrible code below, I can't get a better result now. What is a better way of doing that? It's about this part of my database: EDIT A Patient has a Subscription to multiple MonitoringObjects. Target records refer to these Subscriptions. I want to retrieve the target records with the newest date per Subscription ...

Is it possible to LIMIT results from a JOIN query?

I've got a query that currently queries a Post table while LEFT JOINing a Comment table. It fetches all Posts and their respective Comments. However, I want to limit the number of Comments returned. I tried adding a sub-select, but ran into errors if I didn't LIMIT the results to 1. I'm really not sure how to go about this while still u...

Join query in doctrine symfony

I have two tables userdetails and blog question The schema is UserDetails: connection: doctrine tableName: user_details columns: id: type: integer(8) fixed: false name: type: string(255) fixed: false BlogQuestion: connection: doctrine tableName: blog_question columns: question_id: ...

HasMany relation inside a Join Mapping

So, I'm having a problem mapping in fluent nhibernate. I want to use a join mapping to flatten an intermediate table: Here's my structure: [Vehicle] VehicleId ... [DTVehicleValueRange] VehicleId DTVehicleValueRangeId AverageValue ... [DTValueRange] DTVehicleValueRangeId RangeMin RangeMax RangeValue Note that DTValueRange does not ha...

Doctrine: Update Join?

Hi, Anyone know how to do an update with a join (i.e. update on two tables in one query) in Doctrine 1.2? I spotted something obscure on a forum that hinted that this is not supported in 1.x but it was about as vague as it comes. Thank you. ...

MySQL UPDATE WHERE IN for each listed value separately?

Hi, I've got the following type of SQL: UPDATE photo AS f LEFT JOIN car AS c ON f.car_id=c.car_id SET f.photo_status=1 , c.photo_count=c.photo_count+1 WHERE f.photo_id IN ($ids) Basically, two tables (car & photo) are related. The list in $ids contains unique photo ids, such as (34, 87, 98, 12). With the query,...

MySQL join headaches, please help!

Ok, I've hit the wall here and need some help. Sample tables are as follows: SCENARIO_NATIONS [scenID] [side] [nation] scen001 1 Germany scen001 2 Britain scen001 2 Canada SCENARIO_NEEDUNITS [scenID] [unitID] scen001 0001 scen001 0003 scen001 0107 scen001 0258 scen001 0759 UNIT_BASIC_DATA [u...

methods of joining 2 tables without using JOIN or SELECT more than one distinct table in the query

Is there a way of joining results from 2 tables without using JOIN or SELECT from more than one table? The reason being the database im working with requires queries that only contain SELECT, FROM, and WHERE clauses containing only one distinct table. I do, however, need information from other tables for the project i'm working on. More...

error with linq join

I have this linq query: var segreterie = from s in db.USR_Utenti join h in db.USR_Accounts on new {s.ID, settings.GruppoSegreteria} equals new {h.USR_UtentiReference,h.ID_Gruppo} select s; that has this problem: The type of one of the expressions in the join clause is incorrect. Type infere...

SQL statement to split a table based on a join

I have a primary table for Articles that is linked by a join table Info to a table Tags that has only a small number of entries. I want to split the Articles table, by either deleting rows or creating a new table with only the entries I want, based on the absence of a link to a certain tag. There are a few million articles. How can I do ...

SQL Inner Join : DB stuck

I postet this question a few days ago but I didn't explain exactly what I want. I ask the question better formulated again: To clarify my problem I added some new information: I got an MySQL DB with MyISAM tables. The two relevant tables are: * orders_products: orders_products_id, orders_id, product_id, product_name, product_price, pr...

PostgreSQL JOIN with array type with array elements order, how to implement?

Hello I have two tables in database: CREATE TABLE items( id SERIAL PRIMARy KEY, ... some other fields ); This table contains come data row with unique ID. CREATE TABLE some_choosen_data_in_order( id SERIAL PRIMARy KEY, id_items INTEGER[], ); This table contains array type field. Each row contains values of IDs from table "ite...

Sql joining a table

I have a question regarding the SQL joins - Whenever we join two different tables on some fields, what will happen exactly inside oracle which will result in the query output? Does Oracle create/use a temporary table just for presenting the query output? ...

problem with a join

I have this code: int se = (int) settings.GruppoSegreteria; var acc = db.USR_Accounts.Where( p => p.ID_Gruppo == se); var segreterie = from s in db.USR_Utenti join h in acc on s.ID equals h.USR_UtentiReference select s; And this error: The type of one of the expressions in the join c...

Is a JOIN more/less efficient than EXISTS IN when no data is needed from the second table?

I need to look up all households with orders. I don't care about the data of the order at all, just that it exists. (Using SQL Server) Is it more efficient to say something like this: SELECT HouseholdID, LastName, FirstName, Phone FROM Households INNER JOIN Orders ON Orders.HouseholdID = Households.HouseholdID or this: SELECT Ho...

Duplicate Items Using Join in NHibernate Map

I am trying to retrieve the individual detail rows without having to create an object for the parent. I have a map which joins a parent table with the detail to achieve this: Table("UdfTemplate"); Id(x => x.Id, "Template_Id"); Map(x => x.FieldCode, "Field_Code"); Map(x => x.ClientId, "Client_Id"); Join("UdfFields", join => { join....

Linq join with an inner collection

Hi, I am trying a LINQ to Object query on 2 collections Customer.Orders Branches.Pending.Orders (Collection within a collection) I want to output each branch which is yet to deliver any order of the customer. var match = from order in customer.Orders join branch in Branches on order equals branch.Pending.Orders selec...