sql syntax JOIN ....

flamegrill

I'm just working on a database here and it has several tables. The problem i have ran into is an SQL one.

I want the same fields from 2 or more different tables. I am unsure of the correct syntax to join 2 tables properly.

Any one got any ideas?

TIA

Paul

Spunj

For same fields different tables you use UNION

eg

SELECT Name, Address, Age FROM Cust
UNION 
SELECT SupName, SupAddess, Age FROM Supp

If you say UNION ALL it will give you duplicates, without it dedupes them across the fields selected.

You just need to make sure the datatypes match and the fields are in the same order.

Hope thats what you need

- Spunj

Oh and this should probably be in Programming

wish

select tableA.fieldName1, tableA.fieldName2, tableB.fieldName2
from tableA, tableB
where tableA.fieldName2 = tableB.fieldName2

This is a inner join
you can add the distinct keyword or some thing like that.

hope this helps
Rgds.
wish

ozpass

something similar...

SELECT myhead FROM Upper_Body
UNION SELECT mylegs FROM Lower_Body
UNION SELECT stomach FROM Midriff

You could then call this query, say Me

and write a second query

SELECT myhead,mylegs,stomach FROM Me

any use?

flamegrill

I'm afraid mysql seems to have kittens when i use union as does Access (i use access to access the dbs in the mysql server).

I looked throguh some info on google aswell last nite and it seems all the examples you have given should work, but they dont. I'm stumped. Not sure what to do.

Background:

The db has serveral tables which all have the same field names for easy programming , we need to show 2 of the tables concatenated together under the same names. ill try again anyways. if worst comes to worst ill just rebuild the tables.

Still not sure why union wont work thou

Paul

ozpass

This may explain things.

I used Access in a contract Job for RIC in Sydney, Australia last year (unified frontend for SQL, Oracle and Unisys Mapper databases).

I always ended up writing the UNION query (appears in the Query Tab with a little link symbol, can only be edited with SQL editor) and then using a bog standard query to display the results. I'm dredging the memory banks here, cos I don't have Access on my computer here.

Might go something like this.......e.g.

TABLE1:

[date] [fault]
1/1/2002 breakdown
3/1/2002 crash

TABLE2:
[date] [fault]
1/2/2002 explosion
3/2/2002 burn-out

(don't ask me where my weak imagination got this crap from...)

So the union query would look like this:

SELECT date,fault FROM Table1
UNION SELECT date,fault FROM Table2

Save this fella as QUERY1

Use the Access Query editor (or wizard, whatever floats your boat) to create a bog standard query that selects 'QUERY1' AS THE TABLE for each of the fields, [date] and [fault] in this example.

Running the Query should then produce

[date] [fault]
1/1/2002 breakdown
3/1/2002 crash
1/2/2002 explosion
3/2/2002 burn-out

N.B. Check the exact syntax of the 'Union Query' I've given above as i think Access uses a slightly non-standard SQL implementation. You could do this by writing any old query and looking at it with the SQL editor to check the SELECT lines. DEFINITELY adding UNION before SELECT on all lines after the initial SELECT will have the desired effect.

By the by- I find MS Access a really good, useful tool. One of the things Microsoft got right, in my view.

Slydice

I with wish on this one,

here's an example of how i would mess (can't think of a better word) with it.

SELECT a.first_table_A_variable, a.second_table_A_variable, b.first_table_B_variable, b.second_table_B_variable FROM big_table_A_name a, big_table_B_name b
where a.first_table_A_variable=b.second_table_B_variable;

<edit>
oops didn't quite understand your problem
must look up union

</edit>

Inspector Gadget

...you might need to rename some of the fields so that the UNION query matches the right fields up... to borrow from (and amend) Spunj's original example:

SELECT Name, Address, Age FROM Cust
UNION 
SELECT SupName [U]AS Name[/U], SupAddess [U]AS Address[/U], Age FROM Supp

Also, it generally helps if the fields you're trying to combine have the same (exact) data types.

Also, UNION queries DO work in Access - at least, they have for me

[EDIT]I just lashed together something quickly in Access to test this, and it works without the AS mullarkey, assuming the data types for the fields are the same, and the two SELECT queries list the fields in the order of appearance: i.e. "Name" and "SupName" first, then "Address" and "SupAddress" second, and then "Age" and "Age".[/EDIT]

Hope this helps,
Gadget

Inspector Gadget

Having read your post more carefully, one thought occurs to me; is it the fact that you're not using local tables?

I see that you're using Access as a front-end to a MySQL database on another box; this brings up an interesting problem as regards constructing the queries - should you specify that the queries are pass-through?

(If you're working with tables in another database and specify that the query is "pass-through", Access won't try to execute the query itself; instead, it passes that task on to the database actually serving up your data (in this case, mysql) and displays the result it returns. If you don't state the query is pass-through, it'll try to perform the query locally, which will involve sucking down the tables in question from mysql.)

