How do I use left join statements when a certain column is not in the first table

Question

0.00/5 (No votes)

See more:

I have been trying to combine all tables into a large table with a left join. However, because my profile table does not have a cemetery_ID in the table the join statement will not bind. however, I need the cemetery_ID to join the cemetery_reference table.

here is what I have:

"Select * From profile Left join cemeteries on Profile.ID = cemeteries.Individual_ID left join Cemetery_reference on Cemetery.Cemetery_ID = Cemetery_reference.Cemetery_ID"

What I have tried:

Tried to separate the joins by using parenthesis but this not work.

Posted 6-Sep-18 5:52am

Member 11856456

Updated 6-Sep-18 6:29am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-09-06T06:29:00

Solution 1

That's because there is no relation shown between the Cemeteries table (which shares a link with Profile) and the Cemetery table (which links to the Cemetery_reference table), or between the Cemetery and Profile tables.

It's possible you meant to use the Cemeteries (or Cemetery) table throughout instead of both, but if you didn't and there is no relationship, then you need to look for a table which does provide a relationship, or change your DB design. (I'd be tempted to go with the latter: why do you have two tables with such similar names anyway?)

Posted 6-Sep-18 6:29am

OriginalGriff

Comments

Member 11856456 6-Sep-18 12:44pm

The cemetery_reference is the name of the cemetery and the location. The other is the individuals buried within the cemetery and their locations. Also, the reference_cemetery table is set up where no duplicates of that cemetery can be entered.

OriginalGriff 6-Sep-18 13:45pm

I was referring to the Cemetery and Cemeteries tables.

OriginalGriff 6-Sep-18 14:05pm

I'd probably call them Cemeteries (name, address, hours, ...), and Plots (CemeteryID, location within cemetery, occupied / vacant / reserved), possibly with an Interments table for the occupants themselves (Name, PlotID). I don't know about where you life, but sometimes in the UK a plot can be "multi occupant" for example when a husband / wife / child is added at a later date.
Names are important - they make the code more readable and less prone to error, which having a plural and singular form for two different tables does not help with (normally a table is a plural, because it contains more than row).

Member 11856456 6-Sep-18 14:35pm

Is there a way I can post my database diagram? or possibly send it to you? that way you can see what I am trying to accomplish.

OriginalGriff 7-Sep-18 5:30am

Nope! Just describe the tables and columns as SQL statements.