|
Get about ten years experience. Then you will know how to deal with the types of situations you will likely encounter.
|
|
|
|
|
hi ,i want to select name from all around tables
& i have near 25 tables such as : select name from pharmacy , hospital , theater ,etc...
all of them is linked with place table by id
this picture show sample of tables :
http://www.mediafire.com/?20rsb4q25kyj14w[^]
|
|
|
|
|
If there can be several names for a specific place :
SELECT
'Pharmarcy' AS Entity
,Pharmacy.Name AS Name
FROM
Pharmacy
INNER JOIN Place ON Place.Id = Pharmacy.PlaceId
UNION
SELECT
'Hospital' AS Entity
,Hospital.Name AS Name
FROM
Hospital
INNER JOIN Place ON Place.Id = Hospital.PlaceId
and so on...
If there can be only one name for a specific place:
SELECT
Pharmacy.Name AS PharmacyName
,Hospital.Name AS HospitalName
FROM
Place
INNER JOIN Pharmacy ON Place.Id = Pharmacy.PlaceId
INNER JOIN Hospital ON Place.Id = Hospital.PlaceId
No memory stick has been harmed during establishment of this signature.
|
|
|
|
|
i have used union
select mall.name from mall,places where mall.place_id=places.id UNION select hospital.name from hospital,places where hospital.place_id=places.id UNION select clinic.name from clinic,places where clinic.place_id=places.id UNION select shop.name from shop,places where shop.place_id=places.id
but it's very long i should select name from 25 table
can you use nested query i think it will solve the problem
|
|
|
|
|
Having a long query doesn't count as a problem. Why is it classified as such? Does it not work?
Bastard Programmer from Hell 
|
|
|
|
|
when there is much commonality in the fields of a number of tables, it tells me the data should have been structured differently, using just one or two tables and one more field. You now have to pay for the bad decisions made earlier, by adding unproductive code everywhere.
|
|
|
|
|
Now I get nervous when a newbie wants me to download a file to my computer so I can help him! So I won,t be looking at your image!
You need to be clear as to what you want - in your message not some dodgy image. Ery you looking for the entity name from each of your tables or do you have a name field on each of your tables or (god forbid) you actually store a persons name in each table!
If they are linked by ID fields you should be able to build FKs and create a view of all the entity names in one table (possibly).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
don't download image just click on preview on medifie
|
|
|
|
|
As Luc pointed out, that is a lousy design, if you have address all over the place then you have made an error in your data design. You are going to have to live with the monster query or refactor your database (probably not reasonable solution)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Change your place table: add the column name. And then move your data to the correct place, and get rid of the name column in those other tables.
|
|
|
|
|
hi ,i want to select name from all around tables
& i have near 25 tables such as : select name from pharmacy , hospital , theater ,etc...
all of them is linked with place table by id
this picture show :
http://www.mediafire.com/?20rsb4q25kyj14w[^]
|
|
|
|
|
Hi, Im stuck!
I habe a firebird database for which I am writing a VB.net frontend. Among others I have a table named tb_tags with the following collumns:
Tag;Tagtext;Tagtypeid
And a Table named tb_tagtypes with the collumns:
Tagtypeid;tagtypetext
I am filling a datagrid with the SQL command:
"SELECT a.tag, a.tagtext, b.tagtypetext FROM tb_tagtypes b join TB_tags a on b.TAGTYPEID = a.TAGTYPEID"
That works just fine!
Now I want, that the tagtypetext cell is a combobox with all the entrys of tagtypetext from the table tb_tagtypes. And that, according to what tagtypeid is stored in tb_tags, the right row of the combobox should be displayed when filling the datagrid.
Hope I made it clear enough, if not, I willtry my best to answer your questions.
Thanks,
Matthias
|
|
|
|
|
So you already have the data you need to populate the combo in your code, extract a list of the tagtypes into another collection, possibly a simple string collection, and bind that to the combobox via it's data source.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This is my Query i want to implement paging where i will get the page size as a parameter from UI..How do i implement paging in this query..Plz any one Help..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <ragesh nair="">
-- Create date: <11/04/2012>
-- Description: <search>
-- =============================================
--exec iSRPDb_Sp_Search_WO 1,0,'0','T',10,1
--select * from tblProject where ProjectID=13849
--select * from tblProject where ProjectID=22412
--select * from tblProject where ProjectCode='B20904'
--select * from tblSubContractor where SubContractorID=88420
--select top 10* from tblWorkOrderHeader where ProjectID=22412
ALTER PROCEDURE [dbo].[iSRPDb_Sp_Search_WO]
@pBusinessUnitID as int=0,
@pProjectID as int,
@pSubcontractorID as int,
@pWONumber as varchar(10),
@PageSize as int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BusinessUnitFilter Varchar(255)
DECLARE @NameFilter Varchar(255)
DECLARE @SubConNameFilter Varchar(255)
DECLARE @WONumFilter Varchar(255)
DECLARE @Filter Varchar(2000)
DECLARE @StatusFilter VARCHAR(255)
DECLARE @PageNumber int
DECLARE @RowStart int
DECLARE @RowEnd int
DECLARE @condition varchar(255)
SET @StatusFilter=''
--if @PageNumber > 0
--
-- SET @PageNumber = @PageNumber -1
-- SET @RowStart = @PageSize * @PageNumber + 1;
-- SET @RowEnd = @RowStart + @PageSize - 1 ;
IF @pBusinessUnitID > 0
SET @BusinessUnitFilter = 'TWO. BusinessUnitID=' + Convert(Varchar(10),@pBusinessUnitID)
ELSE
SET @BusinessUnitFilter = ''
IF LTRIM(RTRIM(@pProjectID)) > 0
SET @NameFilter = ' AND TP.ProjectID =' + Convert(Varchar(255),@pProjectID)
ELSE
SET @NameFilter = ''
IF LTRIM(RTRIM(@pSubcontractorID)) > 0
SET @SubConNameFilter = ' AND TS.SubcontractorID =' + Convert(Varchar(255),@pSubcontractorID)
ELSE
SET @SubConNameFilter = ''
If ltrim(rtrim(@pWONumber))<> ''
Set @WONumFilter = 'AND TWO.WONumber LIKE ''%' + @pWONumber + '%'''
Else
Set @WONumFilter = ''
SET @FILTER =@StatusFilter + @BusinessUnitFilter + @NameFilter + @SubConNameFilter + @WONumFilter
exec('select
TWO.WONumber,
TS.Name AS SubcontractorName,
TP.ProjectCode,
TP.Description as ProjectName ,
TP.StartDate,
TP.EndDate,
ROW_NUMBER() OVER(order by TP.Description) as RowNO
from tblWorkOrderHeader TWO inner join
tblWorkOrderDetail TWOD on TWO.WONumber= TWOD.WONumber
inner join tblSubContractor TS on TWO.SubContractorID= TS.SubContractorID
inner join tblProject TP on TWO.ProjectID=TP.ProjectID
where ' + @FILTER)
end
--select * from tblProject where ProjectID=20101
--select * from tblSubContractor where SubContractorID=110244
--where (@subconid = 0 or @subconid = aa.subcontractorid)
|
|
|
|
|
You don't say which SQL system this is, but assuming SQL Server.
I'd start at the basic, and use BETWEEN somehow. i.e. one of your WHERE clauses in the proc needs to be similar to:
WHERE [IDColumn] BETWEEN @StartID AND @EndID
Obviously @StartID and @EndID would be passed in.
Assuming IDColumn is an sequential and consecutive field this will return a defined number of rows.
In SQLServer 2008 (maybe 2005), they introduced RowNum (like Oracle), which would be more consistent, and wouldn't rely on an sequential field in the table.
That's where I'd start at least.
|
|
|
|
|
Thank You For Your Response...AnyWay I Have FigureOut the Solution....After implementing Paging the SQL Server Query Looks Like this: I Have created a temporary table and inserted data to that table...and from there i calculated the pagenumber and size..the query will explain more..Hope this will help somebody... 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <ragesh nair="">
-- Create date: <11/04/2012>
-- Description: <search wo="" including="" paging="">
-- =============================================
--exec iSRPDb_Sp_Search_WO 1,0,'0','T',10,2
--select * from tblProject where ProjectID=13849
--select * from tblProject where ProjectID=22412
--select * from tblProject where ProjectCode='B20904'
--select * from tblSubContractor where SubContractorID=88420
--select top 10* from tblWorkOrderHeader where ProjectID=22412
ALTER PROCEDURE [dbo].[iSRPDb_Sp_Search_WO]
@pBusinessUnitID as int=0,
@pProjectID as int,
@pSubcontractorID as int,
@pWONumber as varchar(10),
@PageSize as int,
@PageNumber INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BusinessUnitFilter Varchar(255)
DECLARE @NameFilter Varchar(255)
DECLARE @SubConNameFilter Varchar(255)
DECLARE @WONumFilter Varchar(255)
DECLARE @Filter Varchar(2000)
DECLARE @StatusFilter VARCHAR(255)
DECLARE @RowStart int
DECLARE @RowEnd int
DECLARE @condition varchar(255)
SET @StatusFilter=''
IF @pBusinessUnitID > 0
SET @BusinessUnitFilter = 'TWO. BusinessUnitID=' + Convert(Varchar(10),@pBusinessUnitID)
ELSE
SET @BusinessUnitFilter = ''
IF LTRIM(RTRIM(@pProjectID)) > 0
SET @NameFilter = ' AND TP.ProjectID =' + Convert(Varchar(255),@pProjectID)
ELSE
SET @NameFilter = ''
IF LTRIM(RTRIM(@pSubcontractorID)) > 0
SET @SubConNameFilter = ' AND TS.SubcontractorID =' + Convert(Varchar(255),@pSubcontractorID)
ELSE
SET @SubConNameFilter = ''
If ltrim(rtrim(@pWONumber))<> ''
Set @WONumFilter = 'AND TWO.WONumber LIKE ''%' + @pWONumber + '%'''
Else
Set @WONumFilter = ''
SET @FILTER =@StatusFilter + @BusinessUnitFilter + @NameFilter + @SubConNameFilter + @WONumFilter
CREATE TABLE #TempTable
(
RowNumber bigint ,
WONumber varchar(250) ,
Name varchar(250) ,
ProjectCode varchar(250) ,
Description varchar(250) ,
StartDate datetime ,
EndDate datetime
)
Declare @Query as nvarchar(max)
set @Query=''
set @Query= 'INSERT INTO #TempTable
select
ROW_NUMBER() OVER(order by TP.Description) as RowNumber,
TWO.WONumber,
TS.Name AS SubcontractorName,
TP.ProjectCode,
TP.Description as ProjectName ,
TP.StartDate,
TP.EndDate
from tblWorkOrderHeader TWO inner join
tblWorkOrderDetail TWOD on TWO.WONumber= TWOD.WONumber
inner join tblSubContractor TS on TWO.SubContractorID= TS.SubContractorID
inner join tblProject TP on TWO.ProjectID=TP.ProjectID
where ' + @FILTER+'order by TP.Description'
PRINT @Query
Exec sp_executesql @Query
select * from #TempTable
WHERE RowNumber BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY Name ASC
SELECT count(*)
FROM #TempTable
drop table #TempTable
select @Query
END
|
|
|
|
|
If you are fanatic about normalization, I am sure you know the pains associated with it. Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep.
I have a script to do that. Please feel free to download and share.
|
|
|
|
|
If it's so good and useful, please write an article.
And if you run into that problem often I recommend using referential integrity.
|
|
|
|
|
I was going to curse you out but then saw that you are a legend at The Code Project, so I won't.
> Write an article: Yes the link is kinda article and the script itself.
> I recommend using referential integrity
I hope you are not confusing "on delete cascade" feature in the context of referential integrity.
Referential Integrity: NICE
On Delete Cascade: BAD (VERY BAD actually)
|
|
|
|
|
jujiro wrote: "on delete cascade"
No, I use that very seldom and only when the data is transient anyway.
|
|
|
|
|
jujiro wrote: On Delete Cascade: BAD (VERY BAD actually)
I use DELETE and DELETE CASCADE where the model requires me to do so. I find it good practice to limit the number of RI keys I'll define on a table so that I don't get into messes where all for the sake of normalization, someone has defined a row with a couple of dozen RI keys in it. To me that's just asking for a coding nightmare.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
jujiro wrote: Deletion of a row at a top level table can become a project very quickly due the child tables dependencies, which can run several levels deep.
"Cascading delete". Most modern databases do.
Bastard Programmer from Hell 
|
|
|
|
|
Yeah and most experienced devs DON'T use it. I do not recall ever using cascade delete. I do recall chastisiing a junior dev for trashing the database using cascading delete. Oops just does not cut it!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Yeah and most experienced devs DON'T use it.
They rather write queries to check whether there are child-rows, and delete them manually. I understand that people don't use what bites them - about the same brilliant idea as keeping every field a VARCHAR.
It's standard functionality of the database, and it's less error-prone than coding it manually.
I bet you're glad that I'm not allowed near your systems? 
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: I understand that people don't use what bites them
You may well be right there, the idea of cascading deletes just makes my toes curl.
Eddy Vluggen wrote: I bet you're glad that I'm not allowed near your systems
You are most certainly right there, I could see endless discussions where each tries to justify their OPOV - fruitlessly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
