Going To Tables In Different Branches With One Query
Dec 1, 2004
Hi,
I have five tables p,pm,m,i and bu. Tables 'pm' and 'm' are a branch of table 'p', and table 'bu' is another branch of table 'p'. I know how to run a query to get through the first four tables 'p','pm','m' and 'i' in the manner:
", i.name, m.milestone_leader, m.mgr_opinion FROM PROJECT p INNER JOIN PROJECT_MILESTONE pm ON p.id = pm.source_id INNER " & _
"JOIN MILESTONE m ON pm.related_id = m.id INNER JOIN [IDENTITY] i ON m.milestone_leader = i.id WHERE m.mgr_opinion='75' " & _
"order by p.project_location, p.project_lead_center"
But when i find an item where m.mgr_opinion is equal to 75 i don't know how to include in the query the item that belongs to the table 'bu', because it is in a different branch.
Okie, this one has me a little stumped so bear with me an I will explain as best I can....
I have a family tree database...
The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).
Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.
If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.
I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.
Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.
Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
hi,I have two tables, t1 t2col1 col1 col2 1 1 0 2 1 0 3 1 1 4 2 0 5 2 0 6 3 1 7 3 1 8 4 1 9 4 1 10 4 1 11 4 1t2.col1 is the key from t1.col1I want to retrieve all t1.col1 records which equal to t2.col1 and tb2.col2 has ONLY "1"the result should be: 3, 4I try:select tb1.col1 from t1 as tb1 where tb1.col1 in (select col1 from t2 where col1=tb1.col1 and col2=1 and ...???.)any help?
Hello all, If I write this query :SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandes the return is good.
But If I the query is :SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, Commandes I have an error "Invalid column name.
The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table. How to make a good query with an aliased column without error ?
I have two tables: 1 with data, the other 3 columns from the first table with rows filled with data that needs to be ommitted when a query is made on the first table.
For example, say I have a column named "meat" and there are rows in it with "beef" as the data. In the other table there is also a column with meat and beef as the data. I would need to query both tables to first get the data to be ommitted and then use that result to query the first table. I hope I'm making sense, any help would be appreciated
I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)
To get the data I need - I need to query three tables.
Stockitem - This has the column "itemid" Stockitemmemo - This has the column "itemid" and "timeanddatecreated" Binitem - This has the column "itemid" and "binname"
My results must be based on the following criteria......
All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.
I do have two questions based on the above....
1. Does it make sense what I need? 2. Is it possible
I want to get the list of tables which an SQL query is using. It could be through some query or parser written in jruby or R or any other language,any thrd party tool(freeware)or any scripts.
Hi, I know it is simple query but I am confused. I need to make a query from 3 tables in the same database. There are no relations between these tables. Here is result I need. KE LE JE 15 775.5 398 18 192.23 399
I'm doing a query from two tables. the table "pr" and the table "prre" with the following data: pr.no, pr.nome, pr.data, pr.recibo, pr.ettsuj, pr.ettdesc, prre.cr pr.eliquido, prre.rqtt , prre.ervu, prre.ere
I'm doing the following query:
select cast(pr.no as varchar) as 'Numero',pr.nome as 'Nome',pr.data as 'Data',cast(pr.recibo as varchar) as 'Recibo', cast(prre.rqtt as integer) as 'Qt Dias', prre.ervu as 'valor unitario',prre.ere as 'Total Subsidio', pr.ettsuj as 'Total iliquido', pr.ettdesc as 'Total Descontos', pr.eliquido as 'Total Liquido' from pr inner join prre on prre.prstamp=pr.prstamp
for this query is a list of salaries, with the fields, official number, expiration, name, date, receipt number, net total, the total gross, days subsidy amount, subsisidio.
from bB_posts as t1 join bB_comments as t2 on t2.postid = t1.postid (or t1.commentcount = 0)
This query prints out all the posts which have 1 or more complementary comments. But i would aslo like to print the posts which have 0 comments (seen in brackets).
i have 2 tables in a database. Table A contains: State City Employee_firstEmployee_LastAge KarnatakaBangaloreDavid Na 23 KarnatakaBangalorePrakash Na 25 KarnatakaMysore Naina Na 26 KarnatakaMysore David Na 35 MaharashtraMumbai Parneet Na 24 MaharashtraMumbai Vikas Na 33 MaharashtraPune Amit Na 25 MaharashtraPune Amit Na 19
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL , [OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OpportunityTypeID] [numeric](10, 0) NOT NULL , [SLABased] [int] NOT NULL , [LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateCreated] [datetime] NOT NULL , [AccountID] [int] NOT NULL , [GeographyID] [int] NOT NULL , [VerticalID] [int] NOT NULL , [BDMID] [int] NOT NULL , [Probability] [int] NOT NULL , [PASStatus] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL
and SKILL [SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [OpportunityID] [numeric](18, 0) NOT NULL , [OrderId] [numeric](18, 0) NOT NULL , [PracticeID] [int] NULL , [SkillID] [int] NOT NULL , [NoOfPeople] [int] NOT NULL , [Clientinterview] [int] NOT NULL , [Location] [int] NOT NULL , [JDAttached] [int] NOT NULL , [JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Status] [int] NULL , [Experience] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL , [GeoLocation] [int] NULL )
i want to make a stored procedure for custom search on these two tables with the following fields given to the user as an option to make his choice.. from opportunity table - OpportunityTypeID,SLABased,AccountID , GeographyID, VerticalID, BDMID, Probability
and from skill table SkillID, Location, GeoLocation
Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.
I have 3 tables Clients <-oo ClientContacts oo-> Contacts (the <-oo means one to may relation between the tables)
A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.
The result should return values for Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category, and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.
I tryed an INNER JOIN but it will return juts the records having contact information.
I have three tables in my db. One is clients, other calls, and other visits. I want to get all the calls and visits of all clients in my db every row in each table separate in one row in the results table. How can I do it?
is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.
Can anybody tell me how to use the query analyzer to connect to 2 different servers in one query? If I access a database on another server like that [servername].[dbname].[dbo].[tablename] I recieve an error that says: Server servername is not configured for DATA ACCESS.
I am trying to write a query to compare the same column in each table with "not equal" expression.
My query is like this:
select tableOne.empl_ser_no from tableOne, tableTwo where tableOnel.empl_ser_no <>(select empl_ser_no from tableTwo)
I am getting the following message from SQL Server:
Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression. Command has been aborted.
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
I know how to join 2 tables, but I have a third I need to insert. For some reason, this doesn't work:
Code:
$rows = ff_select( "select ". "u.email as email, ". "c.user_id as user_id, ". "u.name as name, ". "r.age as age ". "from #__comprofiler as c ". "left join #__users as u on c.user_id = u.id ". "left join #__rnr_contest as r on c.user_id = r.userid ". "where (r.age != chicken) and (r.age != nystrip) and (r.age != regrets) and (u.block = 0) and (c.cb_contactmethod LIKE '%Email%') and (u.usertype != 'Super Administrator') and (u.email != 'please@change.com') and (u.username != 'guest') and (u.username != 'piedmont') ". "order by email"
anyone see why? It tells me that "chicken" is not a column which is weird because I don't think it's listed as a column in my query... is it?
I've run into an interesting challenge, and haven't figured out how to solve it yet. I'm fairly novice at MS SQL, and I would assume that there is a relatively simple/elegant solution. Here's what's going on:
I'm tracking projects that have a one to many relationship with sub-project type. There can be many entries for any given project and sub-project type. Each entry has a status.
EX: "Datalist"
ID Proj Sub-Proj Status 1 Alpha Review 1 2 Alpha Test 3 3 Alpha Review 2 4 Alpha Review 2 5 Alpha Test 4
In addition to the Datalist above, I've got a table which contains all valid combinations of Projects and Sub-Projects.
EX: "ValidCombos"
ID Proj Sub-Proj 1 Alpha Review 2 Alpha Test 3 Beta Review 4 Beta Rewrite 5 Beta Test
I need to make a query/table which contains the total number of IDs that have a given Project, Sub-Project, and Status.
EX using data from the tables above: "DesiredTable"
I have 4 temporary tables that hold criteria selected through a report wizard. I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.
Would somebody be kind enough to help me out please.
To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:
(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row. (2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table. (3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.
I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.
SELECT * FROM tblActualWork [ActualWork] JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)
Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.
Please help, not having much joy with this. Many thanks.
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
I'm running a query to show the transfer fees spent by a club with data from two tables :
Code: SELECT SUM([Transfer Fee]) From [Summer_2001_2011] WHERE [New Club] LIKE 'Manchester City' Union All SELECT SUM([Transfer Fee]) From [Winter_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Shows the sum from each table :
Code: 545.01 110.98
What do I need to add to the query to show the total from both tables?
I have 2 tables that are joined together by a primary key (Order Number). Can I use one SQL query to delete from both of the tables. One table contains the order information from a client (Order Number, Customer Name etc). The other table has order information (Order Number, Item Number, Quantity Ordered etc.)
I need one statement that will allow me to remove the items from both tables. Can this be done.
I currently have a query that uses three tables [Table1, Table2, Table3]. Each table only has two columns: Group and something else. I want a query that gives me the group and those three something else.
Table1: Group and Info1 Table2: Group and Info2 Table3: Group and Info3 Query: Group, Info1, Info2, Info3
The groups never repeat in a single table.
However, the groups are not identical in every table. I want the groups to never repeat twice: All the groups should appear if they are there at least once in one of the table, and provide Info1, Info2, Info3 (blank if it's not there in its table, filled if it is).
That should be a fairly simple query, I just can't seem to make it work so groups don't repeat.
Little visual example.
Table1: Group - Info1 1 - a 2 - b 4 - c 5 - d
Table2: Group - Info2 1- aa 3- bb 4- cc
Table3: Group - Info3
5- aaa
The query would give me this:
1 - a - aa - "" 2 - b - "" - "" 3 - "" - bb - "" 4 - c - cc - "" 5 - d - "" - aaa
I wrote this SQL Query based in several tutorial on the net:
"SELECT c.CustomerName, sh.IncidentDesc, sh.IncidentTechInCharge, sih.IncidentChangeTo, sih.IncidentChangeDate FROM Customer c INNER JOIN SupportHistory sh ON c.CustomerCode = 'sh.IncidentCustomerCode' INNER JOIN SupportIncidentHistory sih ON sh.IncidentID = sih.IncidentID"
