0

I have a MySQL database with three tables: sample, method, compound.

sample has the following columns: id(PK)(int), date(date), compound_id(int), location(varchar), method(int), value(float)

method has the following columns: id(PK)(int), label(varchar)

And compound has: id(PK)(int), name(varchar), unit(varchar)

I am trying to generate a SQL command that only pulls in the unique row for the following criteria:

  • Date (sample.date)
  • Compound Name (compound.name)
  • Location (sample.location)
  • Method (sample.method)

However, I want to substitute in the labels for some of the sample columns instead of the numbers:

  • sample.compound_id is matched to compound.id which has a corresponding compound.name and compound.unit

The first SQL command I tried to query was:

SELECT sample.id, sample.date, compound.name, sample.location, method.label, sample.value, compound.unit
FROM sample, compound, method 
WHERE sample.date = "2011-11-03"
AND compound.name = "Zinc (Dissolved)"
AND sample.location = "13.0"
AND method.id = 1;

The output from the above command:

id      date        name            location    label       value   unit
1       2011-11-03      Zinc (Dissolved)    13.0        (1) Indivi...   378.261     μg/L
5       2011-11-03      Zinc (Dissolved)    13.0        (1) Indivi...   197.917     μg/L
9       2011-11-03      Zinc (Dissolved)    13.0        (1) Indivi...   92.4051     μg/L

But when I look at sample and compare sample.id to what was returned:

id      date    compound_id     location     method     value   
1       2011-11-03  13          13.0         1          378.261
5       2011-11-03  14          13.0         1          197.917
9       2011-11-03  47          13.0         1          92.4051

Where compound.id 47 corresponds to compound.id 47 and compound.name "Zinc (Dissolved)". Compound IDs #13 and #14 are "Copper (Dissolved)" and "Copper (Total)", respectively.

So it seems to be returning rows that meet the criteria for sample.date and sample.location without regard to compound.name. Given the above criteria, I know that my database should only return one row, but instead I get some sample.id rows that have a completely different sample.compound_id than the matching compound.name that I specified.

I would like to end up with the columns that are SELECTed in the first line to end up in the same order as I wrote them. This code is for a little database viewer/reporter program I'm writing in Python/Tkinter and relies on the columns being uniform. The code that I use to initialize the data for the program works as I expect:

SELECT sample.id, sample.date, compound.name, sample.location, method.label, sample.value, compound.unit
FROM sample, compound, method 
WHERE sample.compound_id = compound.id
AND sample.method = method.id;

Which puts out each unique line in sample with the substitutions for sample.compound_id to compound.name and sample.method to method.label and adds in the compound.unit at the end.

Question #1: How do I need to restructure my query so that it only returns the row that meets that specific criteria?

Question #2: Eventually I'm going to need to specify multiple sample.locations at one time. Is that as simple as adding an OR statement for each individual location that I need?

qmoog
  • 205
  • 2
  • 13
  • For first query - the one that is not meeting criteria - missing Join on info like sample.comapund_d = compound.id. Also you might have to check spelling for compound.name values or consider to use compound.name LIKE 'Zinc%' – Anda Iancu Jan 24 '13 at 17:16
  • Anda Iancu: I'm a SQL-dummy. I'm not sure how the INNNER JOIN syntax would work with this amount of criteria. – qmoog Jan 24 '13 at 17:21

2 Answers2

2
SELECT sample.id, sample.date, compound.name, sample.location, method.label, sample.value, compound.unit
FROM sample 
INNER JOIN compound ON compound.id = sample.compound_id 
INNER JOIN method ON method.id = sample.method
WHERE sample.date = '2011-11-03'
AND compound.name = 'Zinc (Dissolved)'
AND sample.location = "13.0"
AND method.id = 1;
Anda Iancu
  • 530
  • 1
  • 3
  • 9
1

Now that I have the first question figured out, I figured out my second question:

SELECT sample.id, sample.date, compound.name, sample.location, method.label, sample.value, compound.unit
FROM sample 
INNER JOIN compound ON compound.id = sample.compound_id 
INNER JOIN method ON method.id = sample.method
WHERE sample.date = '2011-11-03'  
AND compound.name = 'Zinc (Dissolved)'
AND sample.location IN ("13.0", "22.0")
AND method.id = 1;

And just keep adding ORs inside the brackets for each other location.

Anda Iancu
  • 530
  • 1
  • 3
  • 9
qmoog
  • 205
  • 2
  • 13
  • use IN clause: AND sample.location IN ("13.0", "22.0") – Anda Iancu Jan 24 '13 at 17:34
  • Anda Iancu: It looks like both methods work. Is the IN clause considered more appropriate (and probably easier to read) SQL? – qmoog Jan 24 '13 at 17:38
  • Yes both methods work -is a preference (or easy to read) - if you have like 200 locations let say 200 OR statements; http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance – Anda Iancu Jan 24 '13 at 17:45