All right, I don't know how to ask this, so I'll just post the query and see if someone can help make it work.
I'm using Access, if that helps. I would post the tables but I don't know how, so if someone can explain how to do that I'll post those too.
SELECT Pokemon.SpeciesID, Pokemon.FormID, SpeciesName,
Type.TypeName AS Type1 WHERE Type.TypeID = SpeciesType.TypeID AND SpeciesType.IsPrimaryType IS true,
Type.TypeName AS Type2 WHERE Type.TypeID = SpeciesType.TypeID AND SpeciesType.IsPrimaryType IS false,
FROM Pokemon, Type, SpeciesType;
I know this is wrong, I need the WHERE parts after the FROM, but I can't figure out how to make that work.
Yes, I have to select type twice, there is very literally no way whatsoever for me to not have to do that. There's a many-to-many join table (dunno what to call this) that has the TypeID, SpeciesID, and an indicator of whether that row is the species's primary type or not. I need to select those with that as true AND those with that as false within one query.
(This is related to a school project.)
EDIT: Since nobody's explaining how to post a table, here's a screenshot of the relationships, doctored to highlight what's relevant.
IsPrimaryType is a yes/no column. I need to select the Yes as Type1 and No as Type2.
Perhaps you can use the Switch function in your SELECT statement?
SELECT Pokemon.SpeciesID, Pokemon.FormID, SpeciesName, switch( SpeciesType.IsPrimaryType IS true, Type.TypeName, true, '' ) AS Type1, switch( SpeciesType.IsPrimaryType IS false, Type.TypeName, true, '' ) AS Type2
Read more about the Switch function here: http://office.microsoft.com/en-sg/access-help/switch-function-HA001228918.aspx
ok so here goes....please note as i Dont know what you table or fields are this is a very VERY rough guide
SELECT Pokemon.SpeciesID, Pokemon.FormID, SpeciesName, Type1, Type2 FROM Pokemon INNER JOIN pokemon.ID = speciestype.id INNER JOIN typetable.id = pokemon.id
as you can note select all the fields from all the tables that you need.
next join you 1st table to you second, they need a relationship, in this case we will simply say i want only the records that match, on this tables id and the others ID (IF THAT IS THE CASE, thats for you to work out which fields are linked)! do that same again and bingo
next you may need to filter, so add the WHERE clause and filter, i want only where type = true.
So you would say,
WHERE yourtable.type = 'true'
notice its in string format as i don't know what your field type is.
UPDATE: also the extra info given you should be able to deduce whats needed, we could do this for you looking at all the information. BUT as this is an assignment I would take the guidance from here, and google and figure the rest out, best way to learn is to do!
Good luck !