one-to-many need 1 row, not all

[SoreGums]

+---------+
| TableA |
+---------+
| ID |
+---------+
| 1 |
+---------+

+--------------------------------------+
| TableB |
+---------+---------+------------------+
| R_TA_ID | Key | Value |
+---------+---------+------------------+
| 1 | Company | Good Fun Pty Ltd |
+---------+---------+------------------+
| 1 | John | Name |
+---------+---------+------------------+
| 1 | Role | Partner |
+---------+---------+------------------+

Query:
SELECT `TableA`.`ID`, `TableB`.`Value`
FROM `TableA`
LEFT OUTER JOIN `TableB`
ON `TableA`.`ID` = `TableB`.`R_TA_ID`
WHERE (`TableB`.`Key` = 'Role' AND `TableB`.`Value` = 'Partner') OR `TableB`.`Key` = 'Company'

+-----------------------+
| Current Result |
+----+------------------+
| ID | Value |
+----+------------------+
| 1 | Good Fun Pty Ltd |
+----+------------------+
| 1 | Partner |
+----+------------------+

+-----------------------+
| Wanted Result |
+----+------------------+
| ID | Value |
+----+------------------+
| 1 | Good Fun Pty Ltd |
+----+------------------+

Mysql 5
How do I get the wanted result?

Thanks :)

[bubbisthedog]

For what reason are you wanting to filter out Partner from your results? Your SQL clearly states that such a result will be returned; that is, you're doing nothing to prevent it from being returned.

[drallab]

You have:
SELECT `TableA`.`ID`, `TableB`.`Value`
FROM `TableA`
LEFT OUTER JOIN `TableB`
ON `TableA`.`ID` = `TableB`.`R_TA_ID`
WHERE (`TableB`.`Key` = 'Role' AND `TableB`.`Value` = 'Partner') OR `TableB`.`Key` = 'Company'

You are pulling up role and partner by the red text; remove that and you will be fine.

Adn you don't need to use the tick marks around everything; it still works without.

:)

[SoreGums]

Well that's part of the Criteria.
If I remove Role & Partner I get everything that has company...

I don't know how to specify non-returning criteria, that is really my question.
And now that it's morning and I'm thinking clearly, the OR is probably what made you guys suggest what you did and its right. SO I mucked up again there....
What I need is only the Value of Company where there is Key=Role AND Value=Partner.
I really am struggling trying to explain this in words...

I was in a rush when I posted and didn't really make that clear, sorry about that guys :)

So If I update the OP this is what it should have been:
+---------+
| TableA |
+---------+
| ID |
+---------+
| 1 |
+---------+
| 2 |
+---------+
| 3 |
+---------+

+--------------------------------------+
| TableB |
+---------+---------+------------------+
| R_TA_ID | Key | Value |
+---------+---------+------------------+
| 1 | Company | Good Fun Pty Ltd |
+---------+---------+------------------+
| 1 | Name | Ted |
+---------+---------+------------------+
| 1 | Role | Partner |
+---------+---------+------------------+
| 2 | Company | Tuesday Signs |
+---------+---------+------------------+
| 2 | Name | John |
+---------+---------+------------------+
| 2 | Role | Friend |
+---------+---------+------------------+
| 3 | Company | Fried Up Good |
+---------+---------+------------------+
| 3 | Name | Timothy |
+---------+---------+------------------+

Query:
SELECT `TableA`.`ID`, `TableB`.`Value`
FROM `TableA`
LEFT OUTER JOIN `TableB`
ON `TableA`.`ID` = `TableB`.`R_TA_ID`
WHERE (`TableB`.`Key` = 'Role' AND `TableB`.`Value` = 'Partner') OR `TableB`.`Key` = 'Company'

+-----------------------+
| Current Result |
+----+------------------+
| ID | Value |
+----+------------------+
| 1 | Good Fun Pty Ltd |
+----+------------------+
| 1 | Partner |
+----+------------------+

+-----------------------+
| Wanted Result |
+----+------------------+
| ID | Value |
+----+------------------+
| 1 | Good Fun Pty Ltd |
+----+------------------+

Mysql 5
How do I get the wanted result?

Thanks :)

[SoreGums]

OK So I thought more about it and came up with this:

SELECT `temp`.`ID`, `temp`.`Value`
FROM
(SELECT `TableA`.`ID`, `TableB`.`Key`, `TableB`.`Value`
FROM `TableA`
LEFT OUTER JOIN `TableB` ON `TableA`.`ID` = `TableB`.`ID`
WHERE (`TableB`.`Key` = 'Role' AND `TableB`.`Value` = 'partner') OR `TableB`.`Key` = 'Company') as `temp`
WHERE `temp`.`Key` = 'Company'

Is this the best way to do this?

[bubbisthedog]

Ah, I see what you mean now. First of all, I think a DB structure something like the following would be a better, normalized type of design.

COMPANIES

COID NAME
----------------------------
1 | Good Fun Pty Ltd
2 | Tuesday Signs
3 | Fried Up Good
----------------------------

ROLES

ROLEID NAME
----------------------------
1 | Partner
2 | Friend
----------------------------

NAMES

NAMEID NAME ROLEID
----------------------------
1 | Ted | 1
2 | John | 2
3 | Timothy | 2
----------------------------

COMPANYNAMES

CONAID COID NAMEID
----------------------------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
----------------------------

What I've assumed here is that each person in NAMES has a particular role, and that more than one person can be affiliated with a company (hence the design of COMPANYNAMES).

Then you could create a JOIN to get the results that you're seeking:

select COMPANIES.COID, COMPANIES.NAME
from COMPANIES
join COMPANYNAMES
on COMPANIES.COID = COMPANYNAMES.COID
join NAMES
on COMPANYNAMES.NAMEID = NAMES.NAMEID
join ROLES
on NAMES.ROLEID = ROLES.ROLEID
where ROLES.NAME = 'Partner'

See if you agree with this design before continuing to try to get your desired results with your current DB design.

[SoreGums]

Ah, I see what you mean now. First of all, I think a DB structure something like the following would be a better, normalized type of design.
I can normalize the data a whole lot better then I have at the moment, but what I'm trying to avoid is a whole heap of empty columns in rows. Also it allows other columns to be added to the table without actually adding a column.
So i'm using a metadata type strategy. I have nfi as to weather it'll be for the good in the long run.

All I know atm is eventually with a totally normalized strategy after a few months growing into years there are going to be a whole heap of NULL's in every row. Also since the requirements aren't really set in stone here as to what data needs to be collected for each bit, the metadata way gives the app the flexibility to add new columns without having to rework the dataset. ie if we need a required column then i can just add it in the app logic and not have to worry about the thousands of rows that are missing this req column.

I'm hardly a seasoned developer and I understand enough to realise that to save me a lot work this is an acceptable way to go.

Thanks for the input though. Interested to hear what people think too :)

[bubbisthedog]

One of the many reasons to normalize a database is to prevent null values in columns. If someone told me that they want to store and modify companies, names and roles, then I'd create a table for each entity, and then establish the relationships between entities after that. By not taking this approach, you'll certainly end up with convoluted queries like the one I'm about to show you --and, frankly, this should be a very simple query (as I exhibited in the JOIN statement in my previous post). So do what you want obviously, but that's my two cents. The following is untested, but should be close:

select TableA.ID, TableB.Value
from TableA
join
(select R_TA_ID f1
from TableB
where Key = 'Role' and Value = 'Partner') ta
on TableA.ID = ta.f1
join TableB
on ta.f1 = TableB.R_TA_ID
where TableB.Key = 'Company'