need a query

Question

Post reply

need a query

spectra

SSCarpal Tunnel

Points: 4120
More actions
September 11, 2007 at 4:18 am

#118259

need a sql query .
I have 50 tables.
all of them have a field emp_id (in some table its pk and in others its ordinary field)
i want to get records from all those tables for a given supplied emp_id .

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi there,

Do you want to find all the records that say have emp_ID = 100

You could use a UNION

You could use temp/table table variables

Does this help?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 2

"..Do you want to find all the records that say have emp_ID = 100.."

YES

so, you mean i need to use 50 times UNION and 50 times select query !

However, the sturucture of UNION is like..

SELECT Column1, Column2 FROM Table1

UNION

SELECT Column1, Column2 FROM Table2

But i may collect Column3,Column4 etc from Table2

..and this may violate the UNION syntax

As they are different tables , so i may me interested to collect diferent columns from different tables for a given emp_id=100

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

There is another option.

You could the objectID's for the tables and build a dynamic query which would only require you to type one select and then print or execute the statement from there!

does that make sense?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 4

I need SQL query for ORACLE DB though.

...plz see my above post to know what exactly i am looking at .

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 5

I want somthing smaller like this..

SELECT Employees.Name, Orders.Product

FROM Employees, Orders

WHERE Employees.Employee_ID=Orders.Employee_ID

AND Employee_ID='100'

may be something like this...i am not sure whether this will work or not in my situation.

i have many tables , and Employee_ID is not PK of all .

so i am not sure whether this will work or not

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Need more information. I am not an ORACLE user but I believe the sql is similar.

You can do 50 individual SELECT's (one per table) to get 50 result sets

You can (as Christopher suggested) use UNION to get one result set containing none or more rows. This demands the same number of columns for each select and each column needs to be of the same datatype or CAST'able to the same datatype.

You can do 50 INNER JOINs to get none or one row with data from each table

You can SELECT from one table and LEFT JOIN the other 49 to get none or one row with nulls for missing data

You can SELECT from one table and FULL OUTER JOIN the other 49 to get one row with nulls for missing data

From your last post it looks like you want to select from one table and LEFT JOIN the others

Far away is close at hand in the images of elsewhere.
Anon.