Update From A Select With Multiple Joins

Feb 1, 2014

I am trying to do a query like this...

UPDATE g
SET g.GroupID = gp.GroupID, g.Contact1 = members.FirstName, g.BusPhone1 = members.BusPhone, g.HomePhone1 = members.HomePhone, g.Internet1 = members.Email
FROM statelst AS g INNER JOIN
grpcon AS gp ON g.GroupID = gp.GroupID INNER JOIN
members ON gp.MemberID = members.MemberID CROSS JOIN

I have my table statelst that I want to update certain columns from the values returned by a select on the grpcon table joined to the members table.I am getting an error "Incorrect syntex near 'JOIN'.

View 1 Replies

Select Distinct Records With Multiple Joins

Mar 13, 2008

View 3 Replies View Related

Update Multiple Column Set Select Statement

Nov 14, 2013

i have a table named masterlist wherein the columns are :

name-----age------sex
andrew---19-------male
trisha---23------female

and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..

View 3 Replies View Related

Newbie - How To Update Multiple Rows With Select Statment

Oct 25, 2004

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

Pam

View 8 Replies View Related

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.

My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related

Multiple Joins - Need Help

Aug 23, 2007

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields.... can you help me with the syntax:
SELECT DISTINCT

View 1 Replies View Related

Multiple Inner Joins

Feb 19, 2008

I cant find the problem with this query for the life in me
SELECT ForumTopic.*, websiteinfo.shortdomainname AS author, MemberInfo.postcount AS pc, MemberInfo.joined AS jd FROM ForumTopic INNER JOIN websiteinfo ON ForumTopic.domaininfoid=websiteinfo.domaininfoid INNER JOIN websiteinfo websiteinfo2 ON MemberInfo.domaininfoid=websiteinfo2.domaininfoid WHERE ForumTopic.id = 1
the error message is:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.domaininfoid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.postcount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.joined" could not be bound.

Can anyone help with this error? Thanks for any responses

View 3 Replies View Related

Multiple Joins

Feb 3, 2006

Hello,

I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
Colin

View 1 Replies View Related

Multiple Self Joins

Mar 28, 2012

Application called Filemaker, I'm now trying to use that experience to teach myself SQL (using Microsoft SQL is SQL 2008 r2).My question is can you have multiple self joins.

I have a table which has two columns (for this example)

spell | spellindicator | episode

the spell is not unique e.g. there could be several records with the spell 'A1234' the spellindicator column will contain a number 1 or 2. the data could look like
this:

spell-------spellindicator---------episode
A1234-----------1-----------------2
B5674-----------1-----------------1
C8739-----------2-----------------3
A1234-----------1-----------------1
B5674-----------2-----------------1
A123------------2-----------------2
A1234-----------1-----------------3

I want to create two left self joins:

1) to count the records that have the same order number with the spellindicator being 1.
2) to find the max and min episode

For 1) I've created two keys:

pk_indicator = spell +':1'
fk_indicator = spell + ':' + spellindicator

example data:
pk_indicator------fk_indicator
A1234:1-----------A1234:1
B5674:1-----------B5674:1
C8739:1-----------C8739:2
A1234:1-----------A1234:1
B5674:1----------- B5674:2
A1234:1-----------A1234:2
A1234:1-----------A1234:1

For2) The join is on the spell

Part 1) works fine on its own
Part 2) works fine on its own

But put both joins in the same statement and the count function no longer works correctly. I realise that there is an issue with the self joins but I thought it would be possible to have multiple self joins.

use leis
go
select l1.spell, l1.spellindicator, l1.episode, l1.pk_Indicator, l1.fk_indicator
,maxepisode = MAX(e2.episode)
,minepisode = MIN(e2.episode)

[code]....

View 2 Replies View Related

Multiple Inner Joins

Oct 23, 2007

I have a customer table which has customer billing addresses; an orders table which has payment and shipping method info; and an items table which has line items for each order. The customer table has a custnum index field. The orders table has a custnum and orderid field. And the items table has an orderid field.

I'd like to be able to retrieve, in one query, the customer's billing address, all of their orders, with each of their order's line items.

What would the query look like in order to accomplish this?

Thanks in advance.

View 1 Replies View Related

Joins On UPDATE

Jul 23, 2005

I need some help understanding what's happening on a join when updatingtables. Assume 2 tables with identical structures:UPDATETable1SETField1 = Table2.Field1,Field2 = Table2.Field2FROMTable2WHERETable1.Field3 = Table2.Field3AND Table1.Field4 = Table2.Field4Indexes exist on Field3 and Field4 on both tables. So why does SQLServer choose a hash join?Thanks in advance.

View 3 Replies View Related

I'm Having Trouble With Doing Multiple Joins

Mar 7, 2001

Here is what I am currently doing:
********************************************
SELECT Hours.Hours, Hours.Comments
FROM Hours INNER JOIN Employee
ON Employee.UserID = Hours.UserID
INNER JOIN Task
ON Hours.TaskID = Task.TaskID
INNER JOIN Project
ON Hours.ProjID = Project.ProjID
WHERE Hours.Date <= EndDate
AND Hours.Date >= StartDate
AND Hours.Date <= EndDate;
********************************************
Am I doing something wrong here?
Any help would be greatly appreciated!

View 2 Replies View Related

Multiple Joins To The Same Table

Oct 29, 2006

I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.

I have a leagues table structured so:

LeagueID | Name | Player1 | Player 2 ... Player6

and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.

I'm working along the lines of

Code:

select
u1.displayname as Player1,
u2.displayname as Player2
from DCMLeagues as L
inner join Users as u1 on L.player1 = u1.userid
inner join Users as u2 on L.player2 = u2.userid

but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.

-- Chris

View 5 Replies View Related

Multiple Joins To Same Column

Apr 9, 2012

I'm putting together the site for a local darts league, essentially each of the teams completes an 'e-scorecard' for that particular game. This is then stored in a table named 'scorecardIndex' :

scxUID(PK)scxTeam1(int)scxTeam2(int)scxTeam1Capt(int)scxTeam2Capt(int)scxDate(nvarchar 50)scxSubmitBy(int)

So each 'index' points off to the UID of the player who was captain, and the UID of of each team.

What I'm struggling with is joining this all together, as I'm joining both scxTeam1Capt and scxTeam2Capt to the same columns in the 'players' table (plFirstName, plSurname)

plUID(PK)plFirstName(nvarchar 50)plSurname(nvarchar 50) plTeamUID(int)plEmail(nvarchar80)plPassword(nvarcharMAX)

And equally joining scxTeam1 & scxTeam2 to the Teams table to pull back the teams actual name.

Using several inner joins I can pull back the entire row for each team, but the columns have the same name..

SELECT *
FROM scorecardIndex AS S
INNER JOIN teams AS T1
ON T1.teamUID = S.scxTeam1
INNER JOIN teams AS T2
ON T2.teamUID = S.scxTeam2

I'm using MS SQL Server 2008 R2.

I've tried to be as detailed as possible without overdoing it.

View 3 Replies View Related

Multiple Left Joins Help?

Apr 27, 2004

Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.

These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this

emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)

emailRecipients table (emailID_PK, RecipientID, SentToEmail)

luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)

Thanks

View 2 Replies View Related

Joins In Multiple Tables

Dec 7, 2013

Table 1:

id amount
1 100
2 200
3 300
4 400

Table 2:

id amount
1 100
1 100
2 200
3 300
4 null

Table 3:

id amount
1 null
2 200
2 200
3 300
3 200
4 null

id is common for each tables , how can i get output like this:

Collapse | Copy Code

id t1 t2 t3
1 100 200 null
2 200 200 200
3 300 300 500
4 400 null null

I am stuck with this query .

View 1 Replies View Related

New Column In Multiple Joins

Aug 21, 2014

In the below query

with cte1
as (select siteid
,productcategory
,sum(isnull(netsales,0)) as netsales
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())

[Code] ....

I need to add routeid column so that i can finally display sum of sales grouped by routeid in addition to the siteid that i am already displaying.

View 4 Replies View Related

Joins Between Multiple Tables?

Feb 4, 2015

I have a general question concerning joins. Below is a table scenario:

SELECT *
FROM TABLE_A T0
INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column]
LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]

Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?

View 1 Replies View Related

Multiple Joins In SQL Server

Jan 4, 2008

Hi I have a requirement where in i haev to convert the SQL from Oracleto the one which will run on the SQL server.in the Oracle Query i am doing multiple joins, between some 13 tables.and some of these joins are inner joins and some are Left outer joins.table1 inner joined with table 2table2 inner join with table3table2 inner join with table4table2 left join with table5table5 left jin with table6table6 left jin with table7table7 left jin with table8table8 left jin with table9Any idea how to achieve this??Tianaren

