search from multiple tables

test 09

Hi all, This is how im getting columns from 3 tables....

select projectID=projects.projectid,projectname,
[image],country,county,ClientCompany,city,ConstructionCompletionDate_dt,
OverallBuildingSize_uts from projects, schedule,BuildingSiteStatics
where projects.projectid=schedule.projectid and projects.projectid=BuildingSiteStatics.projectid

I have searched from 2 tables using following query

select a.*,b.ConstructionCompletionDate_dt from projects a join
schedule b on a.projectid=b.projectid where projectname like '%%' or
country like '' or clientcompany like '%%' or
ConstructionCompletionDate_dt like '%%'

Now i need to search columns from 5 tables How can i do it....

www Developerof NET

test-09 wrote:

Now i need to search columns from 5 tables How can i do it....

Obviously, you need to join the remaining tables with your existing ones and filter the data from the corresponding columns.

When you fail to plan, you are planning to fail.

Mycroft Holmes

As said, jion all 5 tables. If you expect to reuse the 5 table join in other queries I would create a view, makes life much simpler.

Never underestimate the power of human stupidity RAH

test 09

select projectID=projects.projectid,projectname,
[image],country,county,ClientCompany,city,ConstructionCompletionDate_dt,
OverallBuildingSize_uts from projects, schedule,BuildingSiteStatics
where projects.projectid=schedule.projectid and projects.projectid=BuildingSiteStatics.projectid

using above query how can i join 5 tables..

Mycroft Holmes

This query uses what I call Oracle joins where the links are in the where clause, never to be seen in any database I own. Use the propert join methods. Here is a sample from a view I have

FROM dbo.FactConsoTB AS F
INNER JOIN dbo.Branch AS B ON F.BranchID = B.BranchID
INNER JOIN dbo.lnkAccountProduct AS L ON F.APLinkID = L.APLinkID
INNER JOIN dbo.Account AS A ON L.AccountID = A.AccountID
INNER JOIN dbo.Product AS P ON L.ProductID = P.ProductID
INNER JOIN dbo.Currency AS C ON F.CurrencyID = C.CurrencyID
INNER JOIN dbo.Currency AS BC ON F.BaseCurrencyID = BC.CurrencyID
INNER JOIN dbo.ProductCategory ON P.ProductCategoryID = dbo.ProductCategory.ProductCategoryID

test-09 wrote:

select projectID=projects.projectid

Why are you useing this.

Never underestimate the power of human stupidity RAH

test 09

using view i tried this but im getting error "Ambiguous column name 'ConstructionCompletionDate_dt'" what wrong im doing....

SELECT dbo.Projects.ProjectID, dbo.Projects.ProjectName,
dbo.Projects.City, dbo.Projects.County, dbo.Projects.Country,
dbo.Projects.ClientCompany,
dbo.BuildingSiteStatics.OverallBuildingSize_uts,
dbo.Schedule.ConstructionCompletionDate_dt,
dbo.OverallDevelopment.TotalNumberDwellings

FROM dbo.Projects INNER JOIN
dbo.BuildingSiteStatics ON dbo.Projects.ProjectID = dbo.BuildingSiteStatics.ProjectID INNER JOIN

                  dbo.OverallDevelopment ON dbo.Projects.ProjectID

= dbo.OverallDevelopment.ProjectID INNER JOIN

                  dbo.Schedule ON dbo.Projects.ProjectID = dbo.Schedule.ProjectID where projectname like '%%' or country like ''

or clientcompany like '%%' or ConstructionCompletionDate_dt like '%%'

Mycroft Holmes

Prefix the column name with the tablename or the alias. Also try Google and BOL, the response time is generally quicker and gentler.

Never underestimate the power of human stupidity RAH

Lost User

test-09 wrote:

Now i need to search columns from 5 tables How can i do it....

One alternative would be to build some views, consisting of a tablename, columnname and a varchar representing the value. Create a union of those views to search all columns with a single select-statement and the LIKE operator. What database are you using? If you're on Sql Express, have you considered using the Full Text Search[^] component?

I are Troll :suss: