Multi-table JOIN Query With More Than One JOIN Statement
Apr 14, 2015
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
View 2 Replies
ADVERTISEMENT
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Feb 7, 2002
I want only one row for each contact that contains the most recent calendar as enddate and most recent history as ondate. I'm getting multiple rows for each matching contact.
--------------------------------------
select c1.contact ,ca.enddate ,ch.ondate
from ca
join c1 on ca.accountno = c1.accountno
join ch on ca.accountno = ch.accountno
where ca.ondate in (select max(ondate) from ca group by accountno)
------------------------------------------
View 1 Replies
View Related
Jan 9, 2008
Hello All,
I have three tables A, B, and C.
What i want to do is basically left join A with B, then left join B with C.
However when try to do this it won't work out as i imagined.
Is there a better way (one that works) for accomplishing what i am trying to do?
Any help is much appreciated.
Thanks,
View 7 Replies
View Related
Jun 21, 2006
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
View 6 Replies
View Related
Apr 8, 2008
I have a table which represents a machine, it has two transducer heads. I have set a table representing the different transducers which will fit on to each of the heads. I want to join the trans one index to the transducer table and then join transtwo to the transducer table also. I've tried twice and keep getting a sql error on executing.
Oh, as extra trouble I want to put this into a stored procedure.
Here is the monster which now works without both transducers:
declare @wo integer
set @wo = 90001
SELECT w_o_tbl.w_o_id, flaw_tbl.flaw_id, w_o_tbl.class, w_o_tbl.quanity, flaw_tbl.part_serial, job_tbl.number_passed, job_tbl.number_failed, job_tbl.operator,
job_tbl.Reject, job_tbl.db_transfer, w_o_addem_tbl.spc_notes1, w_o_addem_tbl.spc_notes2, w_o_addem_tbl.expedite, cust_tbl.cust_name,
flaw_tbl.flaw_gain, flaw_tbl.backwall, flaw_tbl.length, flaw_tbl.depth, flaw_tbl.tech_init, job_tbl.alarm_lvl, job_tbl.filter, job_tbl.noise_lvl,
part_no_tbl.part_no, part_no_tbl.part_name, technique_tbl.tech_no, instrument_tbl.serial_no, instrument_tbl.model_no, instrument_tbl.manuf,
trans_tbl_1.serial_no AS Expr1, trans_tbl_1.manufacture, trans_tbl_1.trans_freq, trans_tbl_1.trans_size, technique_tbl.transduc_id2,
technique_tbl.water_path, technique_tbl.scan_speed, technique_tbl.ref_std_id1, technique_tbl.ref_std_id2, technique_tbl.ref_std_id3,
technique_tbl.ref_std_id4, technique_tbl.pulser, technique_tbl.freq, technique_tbl.range, technique_tbl.delay, technique_tbl.velocity,
technique_tbl.aeras_not_accble, technique_tbl.accpt_reject, technique_tbl.couplant, technique_tbl.entrance_angle, technique_tbl.sync,
technique_tbl.scan_index, technique_tbl.insp_id8, technique_tbl.insp_id7, technique_tbl.insp_type_id1, technique_tbl.thickness,
technique_tbl.insp_id6, technique_tbl.insp_id5, technique_tbl.insp_id4, technique_tbl.insp_id3, technique_tbl.insp_id2, technique_tbl.insp_id1,
technique_tbl.tech_rev, prod_form_tbl.prod_form
FROM w_o_tbl INNER JOIN
flaw_tbl ON w_o_tbl.w_o_id = flaw_tbl.w_o_id INNER JOIN
cust_tbl ON w_o_tbl.cust_id = cust_tbl.cust_id INNER JOIN
part_no_tbl ON w_o_tbl.part_id = part_no_tbl.part_id INNER JOIN
job_tbl ON w_o_tbl.w_o_id = job_tbl.w_o_id INNER JOIN
w_o_addem_tbl ON w_o_tbl.w_o_id = w_o_addem_tbl.w_o_id INNER JOIN
technique_tbl ON w_o_tbl.tech_id = technique_tbl.tech_id INNER JOIN
instrument_tbl ON technique_tbl.instru_id = instrument_tbl.instru_id INNER JOIN
trans_tbl AS trans_tbl_1 ON technique_tbl.transduc_id1 = trans_tbl_1.transduc_id INNER JOIN
prod_form_tbl ON technique_tbl.prod_form_id = prod_form_tbl.prod_form_id
WHERE (w_o_tbl.w_o_id = @wo)
View 7 Replies
View Related
Jul 25, 2007
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
View 1 Replies
View Related
Nov 9, 2005
Hello,I want to be able to view data from 3 tables using the JOIN statement, butI'm not sure of how to do it. I think i don't know the syntax of the joins.Iimagine this is easy for the experienced - but Im not.Allow me to explain:I have 2 Tables: PERSON and SIGNPERSON------PersonNo int (Primary Key)Name varchar(50)StarSign intFavFood intSIGN----StarSign int (Primary Key)StarSignName varchar(50)Relationship: SIGN has a one-to-many relationship with PERSON. The linkingfield is called 'StarSign'.Question 1:I want to display all the peoples names, and their star sign (whether theyhave one or not).Answer 1:SELECT PERSON.Name, SIGN.StarSignNameFROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;No problems there. But now I want to do the same thing, but have theirfavourite food displayed as well. So an additional table is needed:FOOD----FavFood int (Primary Key)FavFoodName varchar(50)Relationship: FOOD has a one-to-many relationship with PERSON. The linkingfield is called 'FavFood'.Question 2:I want to display all the peoples names, their star signs (whether theyhave one or not), and their favourite food (whether they have one or not).Answer 1:???I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALLthe rows from table PERSON will appear 'irrespective' of whether they haverelated records in the other tables.Jack.
View 3 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Jul 20, 2005
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View 5 Replies
View Related
Apr 27, 2015
I compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?
View 2 Replies
View Related
Jul 22, 2007
Hello All.
I am struggling with the below join block in my stored procedure.
I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs.
I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.
Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves
as the primary key.
I am guessing I am encountering this problem because of how
I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or
do you need more information?
Thanks for any tips.
Code:
select emailAddress from Users union
select user_name from PersonalPhotos union
select email_address from EditProfile union
select email_address from SavedSearches union
select distinct email_address from UserPrecedence union
select email_address from LastLogin) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address
View 8 Replies
View Related
Jan 7, 2014
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
View 1 Replies
View Related
Jan 2, 2006
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
View 3 Replies
View Related
Jun 3, 2006
Hi,
I was wondering if it would be possible to do a join on a grouped select.
SELECT Top 1 forumThreads.psRelTopId FROM forumThreads where forumThreads.psRelTopId > 0GROUP BY forumThreads.psRelTopId
This will return a specific psRelTopId and I would like to retrieve the psSubject column that is associated to that row.
How would I do a join in this scenario, am looking to see if it can be done in one step as opposed to a two step process. This is within a Stored Procedure of course.
View 4 Replies
View Related
Nov 12, 2005
I'm trying to set up a query in SQL and so far I have the following:
Code:
SELECT
CONTACT1.ACCOUNTNO, MAX(CONTACT1.COMPANY) AS Expr1, MAX(CONTSUPP.ACCOUNTNO) AS Expr2, MAX(CONTACT1.CONTACT) AS Expr3, MAX(CONTSUPP.CONTSUPREF) AS Expr4
FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTSUPP.CONTSUPREF <> 'PTG')
GROUP BY CONTACT1.ACCOUNTNO
ORDER BY CONTACT1.ACCOUNTNO
CONTACT1 will only ever contain ACCOUNTNO once, CONTSUPP on the other hand may have multiple instances of the same ACCOUNTNO. If CONTSUPREF contains 'PTG' in CONTSUPP for a particular ACCOUNTNO, I want the query to suppress the ACCOUNTNO altogether, but ONLY if 'PTG' is in CONTSUPP for that particular ACCOUNTNO. The problem I have at the minute is that I'm able to supress the lines from CONTSUPP where CONTSUPREF contains PTG, but it still returns one line for that ACCOUNTNO if there are any more.
In the attached example, I would like examples 2 and 3 to show but not example 1.
I hope that makes sense.
Thanks,
James
View 4 Replies
View Related
Apr 4, 2008
I feel like I'm a join away, here... I have tables for servers, databases, and backups. What I want to know is, what was the the most recent backup date and type for each database?/* Tables */CREATE TABLE [dbo].[Servers]( [ServerID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServerID] DEFAULT (newid()), [ServerName] [nvarchar](60) NOT NULL, CONSTRAINT [PK_Servers_ServerID] PRIMARY KEY CLUSTERED ([ServerID] ASC), CONSTRAINT [UNIDX_Servers_ServerName] UNIQUE NONCLUSTERED ([ServerName] ASC))GO CREATE TABLE [dbo].[Databases]( [DatabaseID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DatabaseID] DEFAULT (newid()), [ServerID] [uniqueidentifier] NOT NULL, [DatabaseName] [nvarchar](100) NOT NULL, [RecoveryModel] [nvarchar](11) NOT NULL, [OwnerUID] [nvarchar](30) NULL, CONSTRAINT [PK_Databases_DatabaseID] PRIMARY KEY CLUSTERED ([DatabaseID] ASC), CONSTRAINT [UNIDX_Databases_DBName_ServerID] UNIQUE NONCLUSTERED ([DatabaseName] ASC, [ServerID] ASC)) GO ALTER TABLE [dbo].[Databases] ADD CONSTRAINT [FK_Databases_Servers] FOREIGN KEY([ServerID])REFERENCES [dbo].[Servers] ([ServerID])GO CREATE TABLE [dbo].[Backups]( [BackupID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BackupID] DEFAULT (newid()), [DatabaseID] [uniqueidentifier] NOT NULL, [BackupDate] [datetime] NOT NULL, [BackupType] [char](4) NOT NULL, CONSTRAINT [PK_Backups_BackupID] PRIMARY KEY CLUSTERED ([BackupID] ASC)) GO ALTER TABLE [dbo].[Backups] ADD CONSTRAINT [FK_Backups_Databases] FOREIGN KEY ([DatabaseID])REFERENCES [dbo].[Databases] ([DatabaseID])GO /* Some Sample Data. */INSERT INTO [Servers]SELECT 'B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','AEPPROD03' GO INSERT INTO [Databases]SELECT '03FC0DF7-163E-45DC-A837-09B14F3740A4','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','msdb','SIMPLE','sa' UNION ALLSELECT 'DD0757B9-D1BE-4407-ABE0-24625CE65076','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','model','SIMPLE','sa' UNION ALLSELECT 'B9232B6F-3E09-480B-8658-4FE9ED2CC387','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','master','SIMPLE','sa' UNION ALLSELECT '9C5CC23D-72D8-40CC-B1B0-665CD5F27120','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_RedFlag','FULL','MM04519' UNION ALLSELECT '13055420-D791-4802-B75E-ADAB4C022BE7','B50EF4C5-2751-49CF-88D9-1BDAC5F5913B','pn_ProductionData','BULK_LOGGED','M M04519'GO INSERT INTO BackupsSELECT '94539C6E-C459-42B1-928C-02E94BA2D230','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 2:04AM','Tran' UNION ALLSELECT '24CE98B1-C737-46EF-92D2-048309DF192B','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 8:04PM','Tran' UNION ALLSELECT '6521DA25-BB6C-4407-964A-09BBEAB8978D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 8:04AM','Tran' UNION ALLSELECT 'B0CB6502-F022-4F46-9994-177BB61E082F','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 12:04PM','Tran' UNION ALLSELECT '69C7078B-F5C3-4805-815C-1D3F6450628E','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 6:04AM','Tran' UNION ALLSELECT '8C6D591E-8633-409C-8585-231B3C0920FC','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 8:04AM','Tran' UNION ALLSELECT 'A7216995-BC83-48CF-B5E6-27EFF2E8A841','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 6:04PM','Tran' UNION ALLSELECT 'A2537957-E601-4BCE-A23B-317021219BA0','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 8:04PM','Tran' UNION ALLSELECT '4A23B5C6-65A4-483D-8F70-32B2D459769A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 4:04PM','Tran' UNION ALLSELECT '8812B10D-B2F0-48A1-ACEF-451FC1572D18','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr 4 2008 2:40AM','Full' UNION ALLSELECT '60C80D58-5FDC-41E4-A529-4CBA6A04B4EA','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 2:04PM','Tran' UNION ALLSELECT 'B7D4F3FF-DDC9-4BD3-8AE8-4CD9C4C31375','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 2:04AM','Tran' UNION ALLSELECT '1A2C3054-5563-465C-9DF8-53A154DB4DF1','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 12:04PM','Tran' UNION ALLSELECT '90C4F821-3251-482D-B103-566F7AA72CBE','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 6:04PM','Tran' UNION ALLSELECT '53F9773E-9F09-44E8-9BC8-6F253D66106C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 2:04PM','Tran' UNION ALLSELECT 'F60C2566-44C4-495E-80BA-76831048451D','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 10:04PM','Tran' UNION ALLSELECT 'FBF29499-D4ED-4AB0-BBE8-76FFA3CC6737','03FC0DF7-163E-45DC-A837-09B14F3740A4','Apr 3 2008 2:40AM','Full' UNION ALLSELECT '88C1F74E-CB0E-4CEC-9720-8D47558AAC5A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 12:04AM','Tran' UNION ALLSELECT '45DD22A0-16A0-4BEE-942E-8FA27DC52C31','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr 4 2008 2:10AM','Full' UNION ALLSELECT 'F9F2EEB2-EFAD-4229-994C-9F175269AF76','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 10:04AM','Tran' UNION ALLSELECT 'FE754924-E31A-43BC-8BD3-B3CA0EA02633','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 10:04AM','Tran' UNION ALLSELECT '8C692625-B071-4A12-A620-B7A4A56AC1B7','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 4:04AM','Tran' UNION ALLSELECT '62FBC73D-49C3-4572-A017-C025ACBF0948','13055420-D791-4802-B75E-ADAB4C022BE7','Apr 3 2008 1:21AM','Diff' UNION ALLSELECT '938DF985-C0E4-41E7-9510-C02FFE06F186','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 10:04PM','Tran' UNION ALLSELECT 'F19BD5F6-0FD4-4BD2-91DA-D03CF9719124','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 3:04AM','Full' UNION ALLSELECT 'D7B39FE1-B4BF-4AA0-9D3F-D0A363BFB757','13055420-D791-4802-B75E-ADAB4C022BE7','Apr 4 2008 1:20AM','Diff' UNION ALLSELECT '500BC0C0-0B7D-4D65-B635-D51F89DCA726','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 4 2008 4:04AM','Tran' UNION ALLSELECT '943474EF-2AE1-49B5-9B8F-DA42630EB195','B9232B6F-3E09-480B-8658-4FE9ED2CC387','Apr 3 2008 2:10AM','Full' UNION ALLSELECT 'CA58E9A5-155B-404A-B57D-DA902A2A1202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 6:04AM','Tran' UNION ALLSELECT 'F2F58383-1E7B-4191-BB89-E99E63B08202','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 12:04AM','Tran' UNION ALLSELECT '985F479B-4C11-4C6D-AE7E-F44B3426DF7A','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 3 2008 3:04AM','Full' UNION ALLSELECT '966B6917-C284-46CA-8664-9DFBEF222BEF','13055420-D791-4802-B75E-ADAB4C022BE7','Mar 30 2008 1:32AM','Full' UNION ALLSELECT 'A588C63F-3887-4B4E-9AD3-F668BEB4194C','9C5CC23D-72D8-40CC-B1B0-665CD5F27120','Apr 2 2008 4:04PM','Tran'GO /* The query so far */SELECT CASE WHEN DATEADD(dd,-2,getdate()) > MAX(B.BackupDate) THEN 'OMFG' WHEN DATEADD(dd,-1,getdate()) > MAX(B.BackupDate) THEN 'wtf?' ELSE 'k' END AS BackupStatus, S.ServerName, D.DatabaseName, MAX(B.BackupDate) AS BackupDate, B.BackupTypeFROM Backups BINNER JOIN Databases D ON B.DatabaseID = D.DatabaseIDINNER JOIN Servers S ON D.ServerID = S.ServerIDGROUP BY S.ServerName, D.DatabaseName, B.BackupTypeORDER BY S.ServerName, D.DatabaseNameGO /* Current Output */-- BackupStatus ServerName DatabaseName BackupDate BackupType-- ------------ ------------- ------------------- ----------------------- ------------ k AEPPROD03 master 2008-04-04 02:10:00.000 Full-- k AEPPROD03 msdb 2008-04-04 02:40:00.000 Full-- k AEPPROD03 pn_ProductionData 2008-04-04 01:20:00.000 Diff-- OMFG AEPPROD03 pn_ProductionData 2008-03-30 01:32:00.000 Full-- k AEPPROD03 pn_RedFlag 2008-04-04 03:04:00.000 Full-- k AEPPROD03 pn_RedFlag 2008-04-04 06:04:00.000 Tran /* Desired output */-- BackupStatus ServerName DatabaseName BackupDate BackupType-- ------------ ------------- ------------------- ----------------------- ------------ k AEPPROD03 master 2008-04-04 02:10:00.000 Full-- k AEPPROD03 msdb 2008-04-04 02:40:00.000 Full-- k AEPPROD03 pn_ProductionData 2008-04-04 01:20:00.000 Diff-- k AEPPROD03 pn_RedFlag 2008-04-04 06:04:00.000 Tran Any help is appreciated.Thanks.-D.
View 8 Replies
View Related
Mar 27, 2004
Hey guys,
I need to perform a query on two tables that look like this:
ATTENDANCE
attendanceid
memberid
meetingid
meetingdate
MEMBERS
memberid
firstname
lastaname
I can run this query just fine:
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid
This gives me memberid's for members that are present.
I need members that are not present.
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid
This returns a thousand rows when it should return no more than 25.
I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)
Thanks,
Mike
View 2 Replies
View Related
Oct 9, 2006
hi,consider this:i have american users in a db called "user" in a server called AMERICAand rest of the world users in a db called "user" in a server called OTHERhow can i do a join betwhen American's users and Rest of the word's User db?
View 3 Replies
View Related
Apr 8, 1999
Is it possible to create a join between two SQL Server DB's on the same server? What is the syntax?
View 1 Replies
View Related
Nov 20, 2007
Hi,
Why does the below not return any results???
Colorcodes mark the related keys.
Thanks.
Tables:
FundClient (ClientID PK, Client)
FundPortfolio (PortfolioID PK, Portfolio, ClientID FK)
Staff(StaffID PK, SeniorMgr, ClientID FK, FundID FK)
myLegal(myID PK, LegalCounsel FK, ClientID FK, FundID FK)
FullLegalList(LegalID PK, LegalName)
Code Block
@LegalCounsel int = 0,
@ClientID int = 0,
@FundID int = 0
DECLARE @thisQuery as varchar(max)
SET @thisQuery = 'SELECT p.Portfolio, SeniorMgr, fl.Legal FROM FundClient f'
BEGIN
SET @thisQuery = @thisQuery + ' INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID'
END
BEGIN
SET @thisQuery = @thisQuery + ' LEFT OUTER JOIN Staff s
ON (s.ClientID = p.ClientID AND s.FundID = p.PortfolioID AND s.ClientID = f.ClientID)'
END
BEGIN
SET @thisQuery = @thisQuery + ' LEFT OUTER JOIN myLegal l
ON (l.ClientID = p.ClientID AND l.FundID = p.PortfolioID)
INNER JOIN FullLegalList fl
ON fl.LegalID = l.LegalCounsel'
END
BEGIN
IF @Legal != 0
SET @thisQuery3 = @thisQuery3 + ' WHERE rl.Legal = ' + cast(@LegalCounsel as varchar(11))
END
BEGIN
IF @ClientID != 0
SET @thisQuery = @thisQuery + ' AND p.ClientID = ' + cast(@ClientID as varchar(11))
END
BEGIN
IF @FundID != 0
SET @thisQuery = @thisQuery + ' AND p.PortfolioID = ' + cast(@FundID as varchar(11))
END
BEGIN
SET @thisQuery = @thisQuery + ';'
END
View 12 Replies
View Related
Jul 30, 2007
I want to do a Query in Table ZT_TransFmt9_Headerto join with other Table and then get the value of KeepMonth from ZT_DataBackupbut Table ZT_TransFmt9_Header is far from away ZT_DataBackupit seems have to run a complicated SQL Query to reach goal..
select BusinessCode from ZT_BillerInfo A, ZT_TransFmt9_Header ins where A.JihsunCode=ins.StoreCode
-----------ZT_TransFmt9_Header to join with ZT_BillerInfo for get BusinessCode
when I get BusinessCode , next I need put BusinessCode in where clause to select data from zt_billerinfo ( Table zt_billerinfo have a column named BusinessCode which can mapping with the BusinessCode I have just select from ZT_BillerInfo and ZT_TransFmt9_Header and then join with Table zt_biller get value of CompanyCode, and then use company code in Table Databackup to get the keepmonth
* I know my descrition may cause you feel confused. I past a Table picture herehttp://picasaweb.google.com.tw/jovi.fat/TAbleRelation/photo#5092934117841944082
View 1 Replies
View Related
Feb 1, 2008
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View 2 Replies
View Related
Dec 16, 2004
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
View 2 Replies
View Related
May 5, 2008
Hi...
I have 3 tables:SportTeams (TeamID, TeamName)SportAthletes (TeamID, AthleteID, AthleteName)SportMedals (AthleteID, Medal)
I want to have a brief medal list (TeamID, G, S, B). I can write query in systax:
Select a.TeamID, a.TeamName_en,g.G,s.S,b.B from SportTeams aLeft Join(Select c.TeamID,Count(*) as G from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'G'Group By c.TeamID) g On a.TeamID = g.TeamIDLeft Join(Select c.TeamID,Count(*) as S from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'S'Group By c.TeamID) s On a.TeamID = s.TeamIDLeft Join(Select c.TeamID,Count(*) as B from SportMedals d Inner Join SportAthletes c On d.AthleteID = c.AthleteIDWhere Medal = 'B'Group By c.TeamID) b On a.TeamID = b.TeamIDOrder By g.G desc, s.S desc, b.B desc, a.TeamID asc
But I can't write it in LINQ syntax (I am beginner with C#, LINQ)
Can you help this sample to LINQ systax?
Thanks!
View 5 Replies
View Related
Jul 19, 2015
writing a cross join query with one table:
Cities(City_name, X_coordinate, Y_coordinate)
the result should be all combinations without reverse column returns
SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???
for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)
View 8 Replies
View Related
Jul 22, 2014
I'm writing a query where I have multiple left-outer joins but I keep getting multi-part identifier error. See the query below?
SELECT gl.seg5 Natural
,gl.seg2 Office
,gl.seg3 Dept
,gl.seg4 Team
,gl.seg6 Sub
,gl.seg7 Tkpr
,gl.seg1 Comp
,'CHK' Source
[Code] ....
Errors
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.baid" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.cknum" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.ckline" could not be bound.
View 5 Replies
View Related
Nov 6, 2006
Hello,
i am facing a bizarre problem, accessing data from a remote server which has been linked to my sql.
My SQL server is 2005 and the remote server is SQL 2000.
i have linked the remote server (called LinkedServer) so that when i run this query:
SELECT * FROM LinkedServer.SomeDB.dbo.SomeTable
executes successfully! However, when i run a similar query like this:
SELECT Column1 AS Col1,
LinkedServer.SomeDB.dbo.SomeTable.Column2 as Col2,
Alias.Column3 as Col3
FROM LinkedServer.SomeDB.dbo.SomeTable
INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable1 ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = LinkedServer.SomeDB.dbo.SomeOtherTable1.Column3
INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable2 AS Alias
ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = Alias.Column3
It gives me this error:
The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column2" could not be bound.
The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column3" could not be bound.
I have noticed that this error is generated only for the selected columns whose path has either been repeated (e.g. "LinkedServer.SomeDB.dbo.SomeTable.Column2" as opposed to "Column2") or for columns which are from aliased tables.
What is going on?!?!?!??!?!?!
Any Help would be tremendously appreciated!!!! 8..)
View 8 Replies
View Related
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 Replies
View Related
Dec 23, 2014
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
View 9 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
Jan 10, 2007
I have two tables:
tblUserDataUserNameUserCode
tblBlogsUserCodeBlogText
I have an SP which takes the username as a variable.
How can I select all blogtext from tblBlogs where the usercode belonging to the username in tblUserdata is equal to the usercode in tblBlogs?
so select all blogs for a specfic username...
View 1 Replies
View Related
Feb 17, 2007
I have table A:ID intName textAnd Table BID intName text Now, I want to select all records from A where there is no matching record in B based on the IDI want to do this with a JOIN statement and not a subquery as I understood that the execution plan for JOIN statements is more efficient...Any help?
View 2 Replies
View Related