View 2 Replies View Related

Problem With Multiple Joins.

Jul 20, 2005

Hi All Database Gurus,I am trying to write code which will produce all the possible validqueries, given tables and join information for tables.Right now i am just trying to construct all the sequential joins.eg. if i have 4 tables A, B, C, D and the join conditions areA Inner join B,B Inner Join C,C Left Outer join Dthen i am constructing joins as :1. A IJ B IJ C LJ D2. B IJ A IJ C LJ D3. B IJ C IJ A LJ D4. B IJ C LJ D IJ A5. C IJ B IJ A LJ D6. C IJ B LJ D IJ A7. C LJ D IJ B IJ AI am not placing any paranthesis to specify the join order. And manyof them are giving me same output.Can anybody tell me how to detect the joins which will give the sameoutput ?here in this case the number of combinations are 7 but for 8 tables iam getting 420 combinations and many of them are same.so please help me to reduce the number of combinations.eagerly waiting for suggetions.Thanking you.Prem.(premratan@hotmail.com)

View 1 Replies View Related

Multiple Joins In A Filter

Feb 15, 2007

I'm using Sql 2005 and merge replication.

I want to create a dynamic merge filter on a table but the data I filter on is only reachable by a few extra joins. For example:

I want to filter Table3. Table3 is joined to Table2 which in joined to Table1. Table1 contains the host_name column that I use to filter i.e. host_name = HOSTNAME().

In order to get filtered data in Table3 do I have to also filter Table 2 and Table1, or in my filter clause for Table3 can I say something like: Table3.FKid = Table2.Id and Table2.FKId = Table1.Id and Table1.host_name = HOSTNAME()?

I'm pretty sure I can't use AND in my dynamic filter clause but I just wanted to make sure.

Thanks for your help

Graham

View 1 Replies View Related

Multiple Joins On The Same Table

Jan 5, 2006

I'm new to SQL 2005 & C# - I'm a MySQL/PHP crossover.

I'm using s Stored Procedure and I'm trying to do multiple joins onto
one table. I have 6 fields in one table that are foreign keys of
another table:

Table1

---------

id

PrimaryCode

SecondaryCode1

SecondaryCode2

SecondaryCode3

SecondaryCode4

SecondaryCode5

Table 2

---------

id

Title

CommCode

The fields in table 1 (except is obviously) hold the id of a row in
Table 2. When displaying data I want to display "Title" -
"CommCode" for each item in Table 1. I got myself started by
searchig on the net and I have a stored procedure. The obvious
problem is that as it goes through the Query only the last value
remains in place - since each value before it is cleared in the
UNION. How can I do this?? Here's my Stored Procedure:

=====================================

ALTER PROCEDURE GetRegistersSpecific

@SearchTxt int

AS

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
PrimaryCode AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON PrimaryCode = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode1 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode1 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode2 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode2 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode3 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode3 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode4 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode4 = CommodityCodes.id

UNION

SELECT
registrations.Company,registrations.Address1,registrations.Address2,registrations.City,registrations.State,registrations.Zip,registrations.ContactName,registrations.Phone,registrations.Fax,registrations.Email,registrations.Website,registrations.Feid,registrations.BusinessType,registrations.BackupWitholding,registrations.SignedName,registrations.SignedDate,
SecondaryCode5 AS MyID, Title, CommodityCode

FROM registrations

JOIN CommodityCodes ON SecondaryCode5 = CommodityCodes.id

WHERE registrations.ID = @SearchTxt

=====================================

Thanks

View 4 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian

View 5 Replies View Related

Update / Delete With Joins

Jul 23, 2005

Asking for some sample SQL/SP code to perform updates/deletes with joinedtables.Example 1:For every record that is matched on both tables A and B, update a field intable A.Example 2:For every record that is matched on both tables A and B, delete record intable A.TIA.~ Duane Phillips.

View 2 Replies View Related

Exec Storeprocedure - Multiple Joins

May 5, 2008

