select in star schema

mrx100

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[^]

phil o

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.

Mycroft Holmes

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

mrx100

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

mrx100

don't download image just click on preview on medifie

Lost User

Having a long query doesn't count as a problem. Why is it classified as such? Does it not work?

Bastard Programmer from Hell :suss:

Luc Pattyn

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. :|

Luc Pattyn [My Articles] Nil Volentibus Arduum

Mycroft Holmes

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

Bernhard Hiller

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.