Another thing is: are you using more than two tables? A UNION construct only allows for two tables to be joined; you may have to resort to judicious use of round brackets, or having to split the query up (i.e. UNION two tables, save that query in Access, and then UNION a third table with that query - the result of the previous UNION - and so on)

It could even be something as stupid as Memo/Object (aka LONGTEXT, IMAGE, BLOB) fields - Access can't do certain operations when one or more fields are of this type.

...maybe if you were more specific, and could provide a slightly more concrete example, we might be able to get further with this....

Gadget

flamegrill

OK here we go. Some more infomation.

I have 8 tables so far. They all have the same field names, making thinks nice and easy to sort through and to make it nice and uniform. I do only need to join 2 of them. there are 2 categories which come under one Major heading. I need to be able show both tables in the major category. When i was designing the database I didnt forsee that we would need to group our different tables into one output. I cant actually show you, client confidentiality and that :P.

To be honest i can just run 2 queries and have em both showing on the same resulting page. But it would be nice not have to redo some of the code to allow just one change

this is what i tried on the same tables (in a non linked actual access db with the same info) and it worked perfect.

SELECT bar_code, desc, trade_price  FROM belkin_prod
UNION 
SELECT bar_code, desc, trade_price  FROM pc_access;

it will not however work on the linked mysql tables or using the command line mysql client. I really cant figure it out to be honest. it works perfect in Access on a normal db.

I'm at a loss here....

Help

Paul

flamegrill

from http://www.mysql.com/doc/en/UNION.html

UNION is implemented in MySQL 4.0.0 and the current stable release of mysql is far from this

should have looked before to be honest.

Inspector Gadget

...it occurs to me that if your database contains two tables - actually, it's eight, isn't it? - with the same fields, then your database isn't properly normalized anyway, which will make searches, reports and the like a pain in the rear. On that point, is your database considered "production" yet (i.e. is it actually being used to store important data day-to-day) or is it still in development?

Gadget

ozpass

You could just build it in Access for the time being and then export it to some flavour of SQL later. Not exactly industrial strength, but at least it'll work.

Spunj

My suggestion is to put all the data into the one table and add a Category Field of some sort. Then just query where Category=X AND/OR Category=Y. The way you have it with multiple tables is a recipe for disaster, no matter how 'nice and uniform' it is. This will allow you or your users to query easily across categories, do sorts statistical counts etc.

As the tables have all the same fieldnames this should be really easy. Is there some reason why you need to keep the tables seperate?

Spunj

Inspector Gadget

Originally posted by Spunj
My suggestion is to put all the data into the one table and add a Category Field of some sort. Then just query where Category=X AND/OR Category=Y. The way you have it with multiple tables is a recipe for disaster, no matter how 'nice and uniform' it is. This will allow you or your users to query easily across categories, do sorts statistical counts etc.

As the tables have all the same fieldnames this should be really easy. Is there some reason why you need to keep the tables seperate?

Spunj

...this is what I was saying, except that I wasn't going to go into specifics unless drawn upon to do so... however, seeing as I'm posting anyway, I'll suggest that you use numeric (integer) values for the category field and then have a second table cross-referencing that numeric value (long integer is a good choice) to a text-based equivalent - for example:

Category_ID    Name
=========      ====
1              PC Access
2              Belkin Products

...and so on. The reasoning behind this is twofold:

1. Instead of storing a long string for each product to denote its category, you are instead storing just a number - four bytes as opposed to maybe 80. Over a lot of products this can make a huge difference in terms of database size and speed.
2. It's a lot easier for a program to mis-record a text string in a field instead of a number - say one routine saves your category as "Belkin Products" and another saves another record as "Belkin_Products"?