Hi,I am written a store procedure that would access four tables and grab appropriate fields.. I am using JOIN functionality because it can have multiple rows. ( The goal is: )Step 1: User can search by ID or MEMBER_ID or both .. grab all the data from mainTable based on the search. WORKS.Step 2: TABLE 2 (userTable table) get EMAIL for each record that was grabbed.. based on the ID. WORKS.Step 3: TABLE 3 and TABLE 4.. I am having some problems combing into the query.. how to add multiple JOINS… Is it safe? Please see below what data needs to be combined into the query.--Code works for Step 1 and 2.declare @ID varchar(20), @MEMBER_ID varchar(20) set @ID= null --testing data.. set @MEMBER_ID ='15552' –testing data.. Select MainTable.REFNO,MainTable.ID,mainTable.MEMBER_ID,userTable.EMAILFROM mainTableLEFT JOIN userTableON mainTable.ID = userTable.IDWhere (mainTable.ID = @ID OR @ID IS NULL) and(mainTable.MEMBER_ID = @MEMBER_ID OR @MEMBER_ID IS NULL)TABLE 3: (works by itself)SELECT SR.COMPANY, SR.LOCATION_NOFROM SI INNER JOIN SR ON SI.SR_ID = SR.SR_IDWHERE SI.ID = MainTable.ID)ORDER BY SR.DATE_RECEIVED DESCTABLE 4: (works by itself)I will be retrieving LOCATION_NO from SR table and comparing the value to the below query: for each record that was found in the mainTable.select LOCATION_NAME from locationwhere LOCATION_NO= SR.LOCATION_NO Please help me solve this.. Thank you

View 5 Replies View Related

Multiple Joins - Duplicate Rows

Jul 10, 2014

have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.

select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,

[code]....

View 4 Replies View Related

Merging Multiple Rows From Joins

Dec 15, 2014

I am still fairly new to SQL, having been tasked with creating a csv file from data now someone else has left.

I can do the csv export using sqlcmd and I have the query sorted and am pulling out the right data, but it generates two rows, as one of the tables has multiple records per cardholder. See the query below, I know there is a way of doing it with XML PATH, I think, but it has got me slightly confused.

set nocount on

selectdbo.card.EncodedNumber,
dbo.card.IsEnabled,
dbo.Cardholder.FirstName,
dbo.cardholder.LastName,
dbo.card.ExpiryTime,
dbo.PersonalDataString.Value

[Code] .....

View 2 Replies View Related

Multiple INEER JOINS Between Two Table.

Sep 16, 2007

Hello Everyone,

I have a quite unusual problem, and I have hard time finding the answer.

I have a table with Locations - lets say that it has just ID, and Name,
and a Transport table containing the ID, ArrivalLocationID and DepartureLocationID.

Now - when I select the Transport table I want to get names of the Arrival and Departure locations from th other table.

If it was a single link I woul do an INNER JOIN like:

SELECT
Transport.*,
Locations.Name AS ArrivalLocation
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
Transport.ArrivalLocation = Locations.ID

But I want to do a double INNER JOIN between two same table. And here I get a problem - how to do it? Something like:

SELECT
TransportProductOperationPeriods.*,
Locations.Name AS LArrivalLocation,
Locations.Name AS LDepartureLocation,
Locations.ID AS LArrivalLocationID,
Locations.ID AS LDepartureLocationID
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
TransportProductOperationPeriods.ArrivalLocation = LArrivalLocationID
INNER JOIN Locations ON
TransportProductOperationPeriods.DepartureLocation = LDepartureLocationID

but obviously that does not work ;)

Thanks in advance,
Joseph

View 2 Replies View Related

How Do You Do Multiple Left Outer Joins???

May 31, 2006

I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys. I bolded the three IDs that could be NULL or have a foreign key for a value. An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet. Right now I only get profiles that have foreign key values and it misses the rest in the search.   So NULL MakeID or ModelID no result on that item my SQL statement below. Using SQL Server 2005. Pretty new this to SQL and databases but so far this has been the only trying part.

Thanks

String dbsql = "SELECT a.EquipmentID " +
                         "     , a.SerialNo " +
                         "     , b.Category " +
                         "     , c.Subcategory " +
                         "     , d.Make " +
                         "     , e.Model " +
                         "     , f.Status " +
                         " FROM tblEquipInfo a " +
                         "     , tblEquipCat b " +
                         "     , tblEquipSubcat c " +
                         "     , tblEquipMake d " +
                         "     , tblEquipModel e " +
                         "     , tblStatus f " +
                         " WHERE b.Category = '" + val + "' " +
                         "   AND a.CategoryID = b.CategoryID " +
                         "   AND a.SubcategoryID = c.SubcategoryID " +
                         "   AND a.MakeID = d.MakeID " +
                         "   AND a.ModelID = e.ModelID " +
                         "   AND a.StatusID = f.StatusID";

