473,407 Members | 2,315 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,407 software developers and data experts.

Problem with SELF JOIN sub query

Can't figure out why this won't work?

select distinct m1.patientId, m1.labelvalue, m2.labelvalue
from patient_misc m1
left join (select patientId from patient_misc where labelId=24) as m2
on m1.patientId=m2.patientId
where m1.labelid=23;
Apr 6 '07 #1
2 2536
Try this out....

SELECT
DISTINCT
m1.patientId,
m1.labelvalue,
m2.labelvalue
FROM
patient_misc m1
LEFT JOIN patient_misc m2 ON m1.patientId=m2.patientId AND m2.labelId=24
WHERE
m1.labelid=23;

But not tested ....


Thanks,
Pimp
Apr 6 '07 #2
Motoma
3,237 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. SELECT m1.patentId, m1.labelvalue, m2.labelvalue
  2. FROM patient_misc m1, patient_misc m2
  3. WHERE m2.labelId = 24
  4. AND m1.labelId = 23
  5. AND m1.patientId = m2.patientId
  6.  
Apr 6 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
7
by: Thomas R. Hummel | last post by:
Hello all, I am trying to write a query that compares a member's enrollment period with the products that their group has had during that period (all members belong to a group and the products...
4
by: jbm05 | last post by:
Hi, I'm curious about the computational complexity of a query I have. The query contains multiple nested self left joins, starting with a simple select, then doing a self left join with the...
4
by: Shahzad | last post by:
dear respected gurus, I would like to knew how to apply append,insert query for a self table where no primary keys issues. i do have problem say there are 5 rows of single record, this is data...
6
by: davegb | last post by:
I'm trying to create a self-join table to show the relationship between employee and supervisor. In another thread, I was advised to create a SupervisorID in the employee table, a separate...
0
by: Stewart Midwinter | last post by:
I have a Tkinter app running on cygwin. It includes a Test menu item that does nothing more than fetch a directory listing and display it in a Toplevel window (I'd use a tkMessageBox showinfo...
3
by: sks | last post by:
I have a table that contains keywords (Varchars) each one mapped to a product. so the database schema is just an id column, product column and keyword column. I want to select the products that...
2
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other...
3
by: Genalube | last post by:
All right I am new at access but why would my calculated fields in my query self destruct all of a sudden? I tested these expressions, saved the query and closed it. I reopened them and tested it. ...
3
by: michaeltro | last post by:
I need help with an Access query. I have a table in access with stacked data in the following format: ID Unit Job Group Score 1 GA 1A Female 5 2 GA 1A ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.