qlm.exe
16 July 2018
.
Videos
YouTube - Software for ballistics; Training DVD.
References
Category:Windows softwareQ:
SQL Statement with multiple Left Join
I have the following sql statement which works, but i would like to create a new statement with more left joins.
I have a series of pages, with a 'pk' column which identifies each page. Each page has a 'description' column which may contain '*' in the description column.
The sql below will search for a page description and return pages that contain this description.
SELECT * FROM pages WHERE pk=@pk
I also want to return the 'pages' 'page' and 'parent' column if it exists.
i have tried using this:
SELECT * FROM pages,parents
WHERE (pages.pk=@pk OR parents.pk=@pk) AND pk IN (SELECT pk FROM pages WHERE pk=@pk)
this throws an error (can't access the parent of a non referenced variable).
i also tried this (though it doesn't work):
SELECT * FROM pages
LEFT JOIN parents ON parents.pk=pages.pk
WHERE (pages.pk=@pk OR parents.pk=@pk)
my question is how can i use LEFT JOIN with IN and also with multiple variables. Is it even possible?
A:
If you have only one place where you use the variable, and that place is also the place where you're putting in the @pk then you can just put the table name at the end of the where clause.
So, it becomes this:
SELECT * FROM pages,parents WHERE pages.pk=@pk OR parents.pk=@pk AND pk IN (SELECT pk FROM pages WHERE pk=@pk)
The where clause isn't changing from the original so I don't understand the error you're getting. Also, if the pages table has a foreign key to the parents table you will need to put that in the select as well (I'm not sure what your exact relationship is).
It's been a wild and wonderful three weeks. I have had such a good time working with Lauren, and with all of you, and I am be359ba680
Related links:
Comments