Dear All,

Could anybody help here?
I have two tables (meetings and persons).
The persons meet each other and their person_id will be stored in
meetings. I do not know what person is standing left or right
(random).

Now I want to see who Nick met yesterday. All personal information is
in table persons so I want to use INNER JOIN to collect the right
information. But what I write down below doesn't work

How could the following be done:

SELECT *,
CASE
WHEN person_left = 'Nick' THEN person_right_id
WHEN person_right = 'Nick' = THEN person_left_id
END AS person_id_to_get_more_info
FROM meetings INNER JOIN persons ON
persons.person_id=person_id_to_get_more_info


Anybody have a suggestion?


Pim Zeekoers

pim@impulzief.nl wrote:
> Dear All,
>
> Could anybody help here?
> I have two tables (meetings and persons).
> The persons meet each other and their person_id will be stored in
> meetings. I do not know what person is standing left or right
> (random).
>
> Now I want to see who Nick met yesterday. All personal information is
> in table persons so I want to use INNER JOIN to collect the right
> information. But what I write down below doesn't work
>
> How could the following be done:
>
> SELECT *,
> CASE
> WHEN person_left = 'Nick' THEN person_right_id
> WHEN person_right = 'Nick' = THEN person_left_id
> END AS person_id_to_get_more_info
> FROM meetings INNER JOIN persons ON
> persons.person_id=person_id_to_get_more_info
>
>
> Anybody have a suggestion?
>
>
> Pim Zeekoers

With what you told us it is not possible to get the result that you want as
there appears to be no way to tell the date of the meetings.

This is actually a job that PROLOG excels at.

On Nov 6, 2:54 pm, p...@impulzief.nl wrote:
> Dear All,
>
> Could anybody help here?
> I have two tables (meetings and persons).
> The persons meet each other and their person_id will be stored in
> meetings. I do not know what person is standing left or right
> (random).
>
> Now I want to see who Nick met yesterday. All personal information is
> in table persons so I want to use INNER JOIN to collect the right
> information. But what I write down below doesn't work
>
> How could the following be done:
>
> SELECT *,
> CASE
> WHEN person_left = 'Nick' THEN person_right_id
> WHEN person_right = 'Nick' = THEN person_left_id
> END AS person_id_to_get_more_info
> FROM meetings INNER JOIN persons ON
> persons.person_id=person_id_to_get_more_info
>
> Anybody have a suggestion?
>
> Pim Zeekoers

What do you mean by "doesn't work?" It doesn't return what you think
it should? It gives a syntax error?

You have an extra = on this line:

WHEN person_right = 'Nick' = THEN person_left_id

On 6 nov, 21:43, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Nov 6, 2:54 pm, p...@impulzief.nl wrote:
>
>
>
>
>
> > Dear All,
>
> > Could anybody help here?
> > I have two tables (meetings and persons).
> > The persons meet each other and their person_id will be stored in
> > meetings. I do not know what person is standing left or right
> > (random).
>
> > Now I want to see who Nick met yesterday. All personal information is
> > in table persons so I want to use INNER JOIN to collect the right
> > information. But what I write down below doesn't work
>
> > How could the following be done:
>
> > SELECT *,
> > CASE
> > WHEN person_left = 'Nick' THEN person_right_id
> > WHEN person_right = 'Nick' = THEN person_left_id
> > END AS person_id_to_get_more_info
> > FROM meetings INNER JOIN persons ON
> > persons.person_id=person_id_to_get_more_info
>
> > Anybody have a suggestion?
>
> > Pim Zeekoers
>
> What do you mean by "doesn't work?" It doesn't return what you think
> it should? It gives a syntax error?
>
> You have an extra = on this line:
>
> WHEN person_right = 'Nick' = THEN person_left_id- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -


That = should not be there no, sorry.

Is says there is no such column "person_id_to_get_more_info"


In other words I could also ask:

SELECT column1 AS helloHere FROM table1 INNER JOIN table2 ON
table2.hellotext=helloHere

I want to use the helloHere value to select the row from another
table.


Can that be done?

pim@impulzief.nl wrote in
news:1194382124.736645.40970@v29g2000prd.googlegro ups.com:


> In other words I could also ask:
>
> SELECT column1 AS helloHere FROM table1 INNER JOIN table2 ON
> table2.hellotext=helloHere
>
> I want to use the helloHere value to select the row from another
> table.

As far as I know, that cannot be done, the JOIN would have to be "ON
table2.hellotext=table1.column1" because the alias is really only used when
displaying the result - it's not changing the name of the column for the
rest of the query.

Good Man <heyho@letsgo.com> wrote in
news:Xns99E14FCEBD54sonicyouth@216.196.97.131:

> pim@impulzief.nl wrote in
>> I want to use the helloHere value to select the row from another
>> table.

And if that's the case, then perhaps you should be using subqueries.

On Nov 6, 2:54 pm, p...@impulzief.nl wrote:
> Dear All,
>
> Could anybody help here?
> I have two tables (meetings and persons).
> The persons meet each other and their person_id will be stored in
> meetings. I do not know what person is standing left or right
> (random).
>
> Now I want to see who Nick met yesterday. All personal information is
> in table persons so I want to use INNER JOIN to collect the right
> information. But what I write down below doesn't work
>
> How could the following be done:
>
> SELECT *,
> CASE
> WHEN person_left = 'Nick' THEN person_right_id
> WHEN person_right = 'Nick' = THEN person_left_id
> END AS person_id_to_get_more_info
> FROM meetings INNER JOIN persons ON
> persons.person_id=person_id_to_get_more_info
>
> Anybody have a suggestion?
>
> Pim Zeekoers

How about this (untested):

SELECT p.*
FROM meetings
JOIN persons p
ON meetings.person_right_id = p.person_id
WHERE meetings.person_left = 'Nick'

UNION

SELECT p.*
FROM meetings
JOIN persons p
ON meetings.person_left_id = p.person_id
WHERE meetings.person_right = 'Nick'