Searching Multiple Entities At A Time
Aug 27, 2005
Dear Group
Can anyone provide a sample query for the following scenario?
Let's assume I want to search for an order someone placed which might
be an individual or company. An individuals first name is stored in
column FirstName And the individuls last name in column LastName of the
contact table and the company name is stored in column CompanyName of
the company table.
If a user issues a query with CName parameter how can I search all
these columns and tables at the same time to see if there's a match? I
might add that there's a column in the orders table that holds
0=Contact 1=Company but ideally I don't want to use this column.
Thanks very much for your help and efforts.
Martin
View 2 Replies
ADVERTISEMENT
Sep 10, 2007
Apologies if this has already been asked and answered, though I haven't found it via search.
In my report model I have a lookup entity that consists of a Code field (PK) and a Description field this is linked to a parent entity (table) and works fine, e.g. the description is shown in the parent entity instead of the FK code. However this lookup entity would be useful linked to many other entities (tables) which use the same FK code linked to the lookup entity's Code field (PK) for use of the Description field. In my first attempt I linked the lookup to a second table entity but unlike the originally linked table entity, this is not shown as the description in report builder, only the role appears and I have to select the role then the description from the 2 fields displayed (Code, Description), additionally I get shown the related first table entity.
Firstly can a lookup entity be linked to multiple tables?
Secondly, if so am I forgetting something so only the description is shown like in the first linked table entity?
Thirdly, if I correct the problem is there a property I need to enable/disable to stop the first linked table entity appearing in the second table entity entity list in report builder or should I just leave it?
Any help/advice/suggestions would be appreciated, and apologies for long list of questions but all the books and help files I have used don't seem to use imperfect/real life data sources and examples.
Thanks
Andy
View 1 Replies
View Related
Oct 26, 2007
Hi Everyone,
I'm attempting to design a schema for a project I working on. The idea is that I can store a list of documents and associate meta data with them.
The problem is that I don't know what that meta data will be at design time. The user will create the meta data fields in the software. For the first design I just had a table called meta field that contained the meta field defintion, and another table called itemdata that contained the document primary key, the metafield primary key and a value encoded as a varchar. This works fine while the list of documents is small.
Another idea I've thought of is to have seperate tables for all of the metafields. This would allow me to be very specific about the values that can be stored in that column etc. It also turns out that for the kind of queries I'm doing its about 4 times as fast. The only problem is that the software will need to create these tables at run time.
What I would like to know is, is this a really bad idea? If so why?. Also are there any other ways I can store this kind of data?
The kind of queries I am doing are find all the documents where metafield 1 is A and metafield 2 is B and metafield 3 is C and metafield 4 is D etc.
Cheers,
Mark
View 4 Replies
View Related
Jan 12, 2006
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
View 3 Replies
View Related
Jul 20, 2005
> This is what I want to do with the data table below.[color=blue]>> I only want it to show one id for each issue. ( ie, 4001 only once,> 4002 only once, etc. But only the one with the latest date and time)>> Ie. 4001 should only be in the table once, with the latest date -> which should be :> 400109/12/200317:12:09 (as I only want to show the last time> the id was updated - do you get me now )>> Ie. 4002 should show only 400211/12/200315:25:13>[/color][color=blue]>>> idhs_change_datehs_change_time> 400127/10/200310:38:27> 400109/12/200314:43:58> 400109/12/200317:12:09> 400227/10/200310:56:28> 400209/12/200314:44:11> 400211/12/200315:25:13> 400327/10/200311:13:12> 400309/12/200314:44:21> 400310/12/200310:48:02> 400310/12/200313:25:09> 400427/10/200311:28:09> 400409/12/200314:44:29> 400409/12/200317:18:28> 400527/10/200315:55:40> 400528/10/200310:18:24> 400627/10/200315:59:47> 400628/10/200310:18:38> 400609/12/200314:44:40> 400609/12/200316:47:15>[/color]
View 3 Replies
View Related
Jul 16, 2007
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies
View Related
Jul 26, 2004
im in need of some help, im new at sql and am trying to seach multiple tables.
i am doing a football database with a table for each of the teams
and each table has the following
"First Name" "Last Name" "Age" "Date of Birth" etc
what i am trying to do is search for either the "first name" or "last name" in every table
if anyone can help me it would be apreciated
View 1 Replies
View Related
Nov 19, 2004
i have multiple tables that vary very little in name. what i mean by this is they are named tbleffort1, tbleffort2 etc. i need to search all the tables together. there is a large constantly changing number of tables, so i would prefer not to have to write them out one by one! any suggestions would be most appreciated!
View 4 Replies
View Related
Mar 22, 2008
Hello to all coding members!
I'm building my first ever complete website in the ASP.net VB language and I have to say, I'm pretty happy that I was able to learn from this website and to gain some ground.
Data retrieval and editing from tables and databases was very intuitive with the Visual Web Developer IDE. I even learnt simple ways to implement (and to script a bit of) login pages to make my visitors feel "at home" in my website.However, when I wanted to implement simple Business Logic into one of my ASP.net page, i happen to stumble upon a block.Business Logic: To have the server go through the whole table of storybook entries submitted by many users, but only pull out the storybook entry(s) that belong to the logged-on user.Hence, let me drill down to my specific questions:
I will be using VB. Coming from a JavaScript genre, my concern is: will putting the Business Logic code into a code-behind page (.vb) or dumping it onto the page itself have any errors or effects?
View 8 Replies
View Related
Feb 15, 2008
Hi,I'd be interested in people's thoughts about the following. A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for. Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple LIKE '%' + @searchTerm + '%' however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue. The benefit of this would be that you could do a:LIKE @term + '%'but then I have the cost of the join. (And the added complexity which is not a major issue)Any thoughts/tips?Thanks!
View 1 Replies
View Related
Jun 11, 2015
I want to write a Stored Procedure which can search based on multiple condition and in default is select all.
Example :
User Name [ ALL ] Textbox
Project Name [ ALL ^] dropdown
ID [ ALL ^] dropdown
-----------------------------
Display On Gridview(Default display all records)
select one condition search one and so on.
View 5 Replies
View Related
Jan 31, 2006
Hi,
I’ve three questions, would someone answer them please,
I have 3 entities:
The book entity(BookNo,BookNmae,AuthorName,Price...etc), the MembershipCard entity(CardNo,MembershipNo,ClientNo,PublishDate,EmpNo..etc), and the Client entity(ClientNo,ClientName,ClientAddr,ClientPhoneBookNo,RequiredBookNo,..etc)
1-What is the relationship degree between them, is it ternary or binary?and Why?
I'm so confused ,my answer is ternary, I made a (1:m) relationship between the clients entity and the books entity (a client may buy more than one book), (1:m) relationship between the employee entity and the books entity (an employee may sell more than one book)and a (1:m) relationship between the employee and the Client(an employee may serve more than one client(is it right or it's (M:N)).
So is my answer is right or wrong, please help me to understand, I’m so confused.
2- Is the cardinality degree between the Client entity and the book entity , (0,N) for the client entity and (1:N) for the book entity??
3-What is the best model to use in designing data in general,Chen's model or Crow's Foot and why?I think it's Crow's one ,for it's more arranged and easy to read.
with thanks
deema
View 7 Replies
View Related
Apr 11, 2007
I created a report model with a single "data source view." The datasource view has three tables "Business, XRef, Employee" and a named query "PeopleCount" based on these tables. The smdl [design] view shows "Business, XRef,Employee" as "entities" and does not show the named query from the "data source view" (I don't know if that is normal). I published this report model to the reporting server (its showed "success").
I then used Report Builder to open the report model as a "chart", and it shows there is only one entity for me to play with, its called "XRef." Why do the other tables ("Business" and "Employee") not show up in the Report Builder's list of entities?
My own guess is that "XRef" is the only "actionable" table for making a chart. Indeed, I notice that among its "fields" is one called "Business Title" (which apparently comes from the Business table). But I also notice it does not have an "Employee Name." Why have the former and not the latter? Again, my own guess is that the type of the Employee.Name field, a nvarchar(MAX), might be considered unusable for reporting, since it is a "max" size. Thoughts?
I am using SQL Server 2005 RTM, which is running on Win 2K3 Server SP1.
View 1 Replies
View Related
Dec 22, 2006
Why is it that only SOME of the tables in my database show up in the Entities pane?
There are a total of six tables in my database, yet only three of them are available to me in the Entities pane. I have looked at permissions, etc, and I am using the SA account. It seems like I should see either ALL or NONE of the tables, but that is not the case.
What am I missing?
View 1 Replies
View Related
Apr 13, 2014
I have to connect 6 entities. I am getting NO RESULT (no output)!!!
BEGIN TRANSACTION;
DROP TABLE TOPIC;
DROP TABLE LECTURE;
DROP TABLE PRICE;
DROP TABLE COURSE;
DROP TABLE PUPIL;
DROP TABLE LP;
[Code] ....
View 1 Replies
View Related
Mar 21, 2008
Hi,
A Composite entity can be made out of two Primary Keys (PK) of two different entities. Therefore it can be called as a link table. Therefore the composite entity depends upon the other two entites. Therefore the composite entity is a exsitence dependent on the other two entities.
Therefore do every composite is a weak entity?
TY
View 3 Replies
View Related
Jul 20, 2005
Our customer (of our ecommerce system) wants to be able to preservedeleted entities in the database so that they can do reporting,auditing etc.The system is quite complex where each end user can belong to multipleinstitutional affiliations (which can purchase on behalf of the user).The end user also has a rich trail of past transactions affiliationsetc. Thus in the schema each user entity is related to many otherswhich in turn relate to yet others and so on.In the past when a user was deleted all of his complex relationshipswere also deleted in a cascading fashion. But now the customer wantsus to add a "deleted" flag to each user so that a user is never_really_ deleted but instead his "deleted" flag is set to true. Thesystem subsequently behaves as if the user did not exist but thecustomer can still do reports on deleted users.I pointed out that it is not as simple as that because the user entityis related to many, many others so we would have to add this "deleted"flag to every relationship and every other entity and thus have"deleted" past purchases, "deleted" affiliations - a whole shadowschema full of such ghost entities. This would overtime degradeperformance since now each query in the system has to add a clause:"where deleted = 0".I assume this is a standard problem since many organizations must havethis need of preserving deleted records (for legal or other reasons).I tried to talk them into creating a simple audit file where all thedeletions will be recorded in XML but they were not too happy withthat.Is there a more satisfying solution to this than have this "deleted"flag?Thanks for your help,- robert
View 9 Replies
View Related
Oct 16, 2007
Hi everybody,
When I've deployed a report model,in Report Builder I have 4 different entities at the same level,when I drag and drop the first field from one entity after I can't see the other three entities....What did I do wrong??
I don't understand ,if you could help me it would be great.
Have a nice day!
sandy
View 3 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Aug 27, 2014
I have the following data stored in a column in a table:
£10,000.00 & ' " % < > Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones
I query this in a stored procedure using the FOR XML clause and universal table, and store the result in an XML data type variable:
DECLARE@ResultXML
EXECUTE[someStoredProcedure] @Result OUTPUT
This data (as XML) is then written to a file; in order to do this, I CONVERT the data to NVARCHAR since there are unicode characters in the source:
DECLARE@strResultNVARCHAR(MAX)
SET@strResult= CONVERT( NVARCHAR( MAX ), @Result, 1 )
Now this works fine, except on inspection, SQLServer has decided to render the data thus:
£10,000.00 & ' " % < > Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones
Why it has changed the apos and quot entities to the corresponding character but not the other entities is beyond me.
how to preserve XML entities?
View 0 Replies
View Related
Jul 23, 2005
I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information aboutarbitrary entities. Basically, allows the clubs to define custom entities(i.e tables) and associated custom attributes (i.e. fields) that may berelated to existing tables (such as Player and FootballClub) or existingentities. For instance, a club may define a PlayerAssessment entity thatrecords all player assessments.To do this, I plan to support the following use case:1. FootballClub admin creates a new entity and gives it a name anddescription (Entity is only accessible to this FootballClub).2. FootballClub admin indicates that the new entity has a M:1 relationshipwith the Player table (this will add Player_ID as a FK attribute).- {An entity may have no relationships.}- {Relationships are also supported to other entities.}3. FootballClub admin specifies the names and domain/types of any dataattributes (i.e. fields) of the entity.- {An attribute's type may be constrained to a few allowable types likeRelationship, Integer, Float, Currency, Date, Time, DateTime, Name,Description and Memo.}4. System creates entity as specified.A few constraints:1. Any entity defined is "private" to the defining club. Other clubs aren'taware of it although they may define custom entities of their ownwith the same name and attributes. [Perhaps there is a way to sharedefinitions of identical entities?]2. A club doesn't have to define any custom entities.Ideas I've considered:1. Generate DLL and create actual tables- Restrict such customizations such that while admin is setting up entities,no other user is allowed to use the system.- Once entity definition is complete, generate an actual table using DLL.Table and column names might be changed to enforce uniqueness/validityconstraints - this suggests a need for table/column name mapping.- PROS: Easy to implement.- CONS: Doesn't scale since only a limited number of tables can be created.DDL on a live, shared system?. Scary!!All users for all clubs will be locked out while entity iscreated.2. Generate DDL and create actual tables in secondary database(s)- Same as above except that the user tables are created in secondary [,shared] databases.- PROS: Reassurance that DDL is never run on the "core" dataAll users don't have to be locked out.- CONS: Doesn't scale since only a limited number of tables can be created.{ Unless I start creating additional databases too!. }Still needs to DDL on a live, shared system.Has anyone done anything similar?. Any ideas on how it might be done?. Inparticular, is this possible without having to execute DDL on the livedatabase?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================
View 3 Replies
View Related
Aug 12, 2015
I have configured master data services in my DB server and when i am going to explorer the entities and respected data by using navigating as below
[URL] -->Click to Explorer
I am able to select data for only one default entity, in entities drop down is getting hidden back side , please find below snap to clearance.
Note: I am using Internet explorer (11.0.9600.17239) to browse the solution.
View 2 Replies
View Related
Feb 1, 2007
I have scoured the internet looking for someone who has run into this issue with no such luck!
Product: Reporting Services 2005 Analysis Services 2005
Our users use Report Builder as an ad-hoc report tool. Data sources for Report Builder come from Report Servers and Analysis Servers.
I have run into issues where a user will select a source for their report that is based on an AS cube. When they select an attribute (field) from a selected entity in the Report Builder Explorer and drop it on the canvas, all entities except the one the field was selected from disappear.
For example:
A cube has a fact table called "Annual Income" with dimension of employee and demographics. In Report Builder, the entities would be listed in the left hand Report Builder Explorer window as
Annual Income
Employee
Demographics
If I select attributes from Employee or Annual Income, as usual, you will see the related hierarchies of the remaining entities. However, if I select an attribute from Demographics, then the Annual Income and Employee entities just disappear and all I can see is the Demographics entity and its attributes.
Has anyone run into this and if so, any ideas? This is becoming extremely frustrating. I have deleted and rebuild cubes, individual dimensions, and everything else I can think of.
Your responses are appreciated,
Scott
View 1 Replies
View Related
Jun 14, 2015
Let's say I have an rdlc report for customers, and it has three sections with subreports:
CUSTOMER_INFO (name, phone, e-mail)
ITEMS
OBSERVATIONS
As the page only has 21cm and the report grows horizontally, I need to repeat the WHOLE report with all of its sections after every 4 customers. So if I have 5 customers, there would be three pages(let's say every subreport takes 1 page) for the first four customers, and then another three pages(with every section once again) just for one customer.
Is it possible? It doesn't seem somehing out of the ordinary, but I can't figure how to make it work. Everytime there is more than 4 customers, the report breaks its layout trying to accommodate the records in a new table below the tables of each subreport.
The expected result would be to bring all of exceeding data to new pages.
View 5 Replies
View Related
Apr 27, 2015
I am trying the assign the models to the user in the user management page. But when I try to expand the + symbol next to the model and open the entities, I get the error that the object reference is not set for a object server instance.
View 2 Replies
View Related
May 31, 2008
Hi: I have 3 tables namely:
1 Category(CategoryID(int), CategoryName(varchar),
2 SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName)
3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar))
how to delete correspoding subcategories of category from SubCategory,Productlist tables using triggers
Ex: Category :TV Subcategory:ColorTV,Plasma,LCD...Plz Send me the query....
Thanks
View 1 Replies
View Related
May 19, 2011
I am currently adding 250 databases to a SQL Server (MS SQL Server 2008) using SQL Server Management Studio. It took all day yesterday, and I am sure there has to be another way to do it. The filenames are quite complex and it is not easy tracking which ones I have added. Is there a wee loop or something I can use to add all .mdf and .ldf files in a folder to the server?
View 4 Replies
View Related
Dec 4, 2006
Hi all
ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>
above query alters the sigle column but how to alter multiple columns at time
saivyshnav
Junior DBA
View 7 Replies
View Related
Jan 10, 2007
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user.
DELETE FROM T1,T2,T3 WHERE column1='1'
how do i do it.
View 4 Replies
View Related
Feb 28, 2008
I have 3 tables
Table1
F1, F2, F3
1 ABC DEF
Table2
F1,F2
1,123
Table3
F1,F2
1,456
I need to update Table2 and Table3 based on Table1 fields. All the 3 tables having F1 primary/foreign key relationship.
The output should looks like for table2 and table3 must be:
Table2
F1,F2
1,ABC
Table3
F1,F2
1,DEF
I need a single update statement. Appreciate your help.
Thanks,
Sampath
View 5 Replies
View Related
Aug 22, 2007
HI,
We are currently trying to import the Data from one DB to Multiple DBs.And these multiple DBs connection strings must be configurable. This I can do it by reading the connection strings from dtsconfig file and execute it for each DB.Is it possible to execute for all the DBs in parallel(Multithreaded).
Thanks,
Sabari.
View 6 Replies
View Related
Feb 27, 2007
hi,
i am making a n application which in between deletes the multiple tables from the sql database.
for that i have written the following code:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;
cmd.ExecuteNonQuery();
in this way, many more tables are to be deleted.
is there any need to create the new SQLCommand object again and agin for each and every query. can iot be done like the given above or can there be some better method?
thanz in advance..
divya
View 1 Replies
View Related
Nov 16, 2006
I have a table that contains the following
UserName - Which is unique to each user
TimeStart - Which is the time and date that the user logs in
TimeEnd - Which is the time and date that the user logs out
Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.
View 1 Replies
View Related
