[RESOLVED] two database records, one output

robkir

Here's a brain twister (at least for me)

I have two databases 1) Buildings and 2) Suites

Buildings look like this:

building_id | building name
01 | Orange County office
02 | Long Beach office

Suites look like this:

building_id | Suite_no | Rate
01 | 102 | $235
01 | 103 | $800
02 | 009 | $485
02 | 012 | $300

Here's the problem. The user will SELECT suites to make changes or look up rate. I want Suites to access Buildings and see what the building name is based on the building_id.

I always seem to get stuck on problems that I can't think of how to phrase the question in order to search internet. Please point me in the right direction.

Thanks,

robkir

bradgrafelman

For the best, most optimized solution, we first need to clarify the vocab you used here; it looks like you showed us two separate tables, not databases; are both of these tables (buildings and suites) contained within the same database?

If they are, and assuming one table is named 'buildings' and the other 'suites,' this query should give you what you want:

SELECT Suite_no, Rate, building_name
FROM suites s
    LEFT JOIN buildings b
    USING (building_id)

The syntax of the query was constructed with the assumption that you were using MySQL as your DBMS; mileage may vary with other servers.

robkir

My bad (shows you how new I am to this stuff). I should have said Tables, not databases. And yes, the tables are contained in the same database.

bradgrafelman

Ah, that's what I had assumed. In that case, take a look at the example query I added above.

EDIT: Note that if you actually want the building_id returned as well, you could add 's.building_id' to the column list; I don't recall for sure, but I believe if you simply added 'building_id' then MySQL would spit out an 'ambiguous column' (or something similar) error message.

robkir

thanks. Let me fool with it and see if I can get it to work.

I appreciate your help.

robkir

bradgrafelman

No problem; feel free to post back if you run into any problems.

Also note that you could get rid of the 's' and 'b' table aliases; I had originally set out to use the "ON xxx = yyy" syntax, but then realized that since both columns had the same name in the two tables (in your example, at least) the 'USING' syntax was easier/less typing. :p

robkir

bradgrafelman it worked perfectly.

Thanks for the clear instructions. I'm sure I'll find another hole to fall into soon...but for now I am a happy camper (er...programmer).

robkir

bradgrafelman

Glad I could help. Don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu.