Very Off-topic, but I think this community is the sort that could help me out!
I'm doing an MSc and part of my project requires me to build a database of various genomes and be able to filter it for various bits of information. I'm trying to do this with sql scripts. I have no computing experience so this is all knew to me. I'm mostly googling business sql forums to see what scripts can be transferred but I've run into a problem I can't find a fix for elsewhere.

This is the current script I'm working on:

SELECT * FROM "Gar" JOIN "Chi" ON "Gar"."Gar Key" = "Chi"."Chicken Key" JOIN "Hum" ON "Gar"."Gar Key" = "Hum"."Human Key" WHERE "Chi"."Orthology confidence [0 low, 1 high]" = '1' OR "Chicken Ensembl Gene ID" IS NULL AND "Hum"."Orthology confidence [0 low, 1 high]" = '1' OR "Human Ensembl Gene ID" IS NULL ORDER BY "Gar"."Ensembl Gene ID"

I have three tables: Gar, Chi, and Hum. I need to take information from Chi and from Hum independently and add them to the Gar table. Unfortunately they are interfering with each other. I want gar plus Chi when orthology =1 or when Gene ID is Null. Separately I want Hum when orthology=1 or when Gene ID is Null.
At the moment I'm getting Chi orthology=1 AND Chi orthology=0 when Hum orthology=1.

Ideally I want Gar Join Chi where . . . . . AND Gar join Hum where . . . . . But I can't find the right syntax to stop Chi and Hum from interfering with each other.

When you say script you mean statement. Right?
I will suggest you to first make small tables on paper then try the statement.

Hi,

Yes, probably not the right forum for this :o) So, am working without test data here and trying to understand what the requirements are. What I would do is to step through your requirements, one by one building up the code as you go. So GAR is the primary table:
select A.*
from GAR A

2) Now you want to join CHI to it, but is there a restriction? What type of join are we talking about? Attached is a picture which describes visually what each type of join does in terms of taking data from each table. Say I want all the records in GAR, and only those in CHI where they match GAR - this is a left join - and that only records from CHI where ORTHOLOGY is 1:
select A.*,
B.ORTHOLOGY
from GAR A
left join (select * from CHI where ORTHOLOGY=1) B
on A.<linking var>=B.<linking_var>

So in the above, I haven't put the linking_vars, you can add these in as per your data (this is where working without test data is difficult). Secondly I introduce the concept of a sub-query, between the brackets is a query in itself, which restricts the data coming into alias B with only 1 values.

3) Now am going to assume the above is also true for HUM:
select A.*,
B.ORTHOLOGY,
C.<some variable>
from GAR A
left join (select * from CHI where ORTHOLOGY=1) B
on A.<linking var>=B.<linking_var>
left join (select * from HUM where ORTHOLOGY=1) C
on A.<linking_var>=B.<linking_var>

Now the above is not the "best" way of coding it, however it is easy to read and follow the logic, which is what I am trying to get across. Its the process that matters, think about the data, how you bring it into the statement, how it is to link with the other data, this is why a lot of packages come with an SQL Plan to visually show this type of thinking.

Hope that helps you. A final resource for you is W3Schools SQL page, which is quite helpful:
http://www.w3schools.com/sql/

It would be much helpful if you provide us table with column names and few data.
As what you are asking is extremely easy but with NO Data it makes programming messy.

You may also use microsoft office access for database.

I took a crash course on SQL when i first started a computer job, got much better after they let me go, but haven't really used it so i'm a bit rusty...

However, you need to have some unique way to reference the data between tables. If you can't identify a easy way to make them unique or link them together, that's probably going to be your problem.

I'm not sure what Gar Chi and Hum are exactly, but you have 4 types of relationships between tables.

1 to 1
1 to Many
Many to 1
Many to Many

The 1 to 1 is the easiest as they have unique relationship and work great as a natural join, especially if the key ID is the same.

So there's the question when you'd use each of these. Well 1 (totally unique) is when there's only 1 of them. This goes for individual products, individuals, id's for something like notes, etc.