As someone once said (and I'm about to paraphrase), database theory is just common-sense written down. Try to put things in such a way that every unique chunk of data (bearing in mind that a "unique chunk of data" can span much more than one field) is recorded just once and you're on to a winner.

Hope this helps,
Gadget

ozpass

It's a fair point, actually. The whole point of relational databases is to unify the core data, with repetitive stuff 'farmed out' to related tables, avoiding unnecessary duplication.

The only time I've found the need to split things into separate tables was for hardcore statistical biz. with multiple queries running on other queries, which can take 'weeks' for large datasets. Multiple tables=smaller datasets.

If your problem hinges on the use of UNION queries then your database design is probably too complicated.

Do a manual 'UNION' query by putting all the data into one table.

Spunj

Gadget - Thats what I was hinting at, but I didn't want to expand on it too much till he had said whether or not he will be 'allowed' to make these changes

Database design and maintenance, in the real world, can be some distance away from the ideal 3NF book version. We are often forced to denormalise for performance reasons. As you say, its common sense written down, but I have often found I have to throw common sense to the wind and experiment till I get the correct balance of performance v's maintainability.

(Still I have never had to keep seperate copies of the 'same' table for long periods. Often have to select subsets to memory/temp tables tho.. I do day to day SQL work with tables that can be > 10million records so I have to spend a certain period of the day in the 'banging head against wall ' position )

Spunj

flamegrill

Firstly thank you to everyone

Secondly the database is in development to changes are bound to happen. Also the tables have a categorey field in them already, becase say belkin do loads of different products like networking stuff, usb and usb2 stuff and cables. So as it is we pick those items where

sub_cat='adapter'

. So to go for one big table id have to come up with another categorey and means fiddling with more varables. Ok its not a bad idea, but i thought by keeping the different manufacturers in different tables that id be on a winner. To implament a seach through this database wouldnt be to hard, simple because the person searching would pic a particular manufacturer to search in.

I'm still not sure what to do to be completely honest and i have more tables to convert from excel sheets :P (silly companies not using databases for this type of information is mental).

So any suggestions for a proper setup and or just changes to the existing layout would be good.

Also just outline specific questions that u may want me to answer. Makes it easier for me to answer your questions

Using an Access database is not an option, temporily or not.

Regards,

Paul

(keep up the suggestions and ideas)

Inspector Gadget

If you're desperate to get the data together for a one-off merged copy, you could do something like:

1) Create a new table whose fields matched the first table of those
you want to merge in terms of data types:
CREATE TABLE temptable (<field1 name> <field1 type>, <field2 name> <field2 type> ...);

2) Copy in the stuff from the first table [EDIT]This syntax is fine as long as you list the fields below in the order they appear in temptable[/EDIT]:
INSERT INTO temptable SELECT <field1> AS <equiv. field in temp table's name>, <field2> AS <equiv. field in temp table's name> ... FROM <table1 name>;

3) Rinse and repeat for second table:
INSERT INTO temptable SELECT <field1> AS <equiv. field in temp table's name>, <field2> AS <equiv. field in temp table's name> ... FROM <table2 name>;

4) ...and so on for any other table(s) you'd like.

5) Delete the temp table when you're finished...
DROP TABLE temptable;

Now, I'd only recommend this for something you're doing as a once-off, as it won't reflect any changes made to the tables the data was drawn from after those tables are imported into the temporary table. If you need to do this again and again as part of an automated process, eeewwwww. Don't even consider it. :eek:

Gadget

Spunj

To implament a seach through this database wouldnt be to hard, simple because the person searching would pic a particular manufacturer to search in

This is where your main problems will lie with your current approach. What happens when one of the big wigs wants do search through ALL the tables for products across different manufacturers? Answer is you are in the same position you are in now, but with a shorter time to get it right

Do exactly what gadget said :

Category_ID    Name
=========      ====
1              PC Access
2              Belkin Products

but change CategoryID to ManufacturerID and have your seperate look table with a ManufacturerID ,Manufacturer Name, address, Etc etc (which you probably have somewhere already?).

This will allow you to have eg a combo box with the manufacturer names and allow someone to check the ones they want to apply any particular query to. You will just gather the ID's for them and query like
:

SELECT BLAH from Products where ManufacturerID = 100 OR 
ManufacturerID = 102 AND sub_cat='adapter' 

(sub_cat='adapter' taken from your prev post)

As you can't give us too many specifics it's hard to come up with specific answers, so I'd recommend setting up a seperate test DB and implement it alongside for now, and see what problems it brings up. We can answer those more easily.

Spunj

Inspector Gadget

The reason why merging all the product tables into one and then using categories is a good idea probably needs further explaining.

The biggest single stumbling block with your approach is knowing what product categories you have. From what you've posted, we can surmise you've currently got eight, of which two are "PC Access" and "Belkin Products".

Now, it appears to me that what you're planning involves the user picking out the category/manufacturer/whatever-it's-called from a list and then using that to determine which table to search.

It occurs to me that there are two ways of keeping a list of what items (category/manufacturer/whatever-they're-calleds):

1. Hard-coded in the database's front-end, or
2. Stored in a table

Now, if you're taking the first approach, it means you have to change your front-end every time a product category is added, removed or renamed; this is madness. Believe me, I know :rolleyes:

If, however, you're going down the route of using a table, why not use the suggestion I've made above (namely sticking all the data into one table, setting a category/manufacturer/whatever-it's-called field for each product to a value corresponding to the correct category/manufacturer/whatever-it's-called, and leave open the possibility of:

• Using Boolean and other logic (AND, OR, NOT, <, >, IN etc.) to perform powerful queries in a relatively straightforward manner
• Be able to make sweeping changes to the product data without having to go through hell and keep everything up to date (ever considered what would happen if a company changed it's name (as they are wont to do)? Would you rename the table, and consequently edit your UI and possibly your queries, or leave it alone, meaning that you're scratching your head in a year's time saying "What the hell's xxx?")

Does this help?
Gadget