Join Question

[adwaitjoshi]

I have one table in which I select a row based on person_id. I join this table with a second table which has person_id as a foreign key.

The table1 will have only one entry with person_id and table2 will have more than one. I need to select all columns from table1 and all columns from table2 such that only most recent row is selected from table2. table2 has a field created_date_time (timestamp) which will tell me which is the latest row what kind of SQL do I need to write for this?

[Naumaan]

Its ORACLE specific Query.
select * from table1 a,table2 b
where a.person_id =b.person_id and b.created_date_time = select MAX(created_date_time ) from table2

[adwaitjoshi]

Yes that worked! HEre is another tricky situation
Table 1
Persons


Table2
Members


For each person there can be zero or more rows in members

I need to write a query, where I am selecting * from table 1 and "Y" even if a single entry is present in Table2 or "N"

So lets say I have 2 entries in persons and 5 entries in members for person1 i.e. person 2 is non member so the result would be

Person1 ALL COLUMNS plus a "Y"
Person2 ALL COLUMNS plus a "N"

the query that I have written returns me number of rows in members so instead of getting 2 rows I get 6 rows :( how can I solve this problem. Thanks in advance !!

[Igor Soukhov]

In this situation you have to use left outer join on
persons and member tables.


select * from
persons left join members
on
persons.Id = members.personId


this query returns ALL columns of persons table
and ALL columns of members table, BUT if the person's row
has no associated information in members table
the resultset columns from members table will be NULL.

Thus, you need to modify result set checkin if values
of members table are null or not:


select Persons.*,
CASE
WHEN Members.personId IS NULL THEN 'N'
ELSE 'Y'
END
from persons
left join members
on persons.id = members.personid


Try this query and tell us if it works.