1 to Many: Think of a zip code. Thousands of people will have the same zip code, but the zip code always corresponds to a specific city/state. Also useful for addresses, cell phones, family members, etc.
Many to 1: The reverse of above.

Many to many: Sorta the same...


I'd recommend reading Learning PHP, MySQL, and JavaScript. This book with the MySQL chapter has an excellent crash course on MySQL, relational databases, and how to help optimize and break them apart.

If you can't afford it or you have to do something now, you can also look at W3School's site on Databases which has a little database you can play with for practicing your queries as well (read-only probably).


I haven't read too much of nightcraw1er.488's post but at a glance he's probably posted some good data. Which is why i commented why i didn't know about Gar Chi and Hum that he has listed. I'd offer better examples myself but as i think about it, there's better places that have it already better explained than what i could do.

http://www.urbandictionary.com/define.php?term=gar
Gar table.. lol sorry XD

*today Gog doesn't like my links..

Nah. You just have to do it a specific way, like this.

This device maybe auto-adds links, so.. nope :P

The tablet, right? I see...

Only if the only data you're trying to get is from fields.

Actually as a note, you can usually shorten the identification of a table by giving it a alias immediately after calling it. So...

[code] SELECT * from Chi C [/code]

C can then be used any time you'd refer to the whole table by name. This is quickly useful later when telling it how to attach and compare the table's unique fields so it can align and pull from them appropriately.

BTW, I'm using openoffice Base for this.
I think Wishbone is on the right idea. I now have:

SELECT * FROM "Gar" JOIN "Chi" ON "Gar"."Gar Key" = "Chi"."Chicken Key" WHERE "Chi"."Orthology confidence [0 low, 1 high]" = '1' OR "Chicken Ensembl Gene ID" IS NULL UNION SELECT * FROM "Gar" JOIN "Hum" ON "Gar"."Gar Key" = "Hum"."Human Key" WHERE "Hum"."Orthology confidence [0 low, 1 high]" = '1' OR "Human Ensembl Gene ID" IS NULL ORDER BY "Gar"."Ensembl Gene ID"

Or if this is easier to read:
SELECT * FROM "Gar"
JOIN "Chi" ON "Gar"."Gar Key" = "Chi"."Chicken Key"
WHERE "Chi"."Orthology confidence [0 low, 1 high]" = '1' OR "Chicken Ensembl Gene ID" IS NULL
UNION
SELECT * FROM "Gar"
JOIN "Hum" ON "Gar"."Gar Key" = "Hum"."Human Key"
WHERE "Hum"."Orthology confidence [0 low, 1 high]" = '1' OR "Human Ensembl Gene ID" IS NULL
ORDER BY "Gar"."Ensembl Gene ID"

But now rather than getting a syntax error,I am getting a "The given command is not a select statement" error when I try to save it.

I know. He's new at this, I'm trying not to overload him with information all at once.

I wrote my first piece of script this morning : )
I'm working off some base code which I re-purposed for my needs, and now I'm trying to build upon it. I'd like to take the time to learn from a more holistic perspective but this project is very urgent and no-one else in my lab can do it (I can't either but oh well).

When you do a union, the fields you select have to be exactly identical, so if the Chi and Hum tables have different column names and data types, you can't do it this way. If only the column names differ, you can override them like this:
SELECT
Gar.field1,
Gar.field2,
Chi.chifield1 AS CommonField1,
Chi.chifield2 AS CommonField2
FROM Chi
JOIN Gar on Chi.var = Gar.var
WHERE Chi.Orthology = 1
OR Chi.GeneID IS NULL
UNION
SELECT
Gar.field1,
Gar.field2,
Hum.humfield1 AS CommonField1,
Hum.humfield2 AS CommonField2
FROM Hum
JOIN Gar on Hum.var = Gar.var
WHERE Hum.Orthology = 1
OR Hum.GeneID IS NULL

This does require that Chi.chifield1 and Hum.humfield1 have the same data type.