MySQL Question

[Techwhore]

I've designed a database and have enter a substantial amount of data into it already but now realize there may have been a better way to accomplish what i need. I don't want to start over, so maybe there's a work around.

I have one database with 13 tables. I'm planning on scripting a PHP interface to query it and realize now that i don't know if i can query certain requests. For instance, the tables are categories of products but the manufacturer's are limited, so the same manufacturer's appear in each of the 13 tables.

One of the queries i'm going to need to do is a search by manufacturer. This can be done by scripting 13 queries, one for each table, but won't allow for sorting of any kind... right (alphabetical, id, etc...) ?

I guess what i need to know is, is it possible to query from multiple tables in the same request? I've tried a few select statements but none have worked... i'm not too savvy in SQL though.

Thanks in advance

 

[joinT]

in the SQL statement below - I select from information from 4 tables & combine it using a WHERE clause.
basically the manufacturer's names are in their own small table & I use a manuID in other tables to refer to the manufacturer.
if you inserted another AND pr.pID = #pID# (#pID# being a passed variable) you can make your querie more & more precise.

SELECT
pr.pID,
pt.pTypeName,
pr.pSubType,
pr.pManuSKU,
pr.manuID,
pr.pTitle,
pr.pDesc,
pr.pSpecial,
pr.pWeight,
pp.pCost,
pp.pPrice,
pp.pMargin,
pp.pQuantity,
ma.manuName
FROM
product pr,
product_pricing pp,
manufacturers ma,
product_type pt
WHERE pr.pID = pp.pID
AND pr.manuID = ma.manuID
AND pr.typeID = pt.typeID


in the statement below this i've used an table join statement

SELECT permissionName
FROM permissions
WHERE permissionID in
(SELECT permissionID
FROM user_permissions
WHERE userID=#userID#)

hope this helps

 

[Armitage]

Do you mean a join?

You probably need to look at your design though. About the only time you should have the same data in multiple tables is if it is the equivalent of a foriegn key.

 

[riteo]

yeah sounds like you have a design problem.

lookup database normalization

or read this article:

Database Normalization And Design Techniques

 

[SQL]

If you don't feel like a redesign right away, perhaps a UNION query would help.

Fairly simple...

select manufacturer,partid from tbl1
UNION
select manufacturer,partid from tbl2
....
select manufacturer,partid from tbl10

order by manufacturer

The order by clause in a UNION query will order across all result sets. It also strips out dupes. If you want dupes, you have to do a UNION ALL.

UNION queries are fairly flexible. Field names don't have to match, just the number of fields in the select statments need to match.

Also, the where clause are seperate for each select statement, unlike the order by statement.

 

[Techwhore]

Thanks for the help, but i'm still having some difficulty.

I tried a join earlier but it returns:

"Column: 'pname' in field list is ambiguous"

The UNION clause seems like what i need, but i don't think i'm getting the syntax right. I've tried various statements, here's just an example:

select pname from table1 where manufacturer = " " UNION select pname from table2 where manufacturer = " ";

The values for manufacturer are the same for both queries.

 

[riteo]

ambiguous column name means that 1 or more tables has same fieldname, thus mysql does not know which field to which you refer.

do this:

select t1.firstname, t2.lastname from table1 t1
inner join table2 t2 on t2.id = t1.id

 

[SQL]

What do you mean the syntax is right?

What are you trying to achieve when you do where manufacturer = " ";?

Also, what version of MySQL you running? I perused through the 4.0.0 docs and it says it now supports the long awaited UNION query. So perhaps you are running an older version that doesn't support UNIONS.

 

[Techwhore]

Ok, thanks for the help, i got it working...

However, it's not quite working right and i think a restructure is in order...

What it's doing:

select t1.pname, t2.pname from t1, t2 where t1.manufacturer = " " and t2.manufacturer = " ";

Assume t1 yeilds 4 results and t2 only 2, the outcome will look like this:

a | a
b | a
c | a
d | a
a | b
b | b
c | b
d | b

There may be a way to acheive my goals with my current setup, but i'm not THAT far into the dbase and i should restructure sooner than later... I can just dump it to a text file then edit as needed? It's tab deliniated right? Once the text file is straightened out, can i just load it back in without creating a table or should i create the table then load?