473,903 Members | 5,672 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL query on multiple tables ?

Jay
Hi everyone !

I hope someone will be able to help me with this problem.
I currently have several tables in MySQL database. Below is my table
structures.

(Data type for each fields are ignored)

user
{
uid,
firstname,
lastname,
date_added,
}

contact
{
uid, //same as "uid" in "user" table.
e-mail,
homephone,
workphone,
cellphone,
address,
city,
state,
zipcode
}
//each field in the classes table contains a grade that the user
(student) got form
//taking that class.
classes
{
uid //same id as "uid" in the "user" table.
political100,
history101,
bio109,
.....
....etc...

}

My goals include the following things:

1. Search for users(students) in a particular city/state/zipcode that
completed a certain
classes (let's say bio109 with an "A" OR history 101 with a "B" or a
combination of all classes with
different combination of "grades". How would I write a "SELECT"
statement that would accomplish this task ?

2. Please note that table contact and classes have a Primary keys that
are not "auto-increment"
The primary keys are from table "user".
I am sorry for such a lengthy message.

Any help would be appreciated ! Thank you in advance,

Jay Hana

Jul 17 '05 #1
7 10189
2nd question first:

imho you table design is flawed. what if a student has two adresses?
you'd need two entries with the same uid which is not possible if uid
is a primary key.
two ways out of that:
1. if you are SURE there's only one adress per student, than there's no
need for the second table (not good i think)
2. and better: introduce an auto increment primary key to all tables,
and use uid as a foreign key in the 2nd and 3rd table

i'd even propose another table 'subjects' where to keep the subject
names and have their id' in the classes tables.

best if read something abiout normalizing db structures, i.e.
here:http://pubs.logicalexpressions.com/P...icle.asp?ID=88

1st question: you need JOINS for that, but i'm not gonna writ an sql
statement down now, because you should fix your data structure first

micha

Jul 17 '05 #2
I noticed that Message-ID:
<11************ **********@f14g 2000cwb.googleg roups.com> from Jay
contained the following:

2. Please note that table contact and classes have a Primary keys that
are not "auto-increment"
The primary keys are from table "user".


Then there seems very little point in having three tables.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #3
Jay wrote:
Hi everyone !

I hope someone will be able to help me with this problem.
I currently have several tables in MySQL database. Below is my table
structures.

(Data type for each fields are ignored)

user
{
uid,
firstname,
lastname,
date_added,
}

contact
{
uid, //same as "uid" in "user" table.
e-mail,
homephone,
workphone,
cellphone,
address,
city,
state,
zipcode
}
//each field in the classes table contains a grade that the user
(student) got form
//taking that class.
classes
{
uid //same id as "uid" in the "user" table.
political100,
history101,
bio109,
.....
....etc...

}

My goals include the following things:

1. Search for users(students) in a particular city/state/zipcode that
completed a certain
classes (let's say bio109 with an "A" OR history 101 with a "B" or a
combination of all classes with
different combination of "grades". How would I write a "SELECT"
statement that would accomplish this task ?

2. Please note that table contact and classes have a Primary keys that
are not "auto-increment"
The primary keys are from table "user".
I am sorry for such a lengthy message.

Any help would be appreciated ! Thank you in advance,

Jay Hana


Jay,

I agree with Micha. You should look into normalizing your database.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Jul 17 '05 #4
Thanks everybody for your input.

OK, here are what I have done for my problem.

I still keep the "id" in tables "contact" and "classes" and make these
"id" fields primary keys in the tables they reside in. Would that solve
the database structure problem ?

If it solves the problem, then how would I make the "select" query on
multiple tables ?

Thanks again,

Jay Hana

Jul 17 '05 #5
I noticed that Message-ID:
<11************ **********@g14g 2000cwa.googleg roups.com> from John
contained the following:
I still keep the "id" in tables "contact" and "classes" and make these
"id" fields primary keys in the tables they reside in. Would that solve
the database structure problem ?


No.

Your data is not structured at the moment, it is simply split across
three tables.

I'm not an SQL expert but you could probably do

SELECT DISTINCT col1,col2,col3. .. FROM tbl1,tbl2,tbl3 WHERE
tbl1.id=tbl2.id AND tbl2.id=tbl3.id AND (bio109 ='A' OR history101='B')

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #6

"Geoff Berrow" <bl******@ckdog .co.uk> wrote in message
I'm not an SQL expert but you could probably do

SELECT DISTINCT col1,col2,col3. .. FROM tbl1,tbl2,tbl3 WHERE
tbl1.id=tbl2.id AND tbl2.id=tbl3.id AND (bio109 ='A' OR history101='B')


You beat me to it. This is basic SQL, and has nothing to do with PHP. I
just had to do one for myself where I did:

select a.*, b.field1 from a, b where a.id=b.id;

Shelly
Jul 17 '05 #7
it would not solve you databse problem.

the basic point is that:
a table should have a primary key as a means to identify without any
doubt any given record in that table. up to here this is totally
independend of any other table in that database.

but now the strong point of a database is that it can link records in
different tables with each other. here you use primary/foreign key
pairs, i.e. primary key of table_user becomes foreign key in
table_contact. that way it's possible to have, say, user 1 'owning'
contact records 7 and 25 (by having 1 as the foreign key in that
records) - means having two adresses. now if user decides to change
his/her name, this will not affect his adresses, you change the name in
the master record once and the rest will still be up to date.

a good way to do that is to find, well, let's call it independent data
objects: in your case something like that:
an user and his login
an user and his adress (couple of records per user)
a class
a user and his classe (couple of records per user)
the whole process of identifiying which data to put in which table and
how to link them is called 'normalizing'. i strongly advice you to have
at least some basic knowledge of that before desingning a database,
even for a small application, because they tend to get bigger and
bigger over time, and if the data is not normalized, you WILL reach the
point where your data structure cannot contain the needed data anymore.
you did in fact reach that point already: your data structure does not
allow for several user's adresses.

micha

Jul 18 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3548
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites.
6
3561
by: jacob nikom | last post by:
I would like to create data model for a group of stores. All stores in this group are very similar to each other, so it is natural to allocate one MySQL database per store. Each database is going to have very similar set of tables with content related to a particular store. Let's suppose each store has multiple departments which are also very similar to each other. In this case I would like to model the departments
0
3961
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest version of this document can be found at: http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
33
5615
by: Joshua D. Drake | last post by:
Hello, I think the below just about says it all: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg Sincerely, Joshua Drake
39
8444
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort of stammering and sputtering, and managed to pull out something I heard a couple of years back - that there was no real transaction safety in MySql. In flight transactions could be lost.
1
2837
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work. Despite entering the required lines to "my.ini" (the new my.cnf) through notepad AND MySQL Administrator, the cache does not work. So, today I took a peek at the 'Health' tab in MySQL Administrator.
3
11361
by: cdelarte | last post by:
I would like to be able to mail merge records from multiple mysql tables using a simple template, preferably via a command line script. MSWord mail merge via ODBC will not work for me as it only supports a single table. Are there any utilities or simple scripts avaialble that would accomplish this?
7
2741
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource back from the database, but get's several, or that it just checks the statement first, and decides...
14
2913
by: Ben | last post by:
I don't know whether anyone can help, but I have an odd problem. I have a PSP (Spyce) script that makes many calls to populate a database. They all work without any problem except for one statement. I first connect to the database... self.con = MySQLdb.connect(user=username, passwd =password) self.cursor = self.con.cursor() self.cursor.execute("SET max_error_count=0")
6
38550
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get through this without much trouble. Programming knowledge is not required. Index What is SQL? Why MySQL? Installing MySQL. Using the MySQL command line interface
0
10004
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9853
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10882
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10989
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10504
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9692
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7215
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4734
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3327
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.