sql query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql query help

Author

Topic

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-05 : 17:01:00

Hello guys, I will explain u exactly what
I am looking for,
There are 4 different cities and each city has 2 access tables from
which I need to pull my data, I had written a query which selects teh
required data from one city, now each city has different business ID
and different data to pull out, like for example, when a user enters a
BID 16139, say it belongs to city A, hence it shld pull in the adress,
license num, phonenum from teh table(thats in city A), and then if he
enters say 17890, which belongs to city B, it shld pull out adress,
license num, phonenum from city B. Now my question is that, I have
written a query which works for one city with 2 tables, I want to know
how do I combine all teh cities so that when a user enters a BID he
would get the required data from the related table and not include
anyother data from any other city. here
is the query that i had written

SELECT dbo_businessNC.bus_name, dbo_businessNC.adrs1,
dbo_businessNC.adrs2,
dbo_businessNC.city, dbo_businessNC.state, dbo_businessNC.zip,
dbo_businessNC.phone, dbo_licenseNC.license
FROM dbo_businessNC, dbo_licenseNC
WHERE (((dbo_businessNC.bus_id)=[Forms].[Form1].[text2] And
(dbo_businessNC.bus_id)=[dbo_licenseNC].[bus_id]));

Now I want to add in these queries too,
SELECT [Hawthorne Acc summary].[Account Name]
FROM [Hawthorne Acc summary]
WHERE (([Hawthorne Acc summary].[ID]=[Form].[Form1].[Text2]));

SELECT [Martinez Business License Master]. [ID] FROM [Martinez
Business License Master] WHERE [Martinez Business License Master].[ID]
= [Form].[Form1].[Text2];

So I am not sure how to combine both these queries to get teh right
data to get selected.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-06 : 09:33:32

1) Why do you have the same data (Address) in different tables?

2) Are the city tables identical in structure? If so, why don't you combine them into one table?

3) How are you enforcing uniqueness of bid numbers across city tables?

4) The lazy (excruciatingly painful) answer is to create a subquery comprised of a union of all the city tables and them pull the address from that virtual table.

5) The real answer is to combine these tables into one table and then simply pull the address from that table.

HTH

=================================================================
None are more hopelessly enslaved than those who falsely believe they are free. -Johann Wolfgang van Goethe, poet, dramatist, novelist, and philosopher (1749-1832)

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-06 : 14:48:01

Hey Kool, Thanks for your reply, I shall explain in detail what exactly I am looking for, now I have 4 different cities, each with 2 tables(access), liek this

CityA
Table1 Table 2
BID MailAddress Telephonenumber BID Licensenumber Transactionamt

CityB
Table1 Table 2
BID MailAddress Telephonenumber BID Licensenumber Accountnumber

The other 2 cities are similar as above, now I created a form and if I enter a BID the concerned data should show up, like if i enter a BID which belongs to city A then the mail adress, telphoen number and transaction amount should show up and if I enter a BID which belongs to cityB, then the related info should show up, now I have written a query for cityA and is working just fine, but I am not sure on how to combine all the 4 cities in one query, I cannot put everything in one table as some of the fields are different, and when a user enters a BID, he doesnt know to which city that BID belongs to. Some of em have told me to use 5 different forms and soem have told to use union, I am not relly sure as to how to use it, if you could help me out I would be greateful, thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-06 : 15:55:06

[CODE]
select BID, CityName, MailAddress
from (
select BID, 'York' as CityName, MailAddress
from York

union all

select BID, 'New York' as CityName, MailAddress
from NewYork

union all

select BID, 'Dehli' as CityName, MailAddress
from Dehli

union all

select BID, 'NewDehli' as CityName, MailAddress
from NewDehli
) OneWorld
where BID = @BID
[/CODE]

I still think that if you look at the data you could combine the separate city tables into one table. Food for thought...

HTH

=================================================================
None are more hopelessly enslaved than those who falsely believe they are free. -Johann Wolfgang van Goethe, poet, dramatist, novelist, and philosopher (1749-1832)

X002548
Not Just a Number

15586 Posts

Posted - 2005-07-06 : 16:04:02

CREATE VIEW viewName
AS...

Then just reference the view...if it's a lot of data you can create a partitioned view.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-07-06 : 16:07:10

Please do not post the same Question more than once.

Jim
Users <> Logic

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-07 : 17:00:18

Hey Kool, thanks for your reply, its working as I wanted. thanks again.

Amit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources