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?
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
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.
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
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
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
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
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:
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)
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?
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!
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.
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' :
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)
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
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.
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
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)
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.
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
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
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'.
have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but failed miserably.
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.
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
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";
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
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.
I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables
Main table
Label table
textbox table The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .
MAIN Table
pk Moduleid ItemName itemtype
36 372 test1 4
37 372 test2 4
38 372 test3 4
39 372 test4 6
40 372 test5 4
label
pk Main_fk labeltext
4 36 labeltext1
5 37 labeltext2
6 38 labeltext3
7 40 labeltext4
Textbox
pk Main_fk textboxtext
1 39 textbox1
I did infact manage to perform a join on these these tables.
Select * From tb_Main
inner join tb_Label
on tb_Main.pk = tb_Label.main_fk
where moduleID = @moduleID
Select * From tb_Main
inner join tb_textbox
on tb_Main.pk = tb_textbox.main_fk
where moduleID = @moduleID
The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.
Is what im asking possible? I would appreciate if some exmaples are posted
I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!
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.
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'
