Which join or query method would be possible? MYSQL Quick Question?

Very quick question!

If a table FK has a null value is there any way that I can design my que开发者_JAVA技巧ry so that it does not bother joining the table the foreign key is associated to. I am currently using Left Joins.

Is there another join I could use or can I do something like this in my query using PHP?

if (table foreign key == NULL) 
{   
do not join table that foreign key points to 
}

else 
{   
join table using foreign key value 
}

Thanks

---

Can you post your table and key names? we can then write the query - I think it needs to be an inner join to avoid the nulls.

select * from table1
inner join table2
on table1.pk = table2.fk
where {your conditions}

For your schema

select * from procedure
inner join dosage on dosage.dosage_id = procedure.dosage_id

Why not deal with your NULL with a CASE statement and push an empty string instead?

SELECT *, CASE dosage_id
WHEN (dosage_id IS NULL) THEN ''
END
from procedure
inner join dosage on dosage.dosage_id = procedure.dosage_id

(note: I'm not the best at CASE - ;) hopefully that gets you close enough)

---

You could set the clause that the foreign:key is not null i think (hope i understood correctly):

SELECT * 
FROM
procedure
LEFT JOIN dosage ON dosage.dosage_id= procedure.dosage_id
WHERE 
dosage.dosage_id is not null

but i think it's equivalent to using an inner join

SELECT * 
FROM
procedure
INNER JOIN dosage ON dosage.dosage_id = procedure.dosage_id

---

AFAIK I think that there is no specific join to get precisely what you want, so you have to use a condition like the one in your pseudo-code to obtain this specific behaviour.

However, a LEFT JOIN would be just fine in this situation: you would not have to write two separate queries, and you'll just have to handle a bunch of NULL values if FK is null.