create view myidtitlenew
as
SELECT c1.messageid_,
c1.title_, c1.list_,
c1.created_
FROM outmail_ c1
inner join (Select max(messageid_) as messageid_, max(title_) as title_
FROM outmail_ c1 where list_ = 'copd'
GROUP BY substring([title_],1,charindex(' ',[title_])-1)
) c2
ON c1.messageid_ = c2.messageid_
go
create view mymembersum
as
select distinct list_, membertype_ , count(*) as cnt
from Members_ where list_='copd'
group by membertype_,list_
go
Hi, I am new to sql and I am trying to join 2 views using MySQL Query Browser. Here is my query and the issues I am having.
SELECT book_month, region_book, product_group as base_prod_group, cnt as base_cnt FROM collections.v_nonpay_trend_base a join collections.v_nonpay_trend_npdata b on (a.book_month=b.disc_check_in_month and a.region_book=b.region and a.base_prod_group=b.product_group)
Error - "Column 'product_group' in field list is ambiguous" and "Column 'cnt' in field list is ambiguous"
Both views have columns named 'product_group' and 'cnt'
Can someone please let me know what I am doing wrong?
I have 2 views that I can join to give me the recordset I want. Both views are currently filtered on a particular column (ie 'WHERE colName = myValue')
The problem is that I want to use this from a web page with user input in which the user would specify myValue.
Is there any alternative to having two views? Can I combine them into one SQL statement? Access allowed me to do it by specifying an alias for a select statement, then joining this to another. I'm not sure if that makes sense, but I guess what I want to do it specify one view then another, then join them - all in the same statement!
Something like this:
Code:
SELECT view1.*, view2.* FROM ((SELECT tbl1.*,tbl2.*,tbl3.* FROM tbl1 JOIN tbl2 ON tbl1.id = tbl2.tbl1_id JOIN tbl3 ON tbl2.id = tbl3.tbl2_id AND tbl3.id = myValue) AS view1) ((SELECT tbl4.* FROM tbl4 WHERE tbl4.id = myValue) AS view2) RIGHT JOIN view1 ON view2....
Hi guys,I detected a strange behaviour when doing an inner join of two views.There is table_Objects, table_Contracts and view_Objects, View_Contracts.Table_Objects: ID, PriceView_Objects: Select * from Table_Objects Where Price > 10Table_Contracts: ID, YearView_Contracts: Select c.ID, c.YearFrom Table_Contracts c Join View_Objects o on c.id=o.idQuiete simple so far, but it doesn't work. Imagine Object with id 3 that is shown in View_Objects and Table_Contracts but not shown in View_Contracts. How is that possible?EDIT: Sometimes its shown, sometimes notThx for your reply!
Ok, What I want to achieve is plain stuff, I want to join 2 views on a common key.It all works well with the SQL2000 Query Analyzer, but not trough ADO.NET or should I say my webapplication.With that I mean that my query return rows when executed from SQL2000 Query Analyzer, But not when used in my application or Executed from the Visual Studio Server Explorer.I have struggled with this one for several hours, I cant get this one right.So lets bring in the one who actually know what his doing View1: 1 select 2 cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2)) AS Rabatt 3 ,cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2)) AS MVA 4 ,cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2)) AS Belop 5 ,PS.* 6 ,K.Kunde_ID 7 FROM 8 tbl_ProduktSalg AS PS 9 INNER JOIN 10 tbl_Ordre AS O 11 ON 12 O.Ordre_ID = PS.Ordre_ID 13 INNER JOIN 14 tbl_Kunde AS K 15 ON 16 K.Kunde_ID = O.Kunde_IDView2: 1 SELECT 2 PS.Ordre_ID 3 ,SUM(cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalRabatt 4 ,SUM(cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2))) AS TotalBelop 5 ,SUM(PS.Pris) AS TotalPris 6 ,SUM(cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalMVA 7 FROM 8 tbl_ProduktSalg AS PS 9 GROUP BY 10 PS.Ordre_ID MyQuery/SPRC: 1 create procedure %PROC% (@Kunde_ID int, @Ordre_ID int) 2 as 3 begin 4 SELECT 5 v_PSD.* 6 ,v_OTS.TotalRabatt 7 ,v_OTS.TotalBelop 8 ,v_OTS.TotalPris 9 ,v_OTS.TotalMVA 10 FROM 11 v_ProduktSalgDetaljer AS v_PSD 12 INNER JOIN 13 v_OrdreTotalSum AS v_OTS 14 ON 15 v_OTS.Ordre_ID = v_PSD.Ordre_ID 16 WHERE 17 v_PSD.Kunde_ID = @Kunde_ID 18 AND 19 v_PSD.Ordre_ID = @Ordre_ID 20 21 end 22
Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.
A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.
I am new to programming trying hard to learn. I made a code for Views in mssql for the 2 tables below:I am wondering if I could make it 3, 4 or more tables at the same time. How can incorporate in the following codes I made.
Create View vwEmployeeInfoByGender as Select FirstName, MiddleName, FamilyName, Gender, BirthDate, MaritalStatus from tblEmployeeInfo join tblGender on tblEmployeeInfo.GenderID = tblGender.GenderID
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
I want to make some joining in my table but I dont know what kind of joining is appropriate. Here is my tables sample:table1 -> tableid, useridtable2 -> userid, firstname, lastnametable3 -> userid, firstname, lastname Now, I want to to get data from table1 with the data from table2 and table3 where userid of table1 = table2, table3.
Hi. I'm new to SQL, and need to join 2 tables... any hints??? table1:id (int)title(varchar(50))body(text) table2:id (int)title(varchar(50))body(text) somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information: table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" I would like to get... id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body" Does anyone know how to get this? I am fairly flexible if i need to change things... cheers, eh!
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
Hello all, I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do??????? Mahmoudona
Hello I have two queries that I would like help joining: Query #1SELECT UserName, (SELECT COUNT(*) AS Expr1 FROM Jokester WHERE (InvitedBy = aspnet_Users.UserName)) AS invsFROM aspnet_Users Query #2 SELECT UserName, SUM(Hits) AS HitsFROM (SELECT UserName, (SELECT COUNT(*) AS Cnt1 FROM (SELECT COUNT(*) AS Cnt2 FROM Hits WHERE (JokeId = j.JokeId) GROUP BY UserId) AS T1) AS Hits, (SELECT AVG(CAST(Rating AS numeric(12, 2))) AS Rating1 FROM Ratings WHERE (JokeId = j.JokeId)) AS Rtng FROM Jokes AS j) AS t2WHERE (Hits >= 1) AND (Rtng >= 3)GROUP BY UserName They both return correct values, I just want to join them on UserName Thank you, Louis
I've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:
-A users Table -A Posts Table -A Replies Table.
Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.
1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.
I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.
Right now I have two Queries that I've created: What I'm wanting to do is graft everything from Query1 onto Query2 but only where Query1.UserID = Query2.UserID . Any suggestions on how I would go about doing this?
--Query 1 ( SELECT PC1.ID, PC1.UserID, PC1.ModuleID, PC1.ModifiedDate FROM Projex2_0_0_Cores PC1 WHERE PC1.ModuleID = 369 )
--Query 2 ( SELECT PC2.UserID, MAX(PC2.CreatedDate) as CreatedDate FROM Projex2_0_0_Cores PC2 WHERE PC2.ModuleID = 369 GROUP BY PC2.UserID, PC2.ModuleID )
I am using MS SQL Server 2005 on Windows XP with SQL Server Management Studio Express CTP. I am having issues with my query on joining 2 tables I created using BETWEEN to restrict the Salary. Table 1 is called Employee and Table 2 is called Job_title. The column Job_title_code is the only column that is in both tables which is how I am joining both tables. Here is my SQL query:
Code:
SELECT Employee.*, Job_title.*
From Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code
WHERE Salary
BETWEEN 50000 AND 500000;
The results I am getting back are:
Msg 207, Level 16, State 1, Line 7 Invalid column name 'Job_title_code'.
I can't figure out how to fix this error. I feel like I have tried everything, so any help will be much appreciated. Thank you.
Hi, I have a table with fields as partnerid, contractno. The partnerid field has the Id number which can be a supplier or a customer. I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.
Data in table PId ContractNo 20045 1567 435 1567 123 1567 345 1678 1004 1678
I need to display the data in the following format.
jack 20 Melbourne AAA Nick 30 Bendigo BBB Russ 28 Sydney AAA Marty 31 Perth AAA
Table 3
name age city Position
jack 20 Melbourne Manager Nick 30 Bendigo Manager Russ 28 Sydney Clerk Marty 31 Perth Manager
Table 4
name age city datejoined
jack 20 Melbourne 09-09-2001 Nick 30 Bendigo 08-05-2001 Russ 28 Sydney 10-12-2000 Marty 31 Perth 11-11-1999
I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.
so the result should be
name age city position datejoined
jack 20 Melbourne Manager 09-09-2001 Nick 30 Bendigo Manager 08-05-2001 Russ 28 Sydney null null
my query
SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined FROM Table1 a, Table2 b, Table3 c, Table4 d WHERE a.age=b.age and a.name=b.name and a.city=b.city and b.age*=c.age and b.name*=c.name and b.city*=c.city and b.position='Manager' and b.age*=d.age and b.name*=d.name and b.city*=d.city
THE RESULT IS
jack 20 Melbourne Manager 09-09-2001 Nick 30 Bendigo Manager 08-05-2001 Russ 28 Sydney null 10-12-2000
When I try to join table4 with table i am getting a exception
Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN
I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.
The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,i.e., they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.
Companies_Addresses id (PK) companyID (FK) companyDetailID (FK) addressID (FK)
I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement.
Code: SELECT DISTINCTAddresses.city as branchCity, Addresses.street as branchStreet FROMCompanies LEFT JOINCompaniesDetails AS cd ON companies.companyID = cd.companyID
LEFT JOINCompanies_Addresses AS c ON c.companyDetailID = cd.companyDetailID LEFT JOINAddresses ON c.addressID = Addresses.addressID
WHERE Companies.name LIKE 'abc' ANDCompanies.status_indicator like 'Current'
I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code: select groups.group.id, groups.group.name from groups, users where groups.disabled='1' and users.user_id = $user_id and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
Hi, i have some sql experience and can link tables but the link i am trying to get is not displaying how i need it to
here is the code i am using, which display logical results, but not the ones i need :P
qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.UNIQUEID = wce_mailer_link.Contactid LEFT JOIN wce_mailer ON wce_mailer.uniqueid = wce_mailer_link.mailerid RIGHT JOIN wce_mailer_attachments ON wce_mailer_attachments.uniqueid = wce_mailer.fileid WHERE wce_contact.uniqueid = '"& Request.QueryString("id") &"'"
Ok i have these tables
wce_contact This has the contacts name and address
wce_mailer This holds the details of the mailer and a link to the wce_mailer_attachments, there would be multiple rows in wce_mailer_attachments table which link to 1 row in wce_mailer.
wce_mailer_link This holds the wce_contact uniqueid, and the wce_mailer uniqueid. there will be many contacts to many mailers
wce_mailer_attachments This holds an individual row for one attachment, but the uniqueid would be the same for multiple rows, Dependant on how many attachments the users adds. i.e. one mailer could have several attachments, they would all have the same uniqueid.
Basically the results i am getting using the join i built are displaying each attachment as a separate row when i display the mailers assigned to a contacts record. i need them to display in one single row where the uniqueids are the same in the wce_mailer_attachments and they match the only fileid in wce_mailer.
Hi Can anyone help me join these two quries together? SELECT SUM(SchemeAccount.TotalVar) AS TotalVar, Scheme.Code FROM SchemeAccount LEFT OUTER JOIN Scheme ON SchemeAccount.SchemeID = Scheme.SchemeID GROUP BY Scheme.Code
and
SELECT SUM(Variation.VarAmount) AS VarAmount, Scheme.Code FROM Variation RIGHT OUTER JOIN Scheme ON Variation.SchemeID = Scheme.SchemeID GROUP BY Variation.SchemeID, Scheme.Code
I am getting output two times but my table has single data then how to remove duplicate my SQL query is:
select nonregister.email as firstname,nonregister.email ,appliedjobstatuswithresumes.description,appliedjobstatuswithresumes.applieddate from nonregister , appliedjobstatuswithresumes where appliedjobstatuswithresumes.nonregid IN (select nonregid from nonregister where nonregid IN (select nonregid from [appliedjobstatuswithresumes]