View 3 Replies View Related

Quries Involving Multiple Joins

Jan 6, 2008

Here are the tables I am dealing with:

Tables
license TABLE
---------------
license_id int PK
vendor_id int FK
po_id int FK Nulls
license_type_id int FK
lic_user_id int FK Nulls
location_id int FK Nulls
lic_start_date smalldatetime Nulls
days_allowed int Nulls
serial_num varchar(50) Nulls
activation_key varchar(50) Nulls
max_users int Nulls
comments varchar(1000) Nulls

software TABLE
------------------
software_id int PK
os_id int
software_name varchar(150)
comments varchar(1000) Nulls

software_license TABLE
--------------------------
license_id int PK , FK
software_id int PK , FK
comments varchar(1000) Nulls

dept_license TABLE
---------------------
dept_id int PK , FK
license_id int PK , FK
comments varchar(1000) Nulls

purch_order TABLE
---------------------
po_id int PK
po_number varchar(50)
po_date smalldatetime
comments varchar(1000) Nulls

dept_purch_order TABLE
----------------------------
po_id int PK , FK
dept_id int PK , FK
comments varchar(1000) Nulls

I am using the following stored proceedure to try to create a useful report from those tables:

Code Block

ALTER PROCEDURE [dbo].[ListDepartmentLicenses]
@Department int,
@Software int
AS
BEGIN
SET NOCOUNT ON;
SELECT dept_license.license_id,
license.lic_start_date,
department.dept_name,
purch_order.po_number,
software.software_name
FROM dept_license
JOIN license
ON dept_license.license_id = license.license_id
JOIN department
ON dept_license.dept_id = department.dept_id
JOIN dept_purch_order
ON dept_purch_order.dept_id = dept_license.dept_id
JOIN purch_order
ON dept_purch_order.po_id = purch_order.po_id
JOIN software_license
ON dept_license.license_id = software_license.license_id
JOIN software
ON software_license.software_id = software.software_id
WHERE (department.dept_id = @Department or @Department Is Null) and
(software.software_id = @Software or @Software Is Null)
ORDER BY license.lic_start_date
END

The sproc compiles fine but I get no rows returned.

View 7 Replies View Related

Stored Procedure With Multiple Joins Problem

Feb 9, 2005

I have a database of news articles and i have a stored procedure that basically pulls one from the database based on an ID number. The author (Press Contact) and publication are stored as just ID numbers and pulled in via JOINs.

SELECT Articles.date_published, Articles.headline, Publication.press_contact,
Publication.pub_name, Articles.body
FROM Articles
LEFT OUTER JOIN
PressContact ON PressContact.press_id = Articles.press_id
LEFT OUTER JOIN
Publication ON Publication.publication_id = Articles.publication_id
WHERE (Articles.id = @ID)

Everything works great in this setup. However, we've recently added a press_id2 field to the articles table to be able to store a 2nd press contact. So now I need my stored procedure to pull out both press contact names and I'm not sure the best way to do that.
I tried to JOIN the PressContact table a 2nd time on PressContact.press_id = Articles.press_id2 but that didn't seem to work.
Can anyone give me any suggestions?
Thanks in advance.

View 2 Replies View Related

Getting Multiple Counts With Grouping And Left Joins

Mar 3, 2008

Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?

Thanks in advance.

drop table percentMinorities;
create view percentMinorities as
select s1.schoolid,
round(count(s2.studentid)/count(s1.studentid),2) as percentWhite,
round(count(s3.studentid)/count(s1.studentid),2) as percentBlack,
round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic,
round(count(s5.studentid)/count(s1.studentid),2) as percentAsian

from

students as s1 left join
(students as s2 left join
(students as s3 left join
(students as s4 left join students as s5
on s4.studentid is not null and s5.ethnicity = 'A')
on s3.studentid is not null and s4.ethnicity = 'H')
on s2.studentid is not null and s3.ethnicity = 'N')
on s1.studentid is not null and s1.ethnicity = 'O'

group by s1.schoolid

View 10 Replies View Related

T-SQL (SS2K8) :: Query Using Multiple APPLY Joins

Mar 20, 2014

I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.

Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.

Here are some things I’ve tried so far…

1.Changed the States table into a subquery that only returns a single state
2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other).
3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.

SELECT s.state_name
, COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits
, SUM(DistinctPermits) AS DistinctPermits
FROM States S
CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits

[Code] ....

View 9 Replies View Related