Bit Of A Complicated SQL Query To Try And Write...
Feb 12, 2008
I'll start by giving the basic idea of the data structure I'm looking at in the database (pardon my MS Paint skills!):
And this all starts with a root plan. So (taking projects out of the picture for the minute), it could be a structure going any number of levels down like so: plan -> goal -> strategy -> plan -> goal -> strategy -> plan -> goal -> strategy, etc.
And not forgetting, that a strategy can also have any number of projects, and all these relationships are one to many, so it ends up being like a large tree structure.
What I am trying to accomplish is user security related, in that I need to enforce rules set to say whether a single user is allowed to view a project. You can define the rules at any level in the structure above. So if for instance, a user is given a rule that they can access anything from a goal, they can see any projects that exist in any strategies underneath that goal in the tree structure. I hope this is making sense so far... The rules can also just be defined at a specific project level.
These rules (at the moment, I'm more than happy to change if this is a silly way of doing it) are set in a table that has the columns:
user_id int
plan_id int
goal_id int
strategy_id int
project_id int
allow bit
Every row will have a user_id value, and one of either a plan_id, goal_id, strategy_id or project id (which I thought would be a simple way to pull out what kind of rule if needed), and the allow column just defines if it's an allow or deny rule (a specific deny rule on any object lower in the tree structure will override any cascaded allow rule).
The way my application needs to grab this information, is to simply have some way of returning a list project_id values for which the current user is allowed to access. I'm basically stumped. The closest I have gotten (which still didn't quite work) was going to end up using quite a large number of cursors... Which is bad. It can be inefficient to an extent, as once these values are gathered once, I can store them in a Session variable, so it's not grabbing them every time the user tries to load a project.
I have a table with 4 columns, ID, Key, Value and SiteID.
SiteID can be Null or an int.
As an example I could have these four rows, ID=1, key=colour, value=red, siteID=Null ID=2, key=colour, value=green, siteID=2 ID=3, key=font, value=arial, siteID=2 ID=4, key=size, value=6,siteID=Null
My query will pass in a siteID and I need to bring back all rows with that site ID plus any rows whos key I haven't already got who's siteID =Null.
The Null site ID is default, so it always needs to bring back the null if a key doesn't exist with a siteID.
Does anyone know if that is even possible with just a straight SQL query?
Hi I have been tearing my hair out trying to resolve this problem. A form is submitted with a number in to checkthe database for the items with that number of items in the quantity field(s) and display them.
There are 2 relevant Tables in the 2005 server SQL Database, One called "Products" the other called "ProductDetails"
In both tables is a field called Quantity, this hold a numerical value for the quantity of the product.
Some products have additional attributes such as sizes or colours and these are stored in the ProductDetails table.
Every product is stored in the "Products" table and has a unique ProductID, where a product has additional attributes, these along with the ProductID and the quantity for that particular attribute are stored in the ProductDetails table.
I am trying to loop through the database and produce a list of all the items and if they have any additional size or colour attributes then display these too, and if they dont have additional attributes then just display the product details and quantity stored in the Products table.
What I have actually done instead is produce a list of every item and every attribute in the database, but for example Where product 1 doesnt have any attributes, and neither does product 2, but product 3 and product 3 do, when the details are displayed on the page (Using ASP),
Rather than displaying product 1 details and the quantity in the "Products" table, product 2 and the details & quantity in the Products table, then product 3 and each of the attributes & Quanity pertaining to that item in the "ProductDetails" table, and so on, it shows every item as having every combination of attributes,
so although products 1 and 2 have no attributes, it will show product 1 with the first attribute of the other products and then show another product 1 with the next attribute from another product and keep going untill all the attributes in the whole of the Product details table is shown , then do the same thing with product 2 and then product 3 and so on.....
Also the products that have no attributes are not only shown with the attributes that they dont have, they are not shown without attribues and with the correct details in the list.
Here is my string, which has changed a million times, I am just learning as I go along so if it is obvious please be pateient with me and help me out...
rsResults.Source = "SELECT DISTINCT Products.ProductID, Products.ProductName, Products.ProductPict, Products.Quantity, ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color, ProductDetails.Quantity FROM Products, ProductDetails WHERE Products.ProductID = Products.ProductID AND Products.Quantity <= "& +rsResults__var1 & " OR Products.ProductID = ProductDetails.ProductID AND ProductDetails.Quantity <= "& +rsResults__var1 & " Order by Products.ProductID asc "
Can anyone see why it is doing this instead of only showing the attributes next to the items that have attribues?
I have two tables: persons (id,age,roleid) roles (roleid,description)
I want to build a sql query to produce the following rows (example): range(age) role1 role2 role3 .... rolen 0 to 4 11 24 5 7 5 to 9 42 7 1 0 10 to 14 14 21 9 8 15 to 20 7 0 7 19
I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: TimeplayerId Games_0-30_secondsGames_30-50_seconds
8-8:30 A 3 2 8:30-9B 2 10 9-:30C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS numberOfCalls_0_30 FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
Hi,I have two tables Trade table and Cons table. Records are inserted inboth the tables independent of each other. There are fields likeExc_Ref, Qty, Date in both the tables.I need to write a query which should give me records :1. Where there is missing Exc_Ref value in either of the table. i.e.If Trade table has a Exc_Ref value but missing in Cons table then thatrecord should be displayed. Similarly if Cons has a Exc_Ref valuewhich is not found in Trade table then that too should be displayed.2. In case where both the tables have matching Exc_Ref data then itshould display the record only when the remaining column does notmatch like Qty or Date.Please help me to resolve this complicated query.ThanksNick
I Have not been able to solve this problem from quiete a while now.
I am using sql server 2005.
I have got a table which contains these columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month
I have to perform a query on this table so that I can group the volumes for different months and different years.
Here is the sample data...
Service Start Service End FMIS Code No of Units Year Month Volumes
01-Oct-00 15-Aug-01 6440 32 ? ? ?
01-Oct-00 30-Sep-02 6441 40 ? ? ?
01-Oct-02 22-May-03 6440 78 ? ? ?
01-Oct-02 23-May-03 6990 87 ? ? ?
06-Mar-03 31-Jul-03 6997 102 ? ? ?
07-Mar-03 31-Jul-03 6744 3 ? ? ?
01-May-03 31-May-03 6440 789 ? ? ?
23-Jun-03 31-Aug-03 6447 1000 ? ? ?
29-Jun-03 30-Jun-03 6440 981 ? ? ?
30-Jun-03 31-Jul-03 6000 50 ? ? ?
01-Jul-03 08-Jul-03 6002 54 ? ? ?
01-Jul-03 13-Jul-03 6000 562 ? ? ?
I have to calculate the columns coloured in blue..
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: Time playerId Games_0-30_seconds Games_30-50_seconds
8-8:30 A 3 5 8:30-9 B 2 10 9-:30 C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2, SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?
Hi, I am positive some SQL Gurus can help me with this complicated query. I am not sure if it is possible with a single query or needs multiple queries.
I have 3 objects. First, PaintOrder; Second, Combination; and Third, Material. Each Paint Order can have multiple Combinations and each Combination can have multiple (from 0 to 5) Materials. All the data is stored in single table called paintMatCom.
I have a Search Screen where user can select multiple Materials (upto 5) to search for the appropriate PaintOrders. Also, use has the option to select AND/OR between materials. Please look at the attached image for clear understanding.
Some of the sample searches are as follows (Combination IDs are not needed in the Output; just PaintOrder IDs). 1. Find PaintOrders with MaterialIDs 1 AND 2 OR 4 2. Find PaintOrders with MaterialIDs 2 OR 3 OR 4 3. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1
Hope you got the idea. I think it would require some Joins and GROUP BY which I am not expert at. I would appreciate any help.
[IMG]C:Documents and SettingsOwnerDesktopmaterials.JPG[/IMG]
You can display both by referring to their position in the recordset or by aliasing them in the query and referring to the alias in your code. Note that in order to put Products.Quantity or ProductDetails.Quantity in just one column you'll have to add some logic in the query, using a coalesce, like:
sql Code:
Original - sql Code
SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity, ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color FROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR ProductDetails.Quantity <= "& +rsResults__var1 & " Order by Products.ProductID asc;
SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_ColorFROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR ProductDetails.Quantity <= "& +rsResults__var1 & " ORDER BY Products.ProductID ASC;
Untested, beware of syntax errors! In this case you'll have just one Quantity colum and you won't have to change anything in your asp code.
I have run into a problem, I have 2 fields in my database, both key fields:
Table 1 ===== Field X <key> Field Y <key>
In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:
Enter the Codes you want: 1000 and 3000 and 8500
So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.
because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)
So I guess I want something like this:
SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD
^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.
Folks,I have the following data in a table:4 NULL NULL2 abc NULL2 aaa NULL4 xyz NULL4 xyz pqr4 pyz xqrI want to get only one record for each record number. that is, theresult set should be like this:4 NULL NULL2 abc NULLPlease suggest how the query should be built. Thanks for the help.Murali
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: Time playerId Games_0-30_seconds Games_30-50_seconds
8-8:30 A 3 5 8:30-9 B 2 10 9-:30 C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
I just don't know how to do it! other ways? ideas? Please help!!!!
Hi, hope someone can help or suggest something to help me with the issue.
I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up
parent_ID child_id Type projname ----------------------------------------------- 100 100 P parent_X_proj 100 25 C child_X_proj 100 29 C child_X_proj2 200 200 P parent_Z 300 300 P parent_Y etc................
this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project. so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.
I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.
SQL2K on W2KserverI need some help revamping a rather complicated query. I've given thetable and existing query information below. (FYI, changing thedatabase structure is right out.)The current query lists addresses with two particular types('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check eachcontact for address type 'AM39DK3KD9' and then (2) if the contact hastype 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if thecontact does not have that type then select types ('MN30D843J2','SC93JDL39D'). (Context - the current query selects two standardaddress types "Main" and "Secondary"; we've added new data and now havetypes "Alternate Main" and "Alternate Secondary". If the Contact hasAlternate addresses, I need to select those; if not, I need to selectthe standard addresses. There are other address types in use, so Imust specify which types to select.)Can anyone point me in the right direction?Thanks very much! jamilehCREATE TABLE [CONTACTS] ([CONTACT_X] [char] (10),[LONGNAME] [char] (75),[ACTIVE] [bit])CREATE TABLE [CONTACTADDRESSES] ([CONTACT_X] [char] (10),[ADDRESS_X] [char] (10),[ADDRESSTYPE_REFX] [char] (10),[ACTIVE] [bit])CREATE TABLE [ADDRESSES] ([ADDRESS_X] [char] (10),[ADDRESSLINE1] [char] (60),[ADDRESSLINE2] [char] (60),[CITY] [char] (20),[STATE] [char] (2),[ZIPCODE] [char] (11),[PHONE] [char] (10))CREATE TABLE [REFERENCETABLE] ([REFERENCETABLE_X] [char] (10),[ADDRESS_X] [char] (10),[DESCRIPTION] [char] (60))CREATE TABLE [MASTERTABLE] ([CONTACT_X] [char] (10),[RECORDTYPE] [char] (1),[ACTIVE] [bit])CREATE VIEW vw_CONTACTInfo_ListLocASSELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,CONTACTADDRESSES.ADDRESSTYPE_REFX,Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,ADDRESSES.ZIPCODE, ADDRESSES.PHONEFROM CONTACTS INNER JOIN CONTACTADDRESSES ONCONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOINADDRESSES ON CONTACTADDRESSES.ADDRESS_X =ADDRESSES.ADDRESS_XINNER JOIN REFERENCETABLE Type_REFERENCETABLE ONCONTACTADDRESSES.ADDRESSTYPE_REFX =Type_REFERENCETABLE.REFERENCETABLE_XWHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFXIN('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =1)) AND(CONTACTS.CONTACT_X IN(SELECT CONTACT_X FROM MASTERTABLE WHEREACTIVE = 1 AND RECORDTYPE = 'E'))
I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.
tblWine tblSources ID Name ID WineID Source Price Status ----------------- ----------------------------------------- 1 Le Dome 1 1 Smith 100.00 IB 2 Teyssier 2 1 Jones 110.00 IB 3 Muscat 3 1 Hill 100.00 DP 4 2 Smith 135.00 DP 5 2 Hill 125.00 DP
I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.
tblWineSources WineID Name SourceID Source Price Status ------------------------------------------------------------- 1 Le Dome 1 Smith 110.00 IB 1 Le Dome 3 Hill 100.00 DP 2 Teyssier 5 Hill 125.00 DP
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
I have two table named tbl_Scale and tbl_NGTrDAMaster tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key and we get CityTypeID from xml databinder....... In my form thr is two drop down list one for scale name and another for city type id this is the data form tbl_NGTrDAMaster 17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412 but i am expecting only one combination of set..... like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that.... please help me.... i am in big trouble......Thanx in advance If my qes is not clear for everyone... plz tell me.... i try my lebel best for understand my prob to u.....
Hello, I have a table with fields; T1: Dept, Name, Desc, ModificationDate How can I group by T1.Name, T1.Desc and bring T1.Dept which has the latest T1.ModificationDate Can anyone write me this query?
I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
i am assuming there is a better way to write this query (since im not too proficient in SQL)
sql Code:
Original - sql Code
select client_id from clients where client_id not in (select schedule_det.client_id from schedule_det, schedule_mstr where schedule_det.schedule_id=schedule_mstr.schedule_id and schedule_mstr.status_code!='COMPLETE')
SELECT client_id FROM clients WHERE client_id NOT IN (SELECT schedule_det.client_id FROM schedule_det, schedule_mstr WHERE schedule_det.schedule_id=schedule_mstr.schedule_id AND schedule_mstr.status_code!='COMPLETE')
these table have some common feild names. table1,2 and 3 all have a,b,c and d as field names. each table has other field names too but the ones they all have in common are a,b,c and d.
so i would like to write a query that returns all rows from all 3 tables where column d is greater than 5 and less than 10.
so basically i want it to treat the records from all 3 tables ad one big dataset.
how would i write a query to do this.
i know i could say: SELECT a,b,c,d FROM table1,table2,table3
but what gets me is the WHERE clause
do i have to say WHERE table1.d >5 AND table1.d <10 OR table2.d>5 AND table2.d <10 OR table3.d>5 AND table3.d <10
Insert into Ename Select 'a' Union all Select 'a' Union all Select 'a' Union all Select 'b' Union all Select 'c' Union all Select 'b' Union all Select 'd' Union all Select 'g' Union all Select 'g'.
Hi i want to audit a tables For That i created audit tables In that table i want to store data as all field related to old data table and from which system user had changed the data
For this system id i used host_id() but the iam not getting the id
I have a table with many records in it. There is one field called "Nature". How would I select the value that appears the most often in the "Nature" field? The nature field contains text.
For example, this code selects those with more than 10 records...
I just want the top record.
SELECT count(*) FROM WEBASGN_FULL GROUP BY NATURE HAVING count(*) > 10
Basically select nature from webasgn_full that occurs the most often in the table....
