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.
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
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.
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.
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?
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'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
Is there a way w.out using TSQL to filter the "ID" number which has all three "Code" of X1,X2, X3 associated with it. In the above example I would only want "ID" AAAAA and CCCCC returned. Thanks in Advance
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 am trying to filter my columns on multiple values. I need them all to be OR because I want it to look through all of the columns and wherever the value matches to not include in the view. My WHERE clause that I thought would work looked like this..
WHERE (NOT (RTPL_VOLUME_DATA_1.SYMBOL LIKE '%spot%')) AND (NOT (RTPL_VOLUME_DATA_1.ISSPREAD = 'True')) AND (NOT (RTPL_VOLUME_DATA_1.GMIPRODUCTCODE = 'Internal')) AND (RTPL_VOLUME_DATA_1.TAG_COMMENT IN ('[]', '[Gscalp]', '[TT]', '[GX2]', '[NA]', '[STELLAR]')) OR (RTPL_VOLUME_DATA_1.TAG_COMMENT IS NULL)
However this does not work and provide the data needed. I then thought that if I replaced all the AND's with OR's that would work, but here it does not filter anything.. not sure where to go from here.
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'
one company can have multiple shareholders and directors records.
i create a search query where users might search by company name, secretary name , shareholder name or directors name. My select query is like below:
Code:
SELECT dsf.dsf_id, dsf.company_name, dsf.incorporation_date, dsf.secretary_name, s.shareholders_name, d.directors_name FROM tbl_dsf dsf LEFT OUTER JOIN tbl_directors d on dsf.dsf_id = d.dsf_id LEFT OUTER JOIN tbl_shareholders s on dsf.dsf_id = s.dsf_id [WHERE CONDITION]
The result for above query would be like:
Code:
abc | 1/2/1999 | william | marry | donna abc | 1/2/1999 | william | jenna | donna abc | 1/2/1999 | william | jolly | donna abc | 1/2/1999 | william | marry | dolly abc | 1/2/1999 | william | jenna | dolly abc | 1/2/1999 | william | jolly | dolly
Is it possible to achive result as below:
Code:
abc | 1/2/1999 | william | marry,jenna,jolly | donna,dolly
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
i didnt think my sql qeury was that complicated that it would crash my webapp. all im trying to do is filter data between two tables. heres my query<cfquery name="GetResults" datasource="#datasource#">SELECT *FROM Content, Content_SitesWHERE Content.ContentID <> Content_Sites.ContentIDORDER BY Content.ContentID DESC</cfquery>equals works, but when i try not equals, it all goes haywire. any ideas?TIA
